[wp-trac] [WordPress Trac] #19729: WP_Meta_Query is inefficient when only specifying keys in "OR" query

[WordPress Trac]

#19729: WP_Meta_Query is inefficient when only specifying keys in "OR" query
-------------------------+-----------------------------
 Reporter:  joehoyle     |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Query        |    Version:  3.3
 Severity:  normal       |   Keywords:
-------------------------+-----------------------------
 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.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/19729>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software