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