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 hire you as a professional and would pay you for your work. Your job would be to make the database more efficient by using indexes or whatever else is needed (I am not an expert), and also providing advice as far as PHP queries go if necessary. A few things 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 or PM me or something if you are interested in working on this. We can work out an hourly rate and I would be happy to pay you via PayPal or whatever method you choose. Thank you!
You seriously need to look at your coding and db structure if a page is making hundreds of queries for a single user. I believe MySQL now supports stored procedures which will reduce some of these issues.
Yeah, I'm aware that that's what's necessary. Unfortunately, I am not qualified to do some of this work. Are you interested?
We dont use PHP and so wouldnt be able to help with that end which would be necessary given that it seems a significant rewrite exercise is required.
I'm pretty good at SQL Server and the Microsoft world, but not experienced enough at high load MySql / PHP to be able to offer anything but some general advice. The reports you quoted from ... can you drill down into them? This line in particular would freak me out if I were in your shoes: "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." Can you describe the nature of your database and web application? If it's a data warehouse and rarely changes, you could hypothetically put an index on every column in every table. If it's a transactional system, meaning the server does as much writing as reading, too many indexes is just as bad as too few. You can go the free advice route, or, if you want to hire someone, more info on how the database is used will help eliminate people without experience. I developed a system in SQL Server / ASP.NET that had a quarter billion rows in a set of tables ( partitioned vertically instead of horizontally ) that added about 1.5 m rows a day, in a single batch. If it was an ERP system or a forum, where data is in a constant state of flux, what I did would have been drastically less helpful. Do you have access to all of the code, from the create table statements to the PHP? If that doesn't amount to all that much, I'll take a quick look at it and tell you what I'd change. You're doing a lot of table scans, so the good news is a few indexes will go a long way. Is it safe to assume you have the database and Apache on a dedicated server?