MySQL Slow Query Log Analysis

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;