k-factor

I think I’m having this same issue on a less robust site. About 13,000 posts total, and a hundred or so categories. After the recent 3.1 upgrade, I instantly noticed a performance hit on two of our more query-intensive pages – pages that were quick-loading before, even without caching. Analyzing with query tools, it’s pretty clear that SQL_CALC_FOUND_ROWS is a culprit.

One query that reads like this:

$my_query1 = new WP_Query(array('category__and' => $myarray,'orderby' => title,'order' => ASC, 'post_status'=>publish, 'showposts' => 250,'ignore_sticky_posts' => 1));

results in:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
WHERE 1=1
AND ( wp_posts.ID IN
(
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (3,6)
GROUP BY object_id
HAVING COUNT(object_id) = 2
))
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_title ASC LIMIT 0, 250

and:

SELECT term_taxonomy_id
FROM wp_term_taxonomy
WHERE taxonomy = 'category'
AND term_id IN (6,3)

with a combined execute time of over 10 seconds.

Once cached, of course the pages load quickly. But the CPU maxing out for one measley SQL query and one page-load is pretty ridiculous. Executed on their own in a query browser, the same queries are instantaneous, but obviously PHP and WordPress itself are involved in the mix here too.

I’m at a loss for now. I’m by no means an expert PHP/MySQL/Wordpress coder, but I’ve lived and learned a bit. I would welcome any suggestions from anyone having better luck with this.