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.