[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:  closed
 Priority:  normal                         |   Milestone:
Component:  Taxonomy                       |     Version:  6.0.2
 Severity:  normal                         |  Resolution:  duplicate
 Keywords:  needs-patch reporter-feedback  |     Focuses:  performance
-------------------------------------------+--------------------------
Changes (by peterwilsoncc):

 * status:  new => closed
 * resolution:   => duplicate
 * milestone:  6.4 =>


Comment:

 @joemcgill @dimitrisv I think I'll close this as a duplicate of #40351,
 which also aims to optimize the term counting queries.

 I don't think changing how (or what) is counted will improve the queries
 in any meaningful way as the fundamental problem comes from two things:

 * the `WHERE` clauses are inefficient on tables of any scale, and should
 be replaced with incrementing or decrementing queries
 * the inefficient queries run often

 On some database engines modifying the count MAY improve the performance
 fractionally, but on others modifying the count MAY degrade performance
 fractionally. As `term_taxonomy_id` can't be `null`, it's likely that the
 SQL optimizer is counting either the rows or the primary key as it deems
 most efficient.

 To improve the performance of every site, no matter the table size, I
 think it's best to figure out the edge cases hit in #40351 and fix the
 core problem.

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