WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 19 months ago

Last modified 12 months ago

#19729 closed enhancement (fixed)

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

Reported by: joehoyle Owned by: ryan
Milestone: 3.5 Priority: normal
Severity: normal Version: 3.3
Component: Query Keywords: has-patch 3.5-early commit needs-unit-tests
Focuses: Cc:

Description

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.

Attachments (4)

meta.php (1.1 KB) - added by joehoyle 2 years ago.
meta.diff (1.1 KB) - added by joehoyle 2 years ago.
19729.diff (1.2 KB) - added by SergeyBiryukov 19 months ago.
query.diff (2.4 KB) - added by joehoyle 16 months ago.
Unit Test for WP_Meta_query

Download all attachments as: .zip

Change History (28)

joehoyle2 years ago

joehoyle2 years ago

comment:1 nacin2 years ago

  • Milestone changed from Awaiting Review to 3.4

Nice.

comment:2 AdamBackstrom2 years ago

  • Cc adam@… added

comment:3 Viper007Bond2 years ago

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',
	),
) );

comment:4 garyc402 years ago

  • Keywords has-patch added

comment:5 aaroncampbell2 years ago

  • Cc aaroncampbell added

comment:6 scribu2 years ago

  • Cc scribu added

comment:7 ryan2 years ago

Perhaps we should do this with #18158 in 3.5.

comment:8 nacin2 years ago

  • Keywords 3.5-early added
  • Milestone changed from 3.4 to Future Release

Agreed. Let's not forget.

comment:9 batmoo2 years ago

  • Cc batmoo@… added

comment:10 markjaquith21 months ago

  • Milestone changed from Future Release to 3.5

Resurrecting this for 3.5.

comment:11 joehoyle21 months ago

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

comment:12 ryan20 months ago

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

comment:13 follow-up: joehoyle20 months ago

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

comment:14 in reply to: ↑ 13 SergeyBiryukov20 months ago

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/

comment:15 sc0ttkclark20 months ago

  • Cc lol@… added

comment:16 nacin19 months ago

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

comment:17 nacin19 months ago

  • Keywords needs-refresh commit needs-unit-tests added

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.

SergeyBiryukov19 months ago

comment:18 SergeyBiryukov19 months ago

  • Keywords needs-refresh removed

comment:19 ryan19 months ago

[UT1062]

There's a start on post meta query tests.

comment:20 ryan19 months ago

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.

Last edited 19 months ago by ryan (previous) (diff)

comment:21 ryan19 months ago

  • Owner set to ryan
  • Resolution set to fixed
  • Status changed from new to closed

In [22103]:

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

comment:22 joehoyle16 months ago

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

joehoyle16 months ago

Unit Test for WP_Meta_query

comment:23 scribu16 months ago

[UT1173]

The only thing I had to change was

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

with

@ticket 19729
Note: See TracTickets for help on using tickets.