MySQL ¿Por qué esta consulta está lenta? ¿Me ayudan?

Pregunta para aquellos que saben más que yo, seguro alguno habrá, la cosa es así, la semana pasada tuve que hacer un cambio en las tablas del blog, pasé todo de MyISAM a InnoDB, una cuestión de modernización.

El primer impacto y más notable fue que el sitio cargaba más lento, se notaba, así que empecé a debuguear y encontré que el query más irrelevante, el que muestra los últimos comentarios en el menú de la derecha, era el culpable.

Pero ¿Por qué? Si tengo todas las tablas con sus índices bonitos y ese query siempre anduvo fantástico! Bueno, parece que el cambio de MyISAM a InnoDB tiene sus contras dependiendo el escenario pero ¿El query estaba mal hecho? 

Paso a mostrarles cómo es todo para que me ayuden si pueden...

Tengan en cuenta que estoy asociando comentarios con posts bajo un parámetro que está indexado en ambas tablas, id_noticia es un índice así que debería ser bien rápido ese JOIN, pero no: 

SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment 
, b.title , p.mail_gravatar 
FROM comentarios a 
JOIN posts b ON (a.id_noticia = b.id_noticia)
LEFT JOIN usuario_perfil p ON (a.id_usuario = p.id_usuario)
WHERE activo = 1  
ORDER BY date DESC 
LIMIT 0, 20;

Esto tarda la friolera de 2.63s, obviamente no iba a quedarme con eso y pensé ¿Y si hago un subquery? El tema es que es el JOIN el que jode porque, por alguna razón, se le trula y no quiere prestarle mucha atención al índice ¿Debería reconstruirlos?

Dí de alta un backup de la base de datos en mi servidor local, eso debería reconsturir todo ¿No? Pues no mi ciela, igual se cargaba esos 2 segundos extra.

Bueno, vamos a "la gronchada", si encierro el query de los últimos 20 comentarios y DESPUÉS hago el join éste será sólo contra 20 registros:

SELECT x.* , b.title , p.mail_gravatar 
FROM 
(SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment
FROM comentarios a 
WHERE a.activo = 1  
ORDER BY a.date DESC
LIMIT 0, 20) x

JOIN posts b ON (x.id_noticia = b.id_noticia)
LEFT JOIN usuario_perfil p ON (x.id_usuario = p.id_usuario);

Efectivamente esto funcionó de maravillas bajando el tiempo a apenas 0.4s, sí, es cochino, pero funciona.

La tabla comentarios tiene 160k registros ,la posts 9k, usuario_perfil ni siquiera tiene costo con el left join (lo ponga o lo saque da igual), el problema lo tiene el join más de manual de todos, en dos campos con índices, inclusive tengo un índice que combina id_notica y activo y otro con el id_usuario que sirve para otras consultas.

Y no,  nada, se caga en ello.

El explain me devuelve:

¿Alguno tiene idea de por qué es lento el primero? 

Update: usando WITH produce exactamente el mismo resultado que mi solución:

WITH
  cte1 AS (SELECT  id_noticia,id_comentario, name, email, id_usuario, comment, date FROM comentarios order by date desc limit 0,20 ),
  cte2 AS (SELECT id_noticia, title FROM posts)
SELECT cte1.id_comentario, cte1.id_noticia, cte1.name, cte1.email, cte1.id_usuario, cte1.comment 
, cte2.title FROM cte1 JOIN cte2 
WHERE cte1.id_noticia = cte2.id_noticia
order by cte1.date desc;

con lo que creo que tan mala no es

Si te gustó esta nota podés...
Invitame un café en cafecito.app


Otros posts que podrían llegar a gustarte...

