Slow queries on a huge database

Question

I have a huge database with over 250,000 posts. I have ran every optimisation i could have and searched every possible solution on the internet.

Lets say i have this query where i want to get the last 4 posts from a category:

$recent = new WP_Query(array( 'cat' => '8', 'posts_per_page' => '4')); 

Which Query Monitor translates to:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (8,9,10,11,12,13,14,15) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 4

And it takes 1,4628 seconds.

My question is why if i put the same query in phpmyadmin and change this line:

AND ( wp_term_relationships.term_taxonomy_id IN (8,9,10,11,12,13,14,15) )

into

AND ( wp_term_relationships.term_taxonomy_id IN (8) )

The query becomes TWICE as fast (0.5077 seconds). Why does it search other Taxonomy ids when it is only supposed to search the "8" - the category id? Any advice would be welcome.

Thank you

Answer 1

As @milo said in the comments, the cat parameter (and similar parameters in other hierarchical taxonomies) will make the query look in subcategories as well. If you do not want that to happen use the category__in parameter.

As for the general slowness, it is hard to guess, but it depends on your DB server performance (maybe it is under constant load and it is time to upgrade it) and how "deep" are those posts in the table.

Last but not least, if you do not need the information of how many matching results exist beyond your limit, you should try adding 'no_found_rows' => true to your query. That way the DB will not try to calculate the number of total posts in the category (the SQL_CALC_FOUND_ROWS part of the query)