Categories
optimization php

Fix the WordPress SQL_CALC_FOUND_ROWS Bug

Anyone who has a WordPress blog with a lot of posts will eventually encounter an extremely slow query. I refer to this as the SQL_CALC_FOUND_ROWS Bug. If you have slow-query-log enabled then a query similar to this might have shown up before:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (44682, 44657, 44630) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 24580, 5

Just how detremential is this to your blogs performance?  Well thanks to newrelic, I can show you:

SQL_CALC_FOUND_ROWS Bug

Unfortunately I don’t know why WordPress runs this query.  What I do know is that it apparently only shows up on index.php.  What you probably care about is how to fix this problem.   I’ve located a possible workaround thanks to this open ticket at wordpress.org.  The diff log on the changes are listed as well.  I went ahead and applied these code changes to wp-includes/query.php.  The fixed query.php can be found here.

How are the results so far?  Inconclusive.  I just applied this patch and nothing broke so far, which is always a plus.  I suggest you give it a try and see how your blog responds in a development environment.  If I STILL spot slowdowns in the revised query, then I will update this post and let you know.

Some of you might be asking, does this affect your version of WordPress? The answer is yes.  I am running WordPress 3.2.1.

Please do post your thoughts, concerns, or comments to help others out.

3 replies on “Fix the WordPress SQL_CALC_FOUND_ROWS Bug”

The query is so that it knows how many pages exist if you have pagination turned on. The other three queries that are just as obnoxious are the Category list, Post Date Calendar and Tag Cloud.

Leave a Reply