[wp-trac] [WordPress Trac] #56598: Performance optimization in _update_post_term_count()

[WordPress Trac]

#56598: Performance optimization in _update_post_term_count()
-------------------------+--------------------------
 Reporter:  dimitrisv    |       Owner:  (none)
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  6.4
Component:  Taxonomy     |     Version:  6.0.2
 Severity:  critical     |  Resolution:
 Keywords:  needs-patch  |     Focuses:  performance
-------------------------+--------------------------

Comment (by peterwilsoncc):

 @dimitrisv Are you able to run the current and proposed change with
 `EXPLAIN FORMAT=TREE`?

 Running it locally on InnoDB/MySQL 8, I'm not seeing any cost benefit but
 it would be more helpful to see data from a large table such as yours.

 Please include the DB version and engine you're running, it can make quite
 a difference.

 For the record, here are the results I am seeing with a sparsely populated
 table:

 {{{#!sql
 EXPLAIN FORMAT=TREE
 SELECT COUNT(*) FROM wp_term_relationships, wp_posts
 WHERE wp_posts.ID = wp_term_relationships.object_id
   AND post_status IN ('publish')
   AND post_type IN ('post', 'page')
   AND term_taxonomy_id = 95;
 }}}


 {{{
 -> Aggregate: count(0)  (cost=25.60 rows=1)
     -> Nested loop inner join  (cost=22.79 rows=28)
         -> Covering index lookup on wp_term_relationships using
 term_taxonomy_id (term_taxonomy_id=95)  (cost=5.29 rows=50)
         -> Filter: ((wp_posts.post_status = 'publish') and
 (wp_posts.post_type in ('post','page')))  (cost=0.25 rows=1)
             -> Single-row index lookup on wp_posts using PRIMARY
 (ID=wp_term_relationships.object_id)  (cost=0.25 rows=1)
 }}}

 ----

 {{{#!sql
 EXPLAIN FORMAT=TREE
 SELECT COUNT(wp_term_relationships.term_taxonomy_id) FROM
 wp_term_relationships, wp_posts
 WHERE wp_posts.ID = wp_term_relationships.object_id
   AND post_status IN ('publish')
   AND post_type IN ('post', 'page')
   AND term_taxonomy_id = 95;
 }}}

 {{{
 -> Aggregate: count(wp_term_relationships.term_taxonomy_id)  (cost=25.60
 rows=1)
     -> Nested loop inner join  (cost=22.79 rows=28)
         -> Covering index lookup on wp_term_relationships using
 term_taxonomy_id (term_taxonomy_id=95)  (cost=5.29 rows=50)
         -> Filter: ((wp_posts.post_status = 'publish') and
 (wp_posts.post_type in ('post','page')))  (cost=0.25 rows=1)
             -> Single-row index lookup on wp_posts using PRIMARY
 (ID=wp_term_relationships.object_id)  (cost=0.25 rows=1)
 }}}

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