Execute a large WP_Query with many “AND” Meta_Queries?

by

You might need a 180° solution here 😉
Think about the problem once more and try this:

As a visitor I can filter by color and size.

So your solution might be:

...
JOIN ... (A)
... (B)
... (C)
WHERE (A.meta_key = 'color' AND A.meta_value="red")
OR (B.meta_key = 'color' AND B.meta_value="blue")
...
AND (C.meta_key = 'size' AND C.meta_value = 1)
...

But actually you can drop some joins using IN:

JOIN ... (A)
... (B)
WHERE (A.meta_key = 'color' AND A.meta_value IN ('red', 'yellow'))
AND (B.meta_key = 'size' AND B.meta_value IN (1, 2, 3))

So start using “IN” for comparing values like in the manual:

$args = array(
    'post_type'  => 'my_custom_post_type',
    'meta_key'   => 'age',
    'orderby'    => 'meta_value_num',
    'order'      => 'ASC',
    'meta_query' => array(
        array(
            'key'     => 'age',
            'value'   => array( 3, 4 ),
            'compare' => 'IN',
        ),
    ),
);
$query = new WP_Query( $args );

You can even start guessing and have it with only one or no join at all:

WHERE meta_key IN ('color', 'size')
  AND meta_value IN ('red', 'blue', 123, 45)

This would be fast but might end up in lots of false positives.

Leave a Comment