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