id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
19729,"WP_Meta_Query is inefficient when only specifying keys in ""OR"" query",joehoyle,ryan,"Suppose I have a WP_Query like so:


{{{
$query = new WP_Query( array( 'meta_query' => array( 
	array( 'key' => 'foo' ),
	array( 'key' => 'bar', 'value' => 'val2' ),
	array( 'key' => 'baz' ),
	array( 'key' => 'froo' ) 'relation' => 'OR' ) ) );
}}}

Currently, `WP_Meta_Query` will JOIN 4 copies of postmeta and use one JOIN per key - however, when using a relation of OR, it's not necessary to use more than one meta JOIN for the key-only queries.

The attached patch will pull out all the ""key only"" queries and use a single JOIN for them, leaving the remaining quires in-tact as they were before.

This can only be done with an OR query, of course.

Below is the SQL query before the patch:

{{{
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'foo'
OR  (mt1.meta_key = 'bar' AND CAST(mt1.meta_value AS CHAR) = 'val2')
OR mt2.meta_key = 'baz'
OR mt3.meta_key = 'froo' ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
}}}

The same after the patch:

{{{
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'foo'
OR wp_postmeta.meta_key = 'baz'
OR wp_postmeta.meta_key = 'froo'
OR  (mt1.meta_key = 'bar' AND CAST(mt1.meta_value AS CHAR) = 'val2') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
}}}

I ran this on a large site (>100k posts), so not sure if the performance gains will be so drastic for smaller sites. Before the patch, with 3 JOINS took around 43seconds. After the patch with 1 Join, 5.2ms. Not sure why the hug difference, presumably multiple JOINS was killing it somehow.",enhancement,closed,normal,3.5,Query,3.3,normal,fixed,has-patch 3.5-early commit needs-unit-tests,adam@… aaroncampbell scribu batmoo@… lol@…