[wp-hackers] Expensive meta query - Is there a better way?

[Gavin Pearce]

Casey Bisson is correct of course - however if you need to use the
post_meta table you could try a custom query along these lines:

SELECT * from wp_posts 
WHERE ID IN (
			SELECT post_id from pp_2_postmeta 
			WHERE ((meta_key = 'key1' AND meta_value =
'value')
			OR (meta_key = 'key2' AND meta_value = 'value')
			OR (meta_key = 'key3' AND meta_value = 'value')
			OR (meta_key = 'key4' AND meta_value = 'value')
			OR (meta_key = 'key5' AND meta_value = 'value'))
		)
AND post_type = 'custom_post_type'
AND post_status = '...'
etc

Sure this can be written more efficiently if you want to refine it
further - but should be significantly quicker than the 20 second +
queries you were having.

Gav


-----Original Message-----
From: wp-hackers-bounces at lists.automattic.com
[mailto:wp-hackers-bounces at lists.automattic.com] On Behalf Of Casey
Bisson
Sent: 09 February 2012 14:13
To: wp-hackers at lists.automattic.com
Cc: wp-hackers at lists.automattic.com
Subject: Re: [wp-hackers] Expensive meta query - Is there a better way?


The post meta is really poorly structured for queries. If I remember
correctly, the default schema doesn't even have an index on the value
column, but that's just the start of reasons why queries against that
table run slowly. 

You can add indexes to the post meta table, but you'd do well to
consider using the taxonomies tables instead. Those are very well
architected for post selection queries. 

The rules I follow for my sites:

Use postmeta for fielded data displayed once the post is found. 

Use taxonomies to find the posts to display. 

--Casey

On Feb 9, 2012, at 3:57 AM, Robert Lusby <nanogwp at gmail.com> wrote:

> Hello,
> 
> We're running a multiple meta query lookup in WordPress - however the
DB query most be expensive as it's result in very slow (20/30 second)
response times in PHP with only 300 posts (total) in the DB. Query
below.
> 
> Is there a better way of doing the below? Essentially if $value is
equal to key1 or key2 or key3 or key4 or key5 (the keys are numbered
consecutively).
> 
> 
> 'post_type' => 'custom_post_type',
> 'meta_query' => array(
>        'relation' => 'OR',
>        array(
>            'key' => 'key1',
>            'value' => $value,
>            'compare' => '=',
>        ),
>        array(
>            'key' => 'key2',
>            'value' => $value,
>            'compare' => '=',
>        ),
>        array(
>            'key' => 'key3',
>            'value' => $value,
>            'compare' => '=',
>        ),
>        array(
>            'key' => 'key4',
>            'value' => $value,
>            'compare' => '=',
>        ),
>        array(
>            'key' => 'key5',
>            'value' => $value,
>            'compare' => '=',
>        ),
>    )
> );
> 
> Thanks,
> Rob
> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
_______________________________________________
wp-hackers mailing list
wp-hackers at lists.automattic.com
http://lists.automattic.com/mailman/listinfo/wp-hackers