WP_Meta_Query is inefficient when only specifying keys in "OR" query

[joehoyle]

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.

Nice.

Quite the speed improvement!

And here's an easier to read (IMO) of the example:

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

Perhaps we should do this with #18158 in 3.5.

Agreed. Let's not forget.

Resurrecting this for 3.5.

Just wanted to bump this, has-patch, presuming needs some peer review?

We really need some unit tests here. Then we could clear these patches faster.

I don't mind writing some basic tests - haven't done it for WordPress Core before though, could anyone point me in the right direction to get set up

Replying to joehoyle:

I don't mind writing some basic tests - haven't done it for WordPress Core before though, could anyone point me in the right direction to get set up

​http://make.wordpress.org/core/handbook/automated-testing/

If the relation is not 'OR' then we should be able to bypass that first foreach all together, instead assigning $queries = $this->queries.

I really want this, and it is a good commit candidate. But, needs a refresh (see also comment 16) and could use a unit test or two.

​[UT1062]

There's a start on post meta query tests.

I've been going through old meta query tickets and creating unit test coverage for each fixed bug.

​http://unit-tests.trac.wordpress.org/browser/trunk/tests/post/query.php

Contributions welcome. Soon I think I'll be comfortable landing 19729.diff.

In [22103]:

Improve performance of WP_Meta_Query when doing OR queries on meta keys. Props joehoyle, SergeyBiryukov. fixes #19729

Added some unit tests for WP_Meta_Query and specially one to address this ticket (and also ​http://unit-tests.trac.wordpress.org/ticket/122)

First unit test so let me know if anything needs changing

Unit Test for WP_Meta_query

​[UT1173]

The only thing I had to change was

@ticket http://core.trac.wordpress.org/ticket/19729

with

@ticket 19729

Follow-up: #24093