mysql - usar - Mejora de una consulta usando muchas combinaciones internas a wp_postmeta, una tabla de clave/valor

redis vs mongodb

Question

Estoy trabajando con un sitio web de WordPress que está realizando la siguiente consulta, pero veo que esta consulta está haciendo muchas combinaciones internas y el sitio web tarda mucho en cargarse y baja mucho, y he estado tratando de crear una consulta que produzca el mismo resultado pero sin éxito todavía

Me gustaría saber cuál podría ser una mejor manera de hacer esto

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta color ON wp_posts.ID = color.post_id 
INNER JOIN wp_postmeta transmission ON wp_posts.ID = transmission.post_id 
INNER JOIN wp_postmeta model ON wp_posts.ID = model.post_id 
INNER JOIN wp_postmeta brand ON wp_posts.ID = brand.post_id 

AND color.meta_key = ''color'' 
AND color.meta_value = ''red'' 
AND transmission.meta_key = ''transmission'' 
AND transmission.meta_value = ''auto'' 
AND model.meta_key = ''model'' 
AND model.meta_value = ''model'' 
AND brand.meta_key = ''brand'' 
AND brand.meta_value = ''brand''

AND wp_posts.post_status = ''publish''
AND wp_posts.post_type = ''car''
ORDER BY wp_posts.post_title

Aquí está la salida de explicación.

+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys               | key      | key_len | ref                          | rows | Extra                                        |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | color         | ref    | post_id,meta_key            | meta_key | 768     | const                        |  629 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts      | eq_ref | PRIMARY,type_status_date,ID | PRIMARY  | 8       | tmcdb.color.post_id          |    1 | Using where                                  |
|  1 | SIMPLE      | brand         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.wp_posts.ID            |    4 | Using where                                  |
|  1 | SIMPLE      | transmission  | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.color.post_id          |    4 | Using where                                  |
|  1 | SIMPLE      | model         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.transmission.post_id   |    4 | Using where                                  |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+

Esquema de Wordpress aquí.

Answer 1

En WordPress hay una buena herramienta de consulta, la WP_Query. Para buscar en meta valores de meta, puede usar este código:

$args = array(
    ''post_type''  => ''post'',
    ''meta_query'' => array(
            array(
                ''key''     => ''fieldname'',
                ''value''   => ''fieldvalue'',
                ''compare'' => ''LIKE'',
            ),
        ),
    );
    $query = new WP_Query( $args );
    if ( $query->have_posts() ) {
        while ( $query->have_posts() ) {
            $query->the_post();
            $custom = get_post_custom();

            print_r($post);
            print_r($custom);
        }
    } else {
        // no posts found
    }
    wp_reset_postdata();

Para obtener más información sobre la API de consulta, visite este sitio, hay numerosos ejemplos: http://codex.wordpress.org/Class_Reference/WP_Query

Answer 2

Es esto mejor?

SELECT
            P.*,
            C.`meta_value` color,
            T.`meta_value` transmission,
            M.`meta_value` model,
            B.`meta_value` brand
    FROM
            `wp_posts` P
        JOIN
            `wp_postmeta` C
                ON P.`ID` = C.`post_id` AND C.`meta_key` = ''color''
        JOIN
            `wp_postmeta` T
                ON P.`ID` = T.`post_id` AND T.`meta_key` = ''transmission''
        JOIN
            `wp_postmeta` M
                ON P.`ID` = M.`post_id` AND M.`meta_key` = ''model''
        JOIN
            `wp_postmeta` B
                ON P.`ID` = B.`post_id` AND B.`meta_key` = ''brand''
    WHERE
            C.`meta_value` = ''red''
        AND
            T.`meta_value` = ''auto''
        AND
            M.`meta_value` = ''model''
        AND
            B.`meta_value` = ''brand''
        AND
            P.`post_status` = ''publish''
        AND
            P.`post_type` = ''car''
    ORDER BY
            P.`post_title`

