Can ordering post list by meta_value cause performance issue?

meta_query & tax_query behavior

When you’re defining a meta/tax_query and make use of the new array style argument list for multiple sets of term or custom field keys, then your query might look close to the following

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

This would be the result:

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) ) 
...

^{Example code taken from this trac ticket by @Otto. Please subscribe there to follow its progress and vote for getting a change into core.}

The problem with the query …

… basically is that you’ll get a JOIN for every column. It’s not necessary, as the tables are already melted together with the 1^st JOIN. Sadly core simply behaves like that at the current state 3.3.x and will stay like this in 3.4.

Maybe a solution?

Your best chance would be to intercept the posts_clauses filter in your plugin and manually change the query. The real problem with this solution is that you’d have to do a str_replace( $search, $replace, $query ); in your plugin. On the one hand, that’s maybe slowing things down and on the other hand you’d have to follow this ticket as when this moves in, your plugin will simply break (better leave a link to the ticket in your plugins code).

What can I do?

Jump into trac and add your thoughts & patches to the ticket. If you’re a real hero, then you could try to get at the meta_query problem too.

Community would thank you 🙂

^{If you think this img is inappropriate, just delete it.}