Meta Query request very slow

• Teranya

• April 23, 2021 at 9:14 pm

Hello,

I’m encountering something odd with 2 WP_Query. I’ve got 2 Content types “Bien immobilier” and “Programme”. They are mostly the same except some points, but here is there biggest difference :
– Bien immobilier has only one price and one typology per post,
– Programme has a repeater field with one price attached to one typology (often 2-5 per post)

So I’ve got 2 WP_Query.

Bien immobilier :

   $args = array(
        'numberposts'   => -1,
        'post_type'     => array('bien-immobilier'),
        'post_status'   => 'publish',
        'orderby'       => 'date',
        'fields'        => 'ids',
        'order'         => 'DESC',
        'meta_query'    => [
            'relation'  => 'AND',
            array(
                'key' => 'type_logement',
                'value' => $type_logement,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'typologie',
                'value' => $typologie,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'prix',
                'value' => $prix,
                'type' => 'NUMERIC',
                'compare' => '<='
            ),
            array(
                'key' => 'infos_logement_superficie',
                'value' => $superficie,
                'type' => 'NUMERIC',
                'compare' => '>='
            ),

            array(
                'relation' => 'AND',
                array(
                    'relation' => 'OR',
                    array(
                        'key' => 'lieu',
                        'value' => $lieux,
                        'type' => 'NUMERIC',
                        'compare' => '=',
                    ),
                    array(
                        'key' => 'departement',
                        'value' => $lieux,
                        'type' => 'NUMERIC',
                        'compare' => '=',
                    ),
                ),
            ),
            array(
                'key' => 'annee_livraison',
                'value' => $date,
                'type' => 'NUMERIC',
                'compare' => '<='
            ),

        ],
    );
    $produits = new WP_Query($args);

Programme :

 function recherche_prix_prog($where){
        $where = str_replace("meta_key = 'prix_$", "meta_key LIKE 'prix_%", $where);
        return $where;
    }
    add_filter('posts_where', 'recherche_prix_prog');

    $args = array(
        'numberposts'   => -1,
        'post_type'     => array('programme'),
        'post_status'   => 'publish',
        'orderby'       => 'date',
        'fields'        => 'ids',
        'order'         => 'DESC',
        'meta_query'    => [
            'relation'      => 'AND',
            array(
                'key' => 'type_logement',
                'value' => $type_logement,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'prix_$_typo_prog',
                'value' => $typologie,
                'compare' => 'LIKE',
            ),
            array(
                'key' => 'prix_$_prix_prog', //groupe prix, sub_field prix_prog
                'value' => $prix,
                'type' => 'NUMERIC',
                'compare' => '<='
            ),
            array(
                'relation' => 'AND',
                array(
                    'relation' => 'OR',
                    array(
                        'key' => 'lieu',
                        'value' => $lieux,
                        'type' => 'NUMERIC',
                        'compare' => '=',
                    ),
                    array(
                        'key' => 'departement',
                        'value' => $lieux,
                        'type' => 'NUMERIC',
                        'compare' => '=',
                    ),
                ),
            ),

            array(
                'key' => 'annee_livraison',
                'value' => $date,
                'type' => 'NUMERIC',
                'compare' => '<='
            ),
            array(
                'key' => 'dispositif',
                'value' => $dispositif,
                'compare' => 'LIKE'
            ),

        ],
    );
    $produits = new WP_Query($args);

Both are working. But “Bien immobilier” is way slower (16 sec) than “Programme” (5 sec). It’s very strange because “Bien immobilier” has 74 results in a simple list and “Programme” has 189 results + another separate request to show markers on a map.

I don’t understand why the lighter is slower than the heaviest.

I really don’t find…so I’m asking for your help !

Thank you so much 🙂

• John Huebner

• April 24, 2021 at 1:00 am

This is because you are doing so many “LIKE” comparisons, not only on the content but also on the field names.

I would eliminate these “LIKE” comparisons from all of the queries.

This will not be easy because you already have a ton of content on the site. If you want to make the change it will mean updating every post.

This old post covers the basics of how to avoid these types of performance issues with queries https://web.archive.org/web/20190814230622/https://acfextras.com/dont-query-repeaters/

Short of this and updating all of the content you could try using transients or some other type of caching mechanism to store the results of these queries to they do not need to be run every time you need to do them.

• Teranya

• April 26, 2021 at 5:30 pm

Thanks for your answer.

It seems that it comes from this part :

  array(
                'key' => 'prix',
                'value' => $prix,
                'type' => 'NUMERIC',
                'compare' => '<='
            ),

Because when I comment this part, the search is faster. But I don’t know how to be more efficient than that…

Concerning the change from LIKE to =, I tried. But nothing appears when I’m using it. Is it possible to use = with CHAR ? Because it’s well stored as “Appartement”, “Maison” or “Local commercial” (and in the search, I have the capitalized word, so strictly the same as it’s stored in database).

In every case, I can’t ask my client to come back to all his products to update them, he will be mad ^^

• John Huebner

• April 26, 2021 at 9:11 pm

There isn’t any way to speed up the queries. You either need to alter the data to let you do more efficient queries as I have said or you need to look into some type of caching mechanism.