Defer taxonomy post count calculations to background tasks for performance

[roborourke]

When deleting, creating or updating a post it can take a long time if there's a lot of content as WordPress runs some slow queries to update the number of posts in a given taxonomy and store it in the db.

The following code can achieve this:

<?php

namespace Taxonomies\Optimization;

use WP_Taxonomy;

/**
 * Plugin bootstrapper.
 */
function bootstrap() {
	add_filter( 'register_taxonomy_args', __NAMESPACE__ . '\\update_taxonomy_callback' );
	add_action( 'cron_update_taxonomy_count', __NAMESPACE__ . '\\update_term_counts', 10, 3 );
}

/**
 * Update the taxonomy callback on all Taxonomies.
 *
 * @param array $args Array of arguments for the filtered taxonomy.
 *
 * @return array
 */
function update_taxonomy_callback( array $args ): array {
	$args['update_count_callback'] = __NAMESPACE__ . '\\update_count_callback';

	return $args;
}

/**
 * Update count callback.
 *
 * @param int[]  $terms The term_taxonomy_ids of terms to update.
 * @param WP_Taxonomy $taxonomy Taxonomy context to update within.
 *
 * @return void
 */
function update_count_callback( $terms, WP_Taxonomy $taxonomy ) : void {
	if ( ! wp_next_scheduled( 'cron_update_taxonomy_count', [ $terms, $taxonomy ] ) ) {
		$object_types = (array) $taxonomy->object_type;
		wp_schedule_single_event( time(), 'cron_update_taxonomy_count', [ $terms, $taxonomy->name, $object_types ] );
	}
}

/**
 * Update term counts, including all public stati.
 *
 * @param int[]  $terms The term_taxonomy_ids of terms to update.
 * @param string $tax_name Taxonomy name.
 * @param array $object_types An array of object types.
 *
 * @return void
 */
function update_term_counts( $terms, string $tax_name, array $object_types ) : void {
	global $wpdb;
	$post_stati = get_post_stati( [ 'public' => true ] );

	foreach ( array_filter( $terms ) as $term ) {

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
		$term_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"
				SELECT COUNT(*) FROM $wpdb->term_relationships, $wpdb->posts
				WHERE $wpdb->posts.ID = $wpdb->term_relationships.object_id
				AND post_status IN ('" . implode( "', '", $post_stati ) . "')
				AND post_type IN ('" . implode( "', '", $object_types ) . "')
				AND term_taxonomy_id = %d",
				$term
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared

		$wpdb->update( $wpdb->term_taxonomy, [ 'count' => $term_count ], [ 'term_taxonomy_id' => $term ] );
		do_action( 'edited_term_taxonomy', $term, $tax_name );
	}
}

[johnbillion]

Misc notes:

• The benefit of the Lightweight Term Count Update plugin is it avoids these queries altogether. On a site with a large number of term relationships this query can take tens of seconds.
• In theory something could be using a term count after publishing a post and with this change in place it will get the wrong count. Can't think of anything concrete off the top of my head though.
• Reminder that LTCU might go into core at some point: https://core.trac.wordpress.org/ticket/40351

[roborourke]

Cheers @johnbillion, ideally it’d go into core! Will see if we can maybe prompt someone at a8c to publish it to packagist and add some support on the trac ticket too.