Peter at MySQL Performance Blog pointed out this sweet perl script to analyze MySQL’s slow query logs. (This is supposedly a PHP port.)
The script does a good job of aggregating similar queries (those that only differ in their query values) and displaying overall stats for them. The following two queries are showing up a lot in my WPMU installation because I also have it set to log queries that don’t use indexes. They’re not necessarily slow (MySQL < 5.1 logs execution times to the nearest second, with a one second minimum), but the fact that they’re appearing a lot probably means the value isn’t being cached in Memcached.
The first SQL line shows the prototype for the query, the second is a real example from the log.
| ``` ### 90379 Queries ### Total time: 90556, Average time: 1.00195841954436 ### Taking 1 to 6 seconds to complete ### Rows analyzed 744 - 587874 SELECT COUNT(ID) FROM wp_XXX_posts WHERE post_status = 'XXX' and post_type = 'XXX'; SELECT COUNT(ID) FROM wp_7_posts WHERE post_status = 'publish' and post_type = 'post'; ``` |
| ``` ### 8768 Queries ### Total time: 9033, Average time: 1.03022354014599 ### Taking 1 to 4 seconds to complete ### Rows analyzed 0 - 199 SELECT option_name, option_value FROM wp_XXX_options FORCE INDEX(PRIMARY) ORDER BY option_id ASC; SELECT option_name, option_value FROM wp_4_options FORCE INDEX(PRIMARY) ORDER BY option_id ASC; ``` |