Si todavía es lento, lo que probablemente sea, intente agregar este índice,

CREATE INDEX `IX-wp_postmeta-post_id-meta_key-meta_value`
    ON `wp_postmeta` (`post_id`, `meta_key`, `meta_value`);

También puede intentar agregar este índice a wp_post

CREATE UNIQUE INDEX `IX-wp_post-post_status-post_type-post_title-ID`
    ON `wp_post` (`post_stauts`, `post_type`, `post_title`, `ID`);

Cuanto más pueda limitar la lista de selección (el bit entre SELECT y FROM ), mejor. No tiene sentido devolver muchos datos que no usarás. Obtendrá el mejor rendimiento si toda la lista de selección está "cubierta" por un índice.

Answer 3

Esta es una base de datos de Wordpress, y es posible que se muestre reacio a realizar cambios extensos en el esquema, ya que podría romper otras partes de la aplicación o complicar las actualizaciones en el futuro.

La dificultad de esta consulta muestra una de las desventajas del diseño de valor de atributo de entidad . Ese diseño es flexible, ya que permite la creación de nuevos atributos en tiempo de ejecución, pero hace que muchas consultas contra tales datos sean más complejas de lo que serían con una tabla convencional.

El esquema para Wordpress no se ha optimizado bien. Hay algunos errores de indexación ingenuos, incluso en la versión más reciente 4.0.

Para esta consulta en particular, los siguientes dos índices ayudan:

CREATE INDEX `bk1` ON wp_postmeta (`post_id`,`meta_key`,`meta_value`(255));

CREATE INDEX `bk2` ON wp_posts (`post_type`,`post_status`,`post_title`(255));

El índice bk1 ayuda a buscar exactamente la meta clave y el valor correctos.

El índice bk2 ayuda a evitar el fileser.

Estos índices no pueden cubrir índices, porque post_title y meta_value son columnas TEXT , y son demasiado largos para post_title por completo. Tendría que cambiarlos a VARCHAR(255) . Pero eso corre el riesgo de romper la aplicación, si depende de almacenar cadenas más largas en esa tabla.

+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref                        | rows | filtered | Extra                 |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | wp_posts     | NULL       | ref  | bk2           | bk2  | 124     | const,const                |    1 |   100.00 | Using index condition |
|  1 | SIMPLE      | color        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | transmission | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | model        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | brand        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+

Answer 4

Para el rendimiento intente:

Sea explícito en las columnas que desea extraer. Vea qué índices puede necesitar o no. Limite la cantidad de filas que se están tirando.

Answer 5

Para resolver el problema de rendimiento con 10+ se une a consultas SQL en tablas innodb usando utf8 charset, cree un nuevo índice en postmeta:

Base de datos de respaldo primero. Reduce [wp_]postmeta.meta_key length a 191 para evitar el error "La clave especificada era demasiado larga; la longitud máxima de la clave es 767 bytes ".

ALTER TABLE wp_postmeta MODIFY meta_key VARCHAR(191);

Crear índice

CREATE INDEX wpm_ix ON wp_postmeta (post_id, meta_key);

Answer 6

Parece que estás tratando de obtener un conjunto de resultados con una fila por publicación de tipo car . Parece que quieres mostrar varios atributos de cada auto en la publicación, y esos están escondidos en postmeta .

Consejo profesional: nunca use SELECT * en el software a menos que sepa absolutamente por qué lo está haciendo. Especialmente con consultas que contienen muchas operaciones JOIN , SELECT * devuelve muchas columnas innecesarias y redundantes.

Hay un truco de diseño de consulta para saber para la tabla de postmeta WordPress. Si quieres obtener un atributo en particular, haz esto:

 SELECT p.ID, p.post_title,
        color.meta_value AS color
   FROM wp_posts AS p
   LEFT JOIN wp_postmeta AS color ON p.ID = color.post_id AND ''color'' = color.meta_key
  WHERE p.post_status = ''publish''
    AND /* etc etc */

