[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:  has-patch has-unit-tests needs-      |     Focuses:  rest-api,
  test-info                                      |  performance
-------------------------------------------------+-------------------------

Comment (by trinopoty):

 Hi. I have run into this exact issue in the latest version of wordpress.
 (Please see https://wordpress.org/support/topic/slow-sql-query-after-
 update-to-6-9-4/).
 But basically, the query
 {{{
 SELECT 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 (<id list here>)) AND
 wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) ORDER
 BY wp_posts.post_date DESC LIMIT 0, 10
 }}}
 executes with a really unoptimized query plan on the latest wordpress
 version.

 Executes with an optimized plan in the older version and returns in 0.01
 seconds:
 {{{
 -> Limit: 10 row(s)  (cost=452486 rows=10) (actual time=0.0938..0.298
 rows=10 loops=1)
   -> Nested loop inner join  (cost=452486 rows=318651) (actual
 time=0.0927..0.297 rows=10 loops=1)
       -> Covering index lookup on wp_posts using type_status_date
 (post_type='post', post_status='publish') (reverse)  (cost=52123
 rows=443933) (actual time=0.0404..0.055 rows=38 loops=1)
       -> Filter: (wp_term_relationships.term_taxonomy_id in
 (43,89,114,200,462,521,536,609,856,434561))  (cost=0.255 rows=0.718)
 (actual time=0.00556..0.00618 rows=0.263 loops=38)
           -> Covering index lookup on wp_term_relationships using PRIMARY
 (object_id=wp_posts.ID)  (cost=0.255 rows=6.47) (actual
 time=0.00279..0.00532 rows=10.7 loops=38)
 }}}

 But it executes with a much slower query plan in the new version and
 returns in 1.20 seconds:

 {{{
 -> Limit: 10 row(s)  (actual time=1630..1630 rows=10 loops=1)
   -> Sort: wp_posts.post_date DESC, limit input to 10 row(s) per chunk
 (actual time=1630..1630 rows=10 loops=1)
       -> Stream results  (cost=297461 rows=152622) (actual time=1.52..1586
 rows=196591 loops=1)
           -> Nested loop inner join  (cost=297461 rows=152622) (actual
 time=1.52..1551 rows=196591 loops=1)
               -> Filter: ((wp_term_relationships.term_taxonomy_id in
 (43,89,114,200,462,521,536,609,856,434561)) and
 (wp_term_relationships.object_id is not null))  (cost=61760 rows=305245)
 (actual time=1.48..132 rows=196836 loops=1)
                   -> Covering index range scan on wp_term_relationships
 using term_taxonomy_id over (term_taxonomy_id = 43) OR (term_taxonomy_id =
 89) OR (8 more)  (cost=61760 rows=305245) (actual time=1.47..95.7
 rows=196836 loops=1)
               -> Filter: ((wp_posts.post_type = 'post') and
 (wp_posts.post_status = 'publish'))  (cost=0.672 rows=0.5) (actual
 time=0.00691..0.00702 rows=0.999 loops=196836)
                   -> Single-row index lookup on wp_posts using PRIMARY
 (ID=wp_term_relationships.object_id)  (cost=0.672 rows=1) (actual
 time=0.00647..0.0065 rows=1 loops=196836)
 }}}

 This proposed solution might help our case.

 Regards,
 Trinopoty Biswas

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