Consulta meta_query extremadamente lenta en WordPress - Soluciones

La respuesta corta es que los metadatos en WordPress no están diseñados para ser utilizados como datos relacionales. Obtener publicaciones basadas en múltiples condiciones de sus metadatos no es el propósito detrás de los metadatos. Por lo tanto, las consultas, estructuras de tablas e índices no están optimizados para eso.

La respuesta más detallada:

Lo que tu Meta-Query genera es algo como esto:

SELECT   wp_4_posts.* FROM wp_4_posts  
INNER JOIN wp_4_postmeta ON (wp_4_posts.ID = wp_4_postmeta.post_id)
INNER JOIN wp_4_postmeta AS mt1 ON (wp_4_posts.ID = mt1.post_id)
INNER JOIN wp_4_postmeta AS mt2 ON (wp_4_posts.ID = mt2.post_id)
INNER JOIN wp_4_postmeta AS mt3 ON (wp_4_posts.ID = mt3.post_id)
INNER JOIN wp_4_postmeta AS mt4 ON (wp_4_posts.ID = mt4.post_id) 
WHERE 1=1  
AND wp_4_posts.post_type = 'post' 
AND (wp_4_posts.post_status = 'publish' OR wp_4_posts.post_status = 'private') 
AND ( (wp_4_postmeta.meta_key = '_author' AND CAST(wp_4_postmeta.meta_value AS CHAR) = 'Test')
OR  (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Test')
OR  (mt2.meta_key = '_contributor_1' AND CAST(mt2.meta_value AS CHAR) = 'Test')
OR  (mt3.meta_key = '_contributor_2' AND CAST(mt3.meta_value AS CHAR) = 'Test')
OR  (mt4.meta_key = '_contributor_3' AND CAST(mt4.meta_value AS CHAR) = 'Test') ) GROUP BY wp_4_posts.ID ORDER BY wp_4_posts.post_date DESC

Analicemos cómo MySQL maneja esta consulta (EXPLAIN):

    id      select_type     table           type    possible_keys                   key                     key_len ref                             rows    Extra
    1       SIMPLE          wp_4_posts      range   PRIMARY,type_status_date        type_status_date        124     NULL                            5       Using where; Using temporary; Using filesort
    1       SIMPLE          wp_4_postmeta   ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt1             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt2             ref     post_id,meta_key                post_id                  8      wordpress.mt1.post_id           1       Using where
    1       SIMPLE          mt3             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_posts.ID         1
    1       SIMPLE          mt4             ref     post_id,meta_key                post_id                  8      wordpress.wp_4_postmeta.post_id 1       Using where

Lo que puedes observar es que MySQL realiza una selección en wp_posts y une 5 veces la tabla wp_postmeta. El tipo ref indica que MySQL debe examinar todas las filas en esta tabla, haciendo coincidir el índice (post_id, meta_key) comparando un valor de columna no indexado con tu cláusula where, y eso para cada combinación de filas de la tabla anterior. El manual de MySQL indica: "Si la clave que se usa coincide solo con unas pocas filas, este es un buen tipo de unión". Y ese es el primer problema: en un sistema WordPress promedio, el número de metadatos por publicación puede crecer fácilmente hasta 30-40 registros o más. La otra clave posible meta_key crece con tu cantidad de publicaciones. Entonces, si tienes 100 publicaciones y cada una tiene un meta _publisher, habrá 100 filas con este valor como meta_key en wp_postmeta, por supuesto.

Para manejar todos estos posibles resultados, MySQL crea una tabla temporal (using temporary). Si esta tabla se vuelve demasiado grande, el servidor generalmente la almacena en el disco en lugar de la memoria. Otro posible cuello de botella.

Soluciones posibles

Como se describe en las respuestas existentes, podrías intentar optimizar la consulta por tu cuenta. Eso puede funcionar bien para tus necesidades, pero podría causar problemas a medida que las tablas de publicaciones/metadatos crezcan.

Pero si deseas utilizar la API de Consultas de WordPress, deberías considerar usar Taxonomías para almacenar datos por los que quieras buscar publicaciones.

Puede que llegue un poco tarde al juego, pero me encontré con el mismo problema. Al construir un plugin para manejar búsquedas de propiedades, mi opción de búsqueda avanzada consultaba hasta 20 entradas meta diferentes para cada publicación para encontrar aquellas que coincidieran con los criterios de búsqueda.

Mi solución fue consultar la base de datos directamente usando el global $wpdb. Consulté cada entrada meta individualmente y almacené los post_ids de las publicaciones que coincidían con cada criterio. Luego hice una intersección en cada uno de los conjuntos coincidentes para obtener los post_ids que coincidían con todos los criterios.

Mi caso era relativamente simple porque no tenía ningún elemento OR que necesitara considerar, pero podrían incluirse con bastante facilidad. Dependiendo de qué tan compleja sea tu consulta, esta es una solución funcional y rápida. Aunque, admito que es una opción pobre en comparación con poder hacer una verdadera consulta relacional.

El código a continuación ha sido simplificado enormemente de lo que usé, pero puedes entender la idea a partir de él.

class property_search{

public function get_results($args){
    $potential_ids=[];
    foreach($args as $key=>$value){
        $potential_ids[$key]=$this->get_ids_by_query("
            SELECT post_id
            FROM wp_postmeta
            WHERE meta_key = '".$key."'
            AND CAST(meta_value AS UNSIGNED) > '".$value."'
        ");//se necesitaría crear un nuevo operador para manejar cada tipo de dato y comparación.
    }

    $ids=[];
    foreach($potential_ids as $key=>$temp_ids){
        if(count($ids)==0){
            $ids=$temp_ids;
        }else{
             $ids=array_intersect($ids,$temp_ids);
        }
    }

    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $args = array(
        'posts_per_page'=> 50,
        'post_type'=>'property',
        'post_status'=>'publish',
        'paged'=>$paged,
        'post__in'=>$ids,
    );
    $search = new WP_Query($args);
    return $search;
}

public function get_ids_by_query($query){
    global $wpdb;
    $data=$wpdb->get_results($query,'ARRAY_A');
    $results=[];
    foreach($data as $entry){
        $results[]=$entry['post_id'];
    }
    return $results;
}

}

wp_postmeta tiene índices ineficientes. Aquí hay una discusión sobre esto, junto con soluciones recomendadas:

http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Las consultas OR son realmente costosas.

Tienes demasiadas claves, pero asumamos que no puedes cambiar eso ahora. La otra cosa que puedes hacer, sin demasiado código, es modificar el número de posts que estás obteniendo, cambia 'posts_per_page' a 10, o un número mayor, y observa cómo cambia el rendimiento.