Es muy importante comprender este patrón cuando haces lo que intentas hacer. Este patrón es obligatorio porque postmeta es un tipo peculiar de tabla llamada almacén de clave / valor. ¿Que está pasando aqui? Unas pocas cosas:

1. Con este patrón, obtendrá una fila para cada publicación, con algunas columnas de la tabla de posts y un atributo particular de la tabla de postmeta .
2. Estás LEFT JOIN en la tabla de postmeta , por lo que aún obtienes una fila si falta el atributo.
3. Está utilizando un nombre de alias para la tabla postmeta . Aquí está el postmeta AS color .
4. Está incluyendo el selector para meta_key (aquí está ''color'' = color.meta_key ) en la condición ON de la unión.
5. Está utilizando un alias en su cláusula SELECT para presentar el elemento postmeta.meta_value con un nombre de columna apropiado. Aquí está color.meta_value AS color .

Una vez que se acostumbre a emplear este patrón, puede apilarlo, con una cascada de operaciones LEFT JOIN , para obtener muchos atributos diferentes, como ese.

     SELECT wp_posts.ID, wp_posts.post_title, wp_posts.whatever,
            color.meta_value        AS color,
            transmission.meta_value AS transmission,
            model.meta_value        AS model,
            brand.meta_value        AS brand
       FROM wp_posts

  LEFT JOIN wp_postmeta  AS color 
         ON wp_posts.ID = color.post_id        AND color.meta_key=''color''

  LEFT JOIN wp_postmeta  AS transmission
         ON wp_posts.ID = transmission.post_id AND transmission.meta_key=''transmission''

  LEFT JOIN wp_postmeta  AS model
         ON wp_posts.ID = model.post_id        AND model.meta_key=''model''

  LEFT JOIN wp_postmeta  AS  brand
         ON wp_posts.ID = brand.post_id        AND brand.meta_key=''brand''

      WHERE wp_posts.post_status = ''publish''
        AND wp_posts.post_type = ''car''
   ORDER BY wp_posts.post_title

He hecho un montón de sangrado en esta consulta para que sea más fácil ver el patrón. Puede preferir un estilo de sangrado diferente.

Es difícil saber por qué tenía problemas de rendimiento con la consulta en su pregunta. Posiblemente sea porque estaba obteniendo una explosión combinatoria con todas las operaciones INNER JOIN que luego se filtraron. Pero en cualquier caso, la consulta que mostró probablemente no estaba devolviendo filas.

Si todavía tiene problemas de rendimiento, intente crear un índice compuesto en postmeta en las (post_id, meta_key, meta_value) . Si está creando un plugin de WordPress, probablemente sea un trabajo para hacerlo en el momento de la instalación del complemento.

Answer 7

Speeding up wp_postmeta se detalla aquí: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

¿ Y por qué las referencias a wp_postmeta son tan lentas?

Answer 8

Suponiendo que realmente pueda cambiar el código que maneja los resultados, lo haría una consulta mucho más simple y usaría el código para filtrar los resultados.

SELECT [wp_posts fields you care about], wp_postmeta.meta_key, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE wp_posts.post_status = ''publish'' AND wp_posts.post_type = ''car''
   AND wp_postmeta.meta_key IN (''color'', ''transmission'', ''model'', ''brand'')
ORDER BY wp_posts.post_title, wp_postmeta.meta_key, wp_postmeta.meta_value;

O bien, podrías hacer algo como ...

SELECT [wp_posts fields desired], COUNT(*) AS matchCount
FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = ''publish'' AND wp_posts.post_type = ''car''
    AND ((meta_key = ''color'' AND meta_value = ''red'')
        OR (meta_key = ''transmission'' AND meta_value = ''auto'')
        OR [etc...]
        )
GROUP BY wp_posts.ID
HAVING matchCount = [number of key-value pairs you''re checking]
;