I'm working on optimizing my MySQL database because my admin area is slow on most of the pages where there are hundreds and hundreds of queries. If any of you are coders who are experienced in this, please let me know. I would pay you for your professional help if it comes to that ! If you just have some suggestions, please let me know. A few "jaw-dropping" numbers I've gleaned from MySQL reports: Handler_read_rnd 18 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly. Handler_read_rnd_next 2,461.87 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. Qcache_lowmem_prunes 1,330 k The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Created_tmp_disk_tables 100 k The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based. Select_full_join 37 k The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables. Select_range_check 562 The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.) Sort_merge_passes 1,940 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable. Opened_tables 454 k The number of tables that have been opened. If opened tables is big, your table cache value is probably too small. Table_locks_waited 1,189 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication. As you can see, I've got a bunch of issues that need to be cleared up to make everything more efficient. Please post here if you can help out or know someone that can. I'm no expert as far as indexes and whatnot go, but I'm sure there are some things that could be done to decrease those numbers. I don't think any indexes have been made or are being used as of now, and with millions of queries being processed this is a problem. Will pay for help.
If you have not made any index, creating indexes will give you dramatic performance improvement. Also use Explain to understand the query. With the help of EXPLAIN, you can see where you should add indexes to tables to get a faster SELECT that uses indexes to find rows. You can find a good information on mysql optimisation here Sections worth reading: - When MySQL uses indexes - When MySQL doesn't use an index - Learn to use EXPLAIN