1

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

  • 1
    Are those children of category 8? What does the query look like if you use category__in instead of cat? – Milo Mar 11 '18 at 0:14
  • Looks like that solved my problem, please write it as an answer so that i can choose it as complete. Any ideas on how to further optimize it is also very welcome :) Thank you very much sir! – John M. Mar 11 '18 at 1:21
0

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)

  • Thank you, I have already tried that and it helps with few ms. The web server is very expensive and optimized, i can't imagine how slow it would be on a normal one (the database is 9GB Optimized). I got the queries down to ~0.5 ms, the front page would still be slow for logged in users but not as it used to be :) For everyone else the cached version is almost instant. Thank you Milo and @Mark Kaplun . – John M. Mar 12 '18 at 16:24
  • if you are not using object cache, this is a good time to start, then these queries are supposed to be cached, probably until a post is being updated or created. – Mark Kaplun Mar 12 '18 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.