[wp-trac] [WordPress Trac] #54346: Slow SQL queries fetching posts from specific categories

[WordPress Trac]

#54346: Slow SQL queries fetching posts from specific categories
-------------------------------------------------+-------------------------
 Reporter:  Krstarica                            |       Owner:
                                                 |  craigfrancis
     Type:  enhancement                          |      Status:  accepted
 Priority:  normal                               |   Milestone:  Future
                                                 |  Release
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  needs-testing has-patch has-unit-    |     Focuses:  rest-api,
  tests                                          |  performance
-------------------------------------------------+-------------------------

Comment (by craigfrancis):

 I haven't forgotten, I was able to replicate the issue, but need a bit
 more time to look into this.

 For Ivan, with **793,227** Posts, and **53,391** Terms, where each Term is
 used on average by **22** Posts, and the relationship table has
 **1,198,905** records... it seems that, if the sub-query gets a small
 number of results, it's faster to join to the Posts table... but, using a
 sub-query can be a slower for websites with a different ratio of Posts to
 Terms (what I'll be looking at next).

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/54346#comment:19>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform