MySQL Performance Optimization - A Lesson

By Angsuman Chakraborty, Gaea News Network
Wednesday, March 8, 2006

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
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

Westhost User Too
June 23, 2006: 4:08 pm

I too am on Westhost and have been working with people like Jonny to find a solution to my blog problems. I too am using WP-Cache, but now my blog is consuming 4.52% of the entire server from my VPS. Ia m not as technical as you, so we have slow-going, but I will post any solution I find.


May 10, 2006: 6:20 am

The code was specified only as an example. It is in a plugin I wrote for this blog. You will not find it in generic wordpress installation. However if you have a highly commented blog you may benefit from this wp-cache optimization technique.

May 10, 2006: 2:22 am


My wordpress just crashied my semi-dedicated server due to high CPU load..

So I just installed the WP-Cache plugin and found this blog.. I asked my webhosting admin to do this optimization and here’s what he wrote me:

I checked all of your files for the text “SELECT distinct cat_ID”, and couldn’t find a match.

I also checked for “SELECT cat_ID, cat_name AS Category, count(” and couldn’t find a match either.

I also checked for “st_post2cat” and couldn’t find it, so they may be running a different version of the code.

I was just wondering if you could help me regarding this matter. BTW, I’m running wordpress 2.0.2

Thank you for your time…you can just reply this to my e-mail..



will not be displayed