Make WordPress Core

Opened 14 months ago

Last modified 7 months ago

#40351 new enhancement

Term post re-counts scale poorly, are common and difficult to avoid

Reported by: mattoperry Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.8
Component: Taxonomy Keywords: needs-patch needs-unit-tests
Focuses: performance Cc:


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.

Change History (2)

This ticket was mentioned in Slack in #core by mattoperry. View the logs.

7 months ago

#2 @desrosj
7 months ago

  • Keywords needs-patch needs-unit-tests added
Note: See TracTickets for help on using tickets.