Execute a large WP_Query with many “AND” Meta_Queries?

I have a custom post type product. Visitors can search products with many filters. They can set the color, the size, the price, etc. The details of the product are stored with Advanced Custom Fields (ACF).

My solution was a WP_Query with meta_queries. All WP_Meta_Queries are in AND-relations.

My problem is: If the visitor creates more filter for the product, the query will always be slower. I think the INNER JOINs on wp_postmeta are the problem. I have six INNER JOINs on this table.

Is there a solution for this problem?

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.