The MySQL Slow Query Log is a required tool in the database administrator’s toolbox. It’s great for troubleshooting specific issues, but it’s also great for some rainy day application tuning.
My slow query log is in
/var/lib/mysqld/db-001-slow.log and records any queries that take longer than 10 seconds (the default value for
long_query_time). I can get information out of this log using
`mysqldumpslow db-001-slow.log` prints out slow queries sorted by descending execution time. But that’s not useful to me, because any query can get delayed by a blip in the system.
I like running
`mysqldumpslow -s c db-001-slow.log` which prints out the slow queries sorted by descending count of times that query occurred. Optimizing a query that takes 10 seconds to execute but occurs a dozen times every minute will be more beneficial than optimizing the query that takes 140 seconds to execute but rarely occurs.
The first time I tried this exercise, I revealed the following 3 types of slow queries (can’t remember the exact order now):
- Queries with lots of logic and joins returning infrequently-changing data.
- Queries using the
curdate()function, which are not query cacheable.
- Queries to insert/update a stats table for content view counts.
For #1, I used an in-memory cache to cache the query results. For #2, I replaced the
curdate() function with the PHP
date() function everywhere I could find it. For #3, I noticed an extraneous index on the stats table, and indexes slow down inserts and updates, so I removed it. For more on handling these types of queries, see my next post.