Slow meta query with multi meta keys

Question

I have problem with filtering posts by custom fields. On my page I have form with 15 conditions (radiobutons and inputs) and i'd like to filter with them my custom post type PROJEKT. My problem is with very slow query when i use so many meta keys. This is my code:

$posts = get_posts(array(
    'numberposts'   => -1,
    'post_type'     => 'projekt',
    'post_status' => 'publish',
    's' => $s,
    'meta_query'    => 
         array(
        'relation'  => 'AND',
         array(
        'key'       => 'technologia_wyszukiwarka',
        'value'     => $techs,
        'compare'   => 'LIKE'       
        ),
        array(
        'key'       => 'zabudowa_wyszukiwarka',
        'value'     => $styls,
        'compare'   => 'LIKE'
        ),
        array(
            'key'       => 'garaz_wyszukiwarka',
            'value'     => $garazs,
            'compare'   => 'LIKE'
        ),
        array(
            'key'       => 'mdm_wyszukiwarka',
            'value'     => $mdms,
            'compare'   => 'LIKE'
        ),
        array(
            'key'       => 'powierzchnia_wyszukiwarka',
            'value'     => $pows,
            'compare'   => 'LIKE'
        ),
        array(
            'key'       => 'kondygnacja_wyszukiwarka',
            'value'     => $konds,
            'compare'   => 'LIKE'
        ),
        array(
            'key'       => 'ogrzewanie_wyszukiwarka',
            'value'     => $ogrzewanies,
            'compare'   => 'LIKE'
        ),  
        array(
            'key'       => 'liczba_pokoi_wyszukiwarka',
            'value'     => $pokojes,
            'compare'   => 'LIKE'
        ),      
        array(
            'key'       => 'inne_wyszukiwarka',
            'value'     => $innes,
            'compare'   => 'LIKE'
        ),  
        array(
            'key'       => 'kat_dachu_wyszukiwarka',
            'value'     => array($katods,$katdos),
            'compare'   => 'BETWEEN',
            'type' => 'NUMERIC'
        ),  

    ),
));

My query time WP_Query->get_posts is something about 10s. But when I delete last conditions kat_dachu_wyszukiwarka the time is shorter and acceptable 0.5 sec i checked it with Query Monitor. Unfortunately i need this field and few fields more. So my question is how can I do my query faster?

Answer 1

The problem here is that post meta wasn't designed for these kinds of searches, that's why the taxonomy tables exist, else categories and tags would be stored as post meta

• Post meta is optimised for fetching data when you already know the post ID
• Taxonomies are optimised for finding posts when you already know the data

A lot of your custom fields would be more appropriate as non-hierarchical taxonomies of the same name.

As for speeding up your meta query, I'm sorry to say but what you're trying to do is fundamentally expensive, and there's no way of getting around it. You can try to cache the result, but that's sub-optimal.

You will need to change your query to use taxonomies, which means modifying your data. Either by moving some post meta data to taxonomies, or storing the data in both post meta and taxonomies at the same time.

Potential ways to mitigate this involve elastic search clusters

You've also specified -1 for the max number of posts, which could be dangerous. If 10,000 posts are returned your server may timeout, and your Database may be overloaded. Always set an upper limit, even if it's one you never expect to reach

Summary

• Using meta queries to find posts is super expensive
• Post meta isn't built for filtering
• Taxonomies were built for filtering, and they're significantly faster for this
• Post meta is designed for when you already know which posts, so get_post_meta is a fast function
• Always put an upper limit on how many posts you're fetching