MySQL Performance Optimization - A Lesson

The MySQL database supporting this blog was consuming massive amount of CPU, which effectively lead to site going down 3-4 times this week. I knew an obvious culprit. However reality differed.

I knew for sometime I had a jumbo query, which was not cached, and is likely to contribute to MySQL server load:
SELECT cat_ID, cat_name AS Category, count( * ) AS ‘Count’
FROM $wpdb->categories, $wpdb->post2cat, $wpdb->posts
WHERE cat_ID = category_id and
category_nicename != ‘headline’ and ID = post_id and
post_status = ‘publish’
GROUP BY cat_name
HAVING count( * ) > 10
ORDER BY ‘Count’ DESC
LIMIT 0 , 10

I couldn’t see an obvious way to optimize this without curbing the functionality. In case you are wondeering substituting category_nicename != ‘headline’ with cat_ID = 37 doesn’t help.

I also thought about using slow query log to pinpoint the problem. Fortunately none of them was required. I got an email from friendly WestHost High Load support. Jonny found out by repeatedly running ‘SHOW FULL PROCESSLIST;’ that the culprit was:

SELECT distinct cat_ID, cat_name
FROM st_categories, st_post2cat
ORDER BY category_nicename;

This took me by surprise. Th query takes anywhere between 3-5 seconds. In high loads with multiple simultaneous requests that is good enough to overload the server. The solution was deceptively simple:

SELECT distinct cat_ID, cat_name
FROM st_categories
ORDER BY category_nicename;

The original query was redundant and wasteful. Originally I intended to display only categories with one or more posts in them. However somewhere down the line I forgot to add the WHERE clause. Today all my categories have posts. So simply removing st_post2cat from the FROM list is good enough to bring down the execution time to 0.01 seconds.

The take home lessons are:

• Never guess bottleneck queries; find it.
• Simple tool like “SHOW FULL PROCESS LIST” is often as good as definitive, yet harder to analyze tool like slow query log.
• Having good support from your web hosting provider doesn’t hurt either