id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
18105,Improve JOIN efficency of tax_query,Otto42,,"Use of multiple items in a tax_query creates an inner join for each entry. This seems unnecessary.

Example query:

{{{
array(
  'tax_query' => array(
    'relation' => 'OR',
    array('taxonomy' => 'tax1', 'field' => 'slug', 'terms' => 'term1'),
    array('taxonomy' => 'tax2', 'field' => 'slug', 'terms' => 'term2'),
  )
)
}}}

Resulting SQL:

{{{
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts  
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
WHERE 1=1 AND
...
AND (wp_term_relationships.term_taxonomy_id IN (XXX) 
  OR tt1.term_taxonomy_id IN (YYY) ) 
...
}}}

Each new entry in the array creates another inner join on the term_relationships table, along with an associated select argument.

However, the term_relationships table is defined with these main keys:

{{{
CREATE TABLE $wpdb->term_taxonomy (
 term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,
 term_id bigint(20) unsigned NOT NULL default 0,
 taxonomy varchar(32) NOT NULL default '',
...
 PRIMARY KEY  (term_taxonomy_id),
 UNIQUE KEY term_id_taxonomy (term_id,taxonomy),
}}}


This basically means that the term_taxonomy_id is a unique ID for the row, and it's pointing to a unique relationship between some term and some taxonomy. Essentially, term_relationships defines what terms are in what taxonomies.

Therefore, the extra inner join and selection are entirely unnecessary. All references to a term in a taxonomy are unique. This isn't individual terms we're looking for in this query, but term+taxonomy. 

An equivalent SQL statement to the one given before would be this:

{{{
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts  
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1 AND
...
AND (wp_term_relationships.term_taxonomy_id IN (XXX, YYY) ) 
...
}}}

This is equivalent because both XXX and YYY point to a specific term+taxonomy combination, not just to a term.

For the similar relation = AND case (instead of relation = OR), this query would be equivalent:


{{{
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts  
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1 AND
...
AND (wp_term_relationships.term_taxonomy_id IN (XXX) 
AND (wp_term_relationships.term_taxonomy_id IN (YYY) ) 
...
}}}

We get a second statement in the where clause, but note the lack of a second inner join and lack of referencing it. This greatly improves performance. 

Only one inner join is ever actually required for any number of items in the tax_query, thanks to the pre-lookups being performed to find the proper term_relationship entries.

Similar enhancements might be available for the meta table, however that is more complex and may be more difficult to improve.
",enhancement,new,normal,Awaiting Review,Database,,normal,,needs-patch,24-7@… wordpress.trac@…