Optimize wordpress WP_Term_Query mysql request

wp-optimized-taxonomy-mysql.php

<?php
// Example long query (2.557 seconds):
// @see https://radleysustaire.com/s3/1c66a3/

// Example optimized query (0.316 seconds):
// @see https://radleysustaire.com/s3/fb794e/

// Example of what your code would look like:
$args = array(
	'taxonomy' => array( 'account-menu' ),
	'meta_query' => $meta_query,
	'exclude' => $excluded_terms,
	'meta_key' => 'priority',
	'orderby' => 'meta_value_num',
	'order' => 'DESC',
);

add_filter( 'terms_pre_query', 'dtl_optimize_sql', 20, 2 );
$terms = get_terms( $args );
remove_filter( 'terms_pre_query', 'dtl_optimize_sql', 20 );





/**
 * Optimize a WP_Term_Query request by using a subquery to get all terms and to reduce search space.
 *
 * @param $terms array
 * @param &$query WP_Term_Query  By reference, modifications to this object stick
 *
 * @return array|null
 */
function dtl_optimize_sql( $terms, $query ) {
	global $wpdb;
	$sql = $query->request;
	
	// We'll remove the tt column
	$select_search = 'SELECT DISTINCT t.*, tt.*';
	if ( false === strpos($sql, $select_search) ) return $terms;
	
	// We'll replace the FROM with a subquery
	$from_search = 'FROM wp_terms AS t';
	if ( false === strpos($sql, $from_search) ) return $terms;
	
	$join_search = 'STRAIGHT_JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id';
	if ( false === strpos($sql, $join_search) ) return $terms;
	
	// We won't need the tt table, we won't need to check the taxonomy twice
	$where_match = preg_match('/tt.taxonomy IN \((.*?)\) AND/', $sql, $matches);
	if ( false === $where_match ) {
		return $terms;
	}else{
		$where_tt_search = $matches[0]; // 'account-menu'
		$where_post_types = $matches[1]; // 'account-menu'
	}
	
	// 1. Remove TT (term-taxonomy) join
	$sql = str_replace( $select_search, 'SELECT DISTINCT t.*', $sql );
	
	// 2. Replace FROM with subquery
	$subquery = <<<SQL
FROM (
   SELECT DISTINCT
     subt.*,
     
     /* Individual keys as to not duplicate the term_id column */
     subtt.term_taxonomy_id as 'term_taxonomy_id',
     subtt.taxonomy as 'taxonomy',
     subtt.description as 'description',
     subtt.parent as 'parent',
     subtt.count as 'count'

   FROM {$wpdb->terms} AS subt

   STRAIGHT_JOIN {$wpdb->term_taxonomy} AS subtt
   ON subt.term_id = subtt.term_id

   WHERE subtt.taxonomy IN ( {$where_post_types} )
) AS t
SQL;

	$sql = str_replace( $from_search, $subquery, $sql );
	
	// 3. Replace taxonomy join taxonomy condition
	$sql = str_replace( $join_search, '', $sql );
	
	// 4. Replace taxonomy join taxonomy condition
	$sql = str_replace( $where_tt_search, '', $sql );
	
	// Save the request so get_terms will use it
	$query->request = $sql;
}