We have a site where the index page loads very slowly due to poorly written queries - here is an overview of its issues. Need someone who can write queries with joins and understands what an index is etc. Here are the issues and suggested corrections:
Too many sorts are causing temporary tables. Consider increasing sort_buffer_size and/or read_rnd_buffer_size, depending on your system memory limits.
There are lots of rows being sorted. While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting.
There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.
The rate of reading the first index entry is high. This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.
The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.
Many temporary tables are being written to disk instead of being kept in memory. Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group
MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.
17 freelancers are bidding on average $257 for this job
Hello? How are you? I have read your brief description. I have good skills in database file....... So I think we can work together. Hope to work with you. Thank you.
HI Dear. I am an expert in MySQL so can fix your issues. I am confident to your project. I can start work now. I will do best work for your project. Thank you. Regards.
I am good at query writing and optimisation. Still from every project was successful. I have reduce query time from 4 hours to 30 min . I am good indexing, triggers, stored procedure writing