[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:  (none)
     Type:  enhancement            |     Status:  new
 Priority:  normal                 |  Milestone:  Awaiting Review
Component:  Taxonomy               |    Version:  5.8.1
 Severity:  normal                 |   Keywords:
  Focuses:  rest-api, performance  |
-----------------------------------+-----------------------------
 Noticed that REST API queries used in mobile app are very slow and found
 that such SQL queries can be optimized to be 10 times faster (1.8134
 seconds vs. 0.1804 seconds) for wp_posts table having 800k records, see
 below.

 Very similar queries are used when displaying posts from specific
 categories on the web, meaning optimizing this could lead to significant
 speed up everywhere.

 The one responsible for this is WP_Tax_Query->get_sql_for_clause function.

 REST API to fetch posts from specific categories, e.g. `/wp-
 json/wp/v2/posts/?per_page=10&_embed=1&categories=63545,63546,63547,63548,63549,63552,76287&page=1`
 executes the following SQL 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 (
       63545, 63546, 63547, 63548, 63549, 63552, 76287
     )
   )
   AND wp_posts.post_type = 'post'
   AND(
     (wp_posts.post_status = 'publish')
   )
 GROUP BY
   wp_posts.ID
 ORDER BY
   wp_posts.post_date DESC
 LIMIT
   0, 10
 }}}
 Query took 1.8134 seconds.


 This query can be optimized by using subquery:

 {{{
 SELECT
   wp_posts.ID
 FROM
   wp_posts
 WHERE
   wp_posts.ID IN (
     SELECT
       object_id
     FROM
       wp_term_relationships
     WHERE
       wp_term_relationships.term_taxonomy_id IN (
         63545, 63546, 63547, 63548, 63549, 63552, 76287
       )
   )
   AND wp_posts.post_type = 'post'
   AND wp_posts.post_status = 'publish'
 ORDER BY
   wp_posts.post_date DESC
 LIMIT
   0, 10
 }}}
 Query took 0.1804 seconds seconds.

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