Comentarios

  • Joaco     06/05/2023 - 10:51:54

    Fabio, ¿Das por sentado que levantar el backup en otro servidor te reconstruye los índices? Al menos en SQL Server, recuerdo a DBAdmins recreandolos a mano después de levantar el bkp.

    • Fabio Baccaglioni     06/05/2023 - 12:01:15

      es un import de un TXT que ejecuta paso a paso la creación de tabla e índices. Si eso no los crea... no hay forma de crearlos entonces :D jejeje porque inserta los indices.
      Ya los revisé y tiré optimize y analyze sin que cambie mucho la perf.

  • Arturo     06/05/2023 - 11:31:37

    Me la juego que se te pincharon los indices, los recostruiste ?

    • Fabio Baccaglioni     06/05/2023 - 11:58:53

      MySQL/MariaDB no es como Oracle, les hice un Optimize y un Analyze que son las unicas operaciones qeu se pueden hacer para esto. Pasé de 2.7s a 2.3s a 2.1s, pero no mucho más, sigue siendo un query de dos segundos para arriba y, si hay congestión, sube

  • Lostu     06/05/2023 - 11:34:11

    De hecho tiene sentido el fix, primero order by y limit, luego el join con la info que te falta.
    Tenes un index por el "date" q ordenas? posiblemente ayude tambien
    Y para que quede menos cochino podes usar un WITH
    WITH last_comments AS (
    SELECT ... FROM comentarios WHERE ... ORDER BY date LIMIT...
    )
    SELECT ...
    FROM last_comments
    INNER JOIN posts ...
    LEFT JOIN usuario_perfil ...

    • Fabio Baccaglioni     06/05/2023 - 12:03:36

      ja, si, lo mío fue rápido ¿Y si tiro todo esto en un subquery? Al hacer primero el corte por sólo 20 registros luego el join es super rápido, no hay que cruzar toda la tabla con la de posteos, sólo esos 20 y ya. Después se puede escribir más elegante. Mi cuestión es ¿Por qué un JOIN tan ANSI y correcto vendría a ser lento? Y sí, me mintieron toda la vida con SQL , jajaaj

  • Lucho     06/05/2023 - 11:38:56

    Yo agregaría más por fe q por conocimiento un índice para el order by (date).

    • Fabio Baccaglioni     06/05/2023 - 12:02:19

      ya tiene en esa! aunque es al pedo porque la fecha está hasta el segundo por lo que no te aporta mucho como índice y lo hice al pedo. No es el order by de fecha el que frena el query, lo sacás y sigue igual de lento, es cuando conecto una tabla con la otra por el parámetro en común. Por eso cuando las hice separadas con el subquery funcionó rápido.

  • Martin Aberastegue     06/05/2023 - 11:47:15

    Probaste con OPTIMIZE TABLE? Capaz ayuda a reorganizar los indices y datos después del cambio de motor. La otra que se me ocurre seria sino sumar una columna de activo a comentarios, otra cosa para mantener, y medio una atada con alambre pero quizás ayuda.

    • Fabio Baccaglioni     06/05/2023 - 11:59:59

      si, optimize mejoró un 5% como mucho 🤷‍♂️

  • Andrezgz     06/05/2023 - 12:17:04 Revisado: 06/05/2023 - 12:19:06

    Creo que como mencionás esa consulta performaba en MyISAM porque responde mejor a los FULL TABLE SCANS.

    Al pasar a InnoDB, hay que evitarlos. La forma en la que se estructuran los datos físicos no es propicia para consumirlos de esa manera. Lo crítico es la generación del archivo temporal (Using temporary) que luego requiere una segunda pasada para ordenarlo (Using filesort).

    MySQL solo puede elegir un indice por tabla en la consulta, que lo usa para relacionar con otras, filtrar y ordenar. En este caso pienso que no se elige ninguno porque:
    - la selectividad del indice que contiene activo no es muy buena (tal vez el 90% de los comentarios están activados) y se descarta
    - activo no es el "LEFT MOST PREFIX" de ese índice y por tanto no lo usa. Ej si está construido (id_noticia, activo) no es lo mismo que (activo, id_noticia)
    - tal vez falta un índice (date, activo) que podría ser útil para esta consulta
    -
    Tendría que probar con un juego de datos para analizar más a fondo, así en el aire es lo único que se me viene a la mente.

    • Andrezgz     06/05/2023 - 12:36:04

      Podrías compartir la salida del EXPLAIN de "la gronchada"?

      • Fabio Baccaglioni     06/05/2023 - 12:40:41 Revisado: 06/05/2023 - 12:41:03

        cómo no:

    • Fabio Baccaglioni     06/05/2023 - 12:48:05

      ahí revisando, resulta que el ORDER es el que se lleva una gran parte del tiempo, es decir, las dos tablas joinedas deben pesar lo suyo en memoria, activo =1 es irrelevante (99.9% activos, pero no se debería omitir, claro) pero ordena todo el choclo gigante y después ejecuta el LIMIT 0,20 que lo deja para el final.

      Por ende está tomando TODOS los comentarios cruzados con TODOS los posteos, los ordena por fecha y recién después selecciona 20. Y sí, es costoso pero ¿De qué otra forma se podría seleccionar lo mismo?

  • MT     06/05/2023 - 12:38:35

    Solo dos puntos para probar... Cambiar el JOIN por LEFT JOIN y ordenar por id_comentario (si es autoincremental).
    Y una consulta, sin el ORDER y sin el LIMIT cuanto demora?

    • Fabio Baccaglioni     06/05/2023 - 12:44:42

      el LIMIT no afecta el tiempo de respuesta, el ORDER si, sin el ORDER da inmediato
      JOIN/LEFT JOIN no tiene sentido porque no hay comentario sin posteo, son todos en un posteo de forma consistente (siempre tienen el ID) así que no te va a cambiar (de hecho, no cambia).

      pero me parece que va por el tema de la cantidad de comentarios, cruza toda la tabla entera y después hace el orden el orden de 300k registros es pesado.

      Por eso al hacer "la gronchada" tomo sólo 20 y recién ahí hago el join y vuela.

      pero ¿Cómo se puede tomar sólo los últimos 20 sin ordenar por fecha? ah, ni idea

      • MT     06/05/2023 - 14:56:23

        Y algo así que hace ?

        SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment, b.title , p.mail_gravatar
        FROM comentarios a
        JOIN posts b ON (a.id_noticia = b.id_noticia)
        LEFT JOIN usuario_perfil p ON (a.id_usuario = p.id_usuario)
        WHERE a.id_comentario IN (SELECT id_comentario FROM comentarios WHERE activo = 1 ORDER BY date DESC LIMIT 0, 20);

      • Fabio Baccaglioni     06/05/2023 - 15:09:57

        MT dijo:

        Y algo así que hace ?

        SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment, b.title , p.mail_gravatar
        FROM comentarios a
        JOIN posts b ON (a.id_noticia = b.id_noticia)
        LEFT JOIN usuario_perfil p ON (a.id_usuario = p.id_usuario)
        WHERE a.id_comentario IN (SELECT id_comentario FROM comentarios WHERE activo = 1 ORDER BY date DESC LIMIT 0, 20);


        el problema con este query es que metiste el limit en el subquery del where y eso MariaDB no lo permite

        This version of MariaDB doesn´t yet support ´LIMIT & IN/ALL/ANY/SOME subquery´

  • Mario     06/05/2023 - 16:08:36

    Podrias poner el resultado de "EXPLAIN SELECT ..:

  • MT     06/05/2023 - 16:27:19

    Fabio Baccaglioni dijo:

    MT dijo:
    Y algo así que hace ?

    SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment, b.title , p.mail_gravatar
    FROM comentarios a
    JOIN posts b ON (a.id_noticia = b.id_noticia)
    LEFT JOIN usuario_perfil p ON (a.id_usuario = p.id_usuario)
    WHERE a.id_comentario IN (SELECT id_comentario FROM comentarios WHERE activo = 1 ORDER BY date DESC LIMIT 0, 20);


    el problema con este query es que metiste el limit en el subquery del where y eso MariaDB no lo permite

    This version of MariaDB doesn´t yet support ´LIMIT & IN/ALL/ANY/SOME subquery´


    Es verdad! Perdón por eso!!

    Y así ?

    SELECT a.id_comentario, a.id_noticia, a.name, a.email, a.id_usuario, a.comment, b.title , p.mail_gravatar
    FROM comentarios a
    JOIN posts b ON (a.id_noticia = b.id_noticia)
    LEFT JOIN usuario_perfil p ON (a.id_usuario = p.id_usuario)
    WHERE a.id_comentario IN (SELECT * FROM (SELECT id_comentario FROM comentarios WHERE activo = 1 ORDER BY date DESC LIMIT 0, 20) AS limit_comentarios);

    • Fabio Baccaglioni     06/05/2023 - 19:53:09

      si, ahí va rápido, pero si mi solución era groncha esta es hyper groncha 😂😁😁😁, igual... si funciona, funciona

  • Diego     07/05/2023 - 00:30:39

    Hola, cuando me pasó algo parecido lo solucioné así: luego de pasar a innodb hacer un dump y luego volver a importar ese dump. Había una explicación del porque, pero ya la olvidé

  • Daniel     12/05/2023 - 21:23:29

    Mirando tu consulta original veo en esta parte algo que no me gusta para nada:

    JOIN posts b ON (a.id_noticia = b.id_noticia)

    si mal no recuerdo de mysql/mariadb, ese JOIN como esta ejecuta un FULL JOIN (eso depende también de la configuración del motor especifico para cada tabla)

    tendrías que probar agregarle el INNER a ver que onda

    INNER JOIN posts b ON (a.id_noticia = b.id_noticia)

Deje su comentario:

Tranquilo, su email nunca será revelado.
La gente de bien tiene URL, no se olvide del http/https

Negrita Cursiva Imagen Enlace


Comentarios ofensivos o que no hagan al enriquecimiento del post serán borrados/editados por el administrador. Los comentarios son filtrados por ReCaptcha V3.