Trac 40351: Term counts scale poorly

[peterwilsoncc]

I'm after some assistance wrapping up WordPress#40351 to improve the scaloing of term counts.

The original report by Matt Perry on the linked ticket is quite detailed. It starts with the summary:

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.

In WordPress 5.6 I got quite close to patching this in WP but ended up reverting as the code broke term recounts when secondary objects were updated. See WordPress#51630.

It would be great if the performance team could assist with wrapping the patch up. I've got a PR to revert the revert on the WordPress-Develop repo https://github.com/WordPress/wordpress-develop/pull/1601

As noted on the original ticket, this is the final step that I need assistance with:

_wp_prevent_term_counting() was designed to prevent term counting when adding/removing terms during wp_insert_post() and defer the changes to the transition_post_status hook. This is to prevent double counting a change (ie, adding a count of two for a single item).

Unfortunately it proved too blunt as terms added to another object (either a different post, or another object type) went uncounted too. There are some examples in #51630.

The LTCU plugin tracks the individual object IDs for the equivalent functionality. I considered adding something like that but as WP is functional, the function's signature became rather unwieldy.

[tillkruss]

@spacedmonkey Any thoughts?

[spacedmonkey]

I will have to look into this. This is not a simple fix and I need to getting up to speed. I am busy with other things at the moment. I will back to this in a couple of weeks.

[XVII]

This was a big performance boost for us with large improvements on large updates with 000's items.

Looking forward to seeing this make it into core.