WordPress.org

Make WordPress Core

Opened 12 months ago

Last modified 6 days ago

#24093 new enhancement

WP_Meta_Query is inefficient when referencing the same keys in "OR" query

Reported by: sc0ttkclark Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 3.5.1
Component: Query Keywords: needs-refresh meta-query needs-unit-tests needs-patch
Focuses: Cc:

Description

This is part of the chain of thought from #19729

The corresponding fix for #19729 was done in [22103]

I believe this fix didn't take things far enough. For example, a meta_query that does a lot of comparing on the same meta_key, will have a JOIN and a WHERE for each query array. It shouldn't have to re-join the postmeta table on each query array though, since they are based on the same meta_key.

I'll attach a suggested patch that will only join if it needs to, and use the previous alias for the first meta_key joined table if it's already been joined.

Attachments (2)

24093.patch (1.8 KB) - added by sc0ttkclark 12 months ago.
Patch to join postmeta for meta_query only as needed
24093.1.patch (1.0 KB) - added by kevinfodness 3 months ago.
Patch for meta.php to improve query performance

Download all attachments as: .zip

Change History (16)

sc0ttkclark12 months ago

Patch to join postmeta for meta_query only as needed

comment:1 sc0ttkclark12 months ago

  • Version set to 3.5.1

comment:2 F J Kaiser9 months ago

Stumbled upon this patch/ticket while updating Codex on WP_Meta_Query. Nice patch! Finally.

Just one note: If you use a meta_query array

$query_args = array( 'meta_query' => array(
	'relation' => 'OR',
	array(
		'key' => 'foo_key',
		// 'value' => 'foo',
		// 'compare' => 'LIKE',
	),
	array(
		'key' => 'bar_key',
	),
) );
$meta_query = new WP_Meta_Query();
$meta_query->parse_query_vars( $query_args );
$mq_sql = $meta_query->get_sql(
	'post',
	$wpdb->posts,
	'ID',
	null
);

that only searches for two key/value pairs (so two subarrays), then it will still do a join on the native table names like

INNER JOIN wp_postmeta ON wp_post.ID = wp_postmeta.post_id

and then do an additional

INNER JOIN wp_postmeta AS mt{$i} ON (wp_posts.ID = {$alias}{$i}.post_id)

so the result will look like:

'join' => string ' 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)'

comment:3 sc0ttkclark9 months ago

Thanks for the feedback, I'll have a look and update the patch accordingly.

comment:4 F J Kaiser9 months ago

@sc0ttkclark Please keep in mind that the $alias will be used in all returning WHERE clauses, so that needs consideration as well.

comment:5 sc0ttkclark9 months ago

For sure, will look this patch over again and see what it'll take to bring it to it's original intention.

comment:6 wonderboymusic8 months ago

  • Keywords needs-refresh added; has-patch needs-testing dev-feedback removed

comment:7 wonderboymusic5 months ago

  • Keywords meta-query added

comment:8 wonderboymusic3 months ago

  • Keywords needs-unit-tests added

comment:9 nacin3 months ago

#26281 was marked as a duplicate.

comment:10 kevinfodness3 months ago

I wrote a patch that uses subqueries instead of JOIN operations and seems to be significantly more efficient. It should work fine for both OR and AND operations. I tested the patch on a relatively large site running WordPress 3.8.

I'd be interested to get the opinions of core contributors and see the results of unit tests to determine if this is a workable solution.

I posted a detailed writeup on my blog, here: http://kevinfodness.com/2014/01/21/performance-patch-for-wp_meta_query-in-wordpress-core/

The patch itself can be found here: http://kevinfodness.com/wp-content/uploads/2014/01/wp_meta_query.patch

kevinfodness3 months ago

Patch for meta.php to improve query performance

comment:11 kevinfodness3 months ago

The previous patch I submitted was git formatted - per guidelines I converted it to an SVN formatted patch and attached it to this issue.

comment:12 vprat8 weeks ago

  • Keywords has-patch added

Hi,

any update about that issue or plans to incorporate the fix in core for 3.8.x?

comment:13 Yourdigihands8 weeks ago

  • Keywords needs-patch added; has-patch removed

I installed the patch and for me it works very good. I hope this update will be incorporated into the next WP release.

comment:14 Mr.Defi6 days ago

kevinfodness Your modification works fantastic when You need to query thru multiply meta_keys but it breaks order_by meta_key statment.

Here is my $args fot WP_Query

$args = array(
	'post_type' => 'szkolenia',
	'posts_per_page' => -1,
	'meta_key' => 'crs_date_from',
	'orderby'  => 'meta_value',
	'order' => 'ASC'
);

This is how mysql query looks after Your patch:

SELECT wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'szkolenia' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 18 AND wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'crs_date_from' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC

Also this is an error from mysql:

#1054 - Unknown column 'wp_postmeta.meta_key' in 'where clause'

and before patch:

SELECT wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'szkolenia' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 18 AND wp_posts.post_status = 'private') AND (wp_postmeta.meta_key = 'crs_date_from' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC
Last edited 6 days ago by Mr.Defi (previous) (diff)
Note: See TracTickets for help on using tickets.