[wp-trac] [WordPress Trac] #40351: Term post re-counts scale poorly, are common and difficult to avoid

[WordPress Trac]

#40351: Term post re-counts scale poorly, are common and difficult to avoid
-------------------------+-----------------------------
 Reporter:  mattoperry   |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Taxonomy     |    Version:  trunk
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 Under normal conditions whenever a post status transition occurs,
 `_update_term_count_on_transition_post_status` attempts to completely
 recalculate the post counts for each term related to the post by re-
 counting each term's total number of post relationships..  For sites with
 large term relationship tables, large numbers of total terms and high
 numbers of terms per post, this recalculation does not scale well and can
 lead to wp-admin lag (while saving a post for example) or failed queries.

 A typical bad scenario looks like this:

 Consider a site with a large term_relationship table and a post with (say)
 30 terms assigned to it.  When that post is updated, an eventual call to
 `_update_post_term_count` will cause that function to execute 60 total
 queries on some very large tables. 30 are SELECTs:

 `SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts WHERE
 $wpdb->posts.ID = $wpdb->term_relationships.object_id AND post_status =
 'publish' AND post_type IN ('" . implode("', '", $object_types ) . "') AND
 term_taxonomy_id = %d`

 (This is actually the hopeful case, since if we need to count attachments
 too, those are added to the above query via a subselect which probably
 makes things worse.)

 Interspersed among the 30 SELECTs are 30 UPDATEs to the tt table,
 generated by:

 `$wpdb->update( $wpdb->term_taxonomy, compact( 'count' ), array(
 'term_taxonomy_id' => $term ) );`

 One result of all of this can be failed queries -- typically the SELECTs
 -- and incorrect term post counts.  Even more frequently the issue
 manifests as slow post updating behavior (the lag a user feels while
 waiting for a post to save or publish.)

 We currently provide two mechanisms (besides just unhooking
 `_update_term_count_on_transition_post_status` entirely and not updating
 term post counts at all) to do something about this problem.  The first is
 to defer post counts, but this just delays the badness until a later call
 of `wp_update_term_count`.  The second is to define a per-taxonomy custom
 update callback using `update_count_callback`.

 Neither of these mechanisms is intended to improve performance, and both
 are obscure.  It would be better to by default increment or decrement post
 counts directly in the tt table in response to posts entering or leaving
 published (or other countable) stati, and reserve complete recalculations
 for special occasions (such as when a term is edited.)  Using this
 strategy, the 60 total queries in the example above could -- on publish --
 be replaced by a '''a single''' query that would look something like:

 `UPDATE {$wpdb->term_taxonomy} AS tt SET tt.count = tt.count + 1 WHERE
 tt.term_taxonomy_id IN ( .... )`

 (where the final list is a list of tt_ids.)

 This solution is implemented now as a plugin here:
 https://github.com/Automattic/lightweight-term-count-update.

 Patch based on this on the way soon.

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