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...
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.
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 ...
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
06/05/2023 - 11:38:56
Yo agregaría más por fe q por conocimiento un índice para el order by (date).
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.
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.
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.
Podrías compartir la salida del EXPLAIN de "la gronchada"?
Si bien la vemos como una gronchada, revisándola con más atención tal vez no sea tan malo hacer esa subquery. Si querés que quede más prolijo podés hacer una "view" (https://dev.mysql.com/doc/refman/5.7/en/view-syntax.html) y joinear contra la misma.
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?
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?
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
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);
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);
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´
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é
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)
jejeje porque inserta los indices.
