mucha lentitud desde hace algunos dias

[mayid]

Tengo alojada la web en piensa solutions. Por alguna razon, desde el viernes anda fatalmente lenta en algunas secciones. El factor comun de esas secciones es que usan la base de datos. Pero sin embargo hay alguna otra seccion que tiene bd y anda normal.

La cuestion es que desde el hosting dicen que ha de ser problema del script. Y yo no lo creía así en un primer momento. Es cierto que estuve modificando algo las consultas, y que por ejemplo hay un calculo sum() para conocer "las fotos mas votadas". Pero al margen de esto ultimo no hice nada que pueda haber causado tal lentitud! Y ademas los cambios anduvieron bien durante casi un dia.

Me resisto a pensar que el problema sea de mysql. Pero creo que lo de "mas votadas" es el problema. Aunque a otras secciones también las vi lentas!

Les dejo las consultas de la pagina principal (la galeria) a ver si las ven a bien:

Código MySQL:

SELECT id, titulo FROM albums WHERE id_usuario = '1' ORDER BY id DESC LIMIT 10
 
SELECT album.id, album.titulo, album.descripcion, album.id_usuario, album.creado, usuario.nombre, usuario.apellido, cat.categoria, foto.nombre_foto 
FROM albums AS album 
INNER JOIN categorias AS cat ON (cat.id = album.id_categoria) 
INNER JOIN usuarios AS usuario ON (usuario.id = album.id_usuario) 
INNER JOIN fotos_principales AS principal ON (album.id = principal.id_album) 
INNER JOIN fotos AS foto ON (principal.id_foto = foto.id)
WHERE id_usuario != '1' 
ORDER BY album.id DESC
 
SELECT album.id, album.titulo, album.descripcion, album.id_usuario, album.creado, usuario.nombre, usuario.apellido, cat.categoria, foto.nombre_foto 
FROM albums AS album 
LEFT JOIN categorias AS cat ON (cat.id = album.id_categoria) 
LEFT JOIN usuarios AS usuario ON (usuario.id = album.id_usuario) 
LEFT JOIN fotos_principales AS principal ON (album.id = principal.id_album) 
LEFT JOIN fotos AS foto ON (principal.id_foto = foto.id) 
WHERE id_usuario = '1' 
ORDER BY album.id DESC
 

La consulta problematica a la que me refiero es la siguiente:

Código MySQL:

SELECT img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas , album.id AS id_album, album.titulo AS titulo_album, album.id_usuario AS id_autor , SUM(rate.puntaje) promedio, count(rate.puntaje) AS votos 
FROM fotos AS img 
LEFT JOIN puntuaciones AS rate ON img.id = rate.id_foto 
INNER JOIN albums AS album ON img.id_album = album.id 
GROUP BY img.id 
ORDER BY promedio DESC, votos DESC LIMIT 3
 

Quizás al tener más y más votos en las obras la consulta con sum() tarda mucho? Debería entonces almacenar un total en tabla aparte? La tabla puntuaciones es tipica: guarda el ip de la persona y el puntaje de uno a cinco que le da a una imagen.

[mayid]

Un par de datos mas:

- las dos primeras consultas son casi iguales, pero una sirve para obtener todos los albums excepto los del administrador, y la segunda para obtener los del administrador. No se por qué no se agrega el LIMIT. Eso es algo que maneja el paginador y a mi parecer no lo hace bien.

- la consulta de mas votadas es la que enlentece la carga de la pagina. Sin embargo eso empezó a ser así hace pocos dias. Antes funcionaba bien. El numero de items que esta procesando es de alrededor de 500.

[mayid]

Bueno, lo que pienso hacer es crear una nueva tabla para guardar los totales:
puntuacion_total(id_foto, total)

Entonces, sumar los totales de la otra tabla y a partir de ahí ir llenando las dos tablas cuando haya un voto.

[jurena]

Mayid,
sólo por decir algo al respecto
yo haría un cruce inner join entre fotos y puntuaciones, y haría de esa tabla un alias y saco el límite con las tres; posteriormente uno
SELECT img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas , album.id AS id_album, album.titulo AS titulo_album, album.id_usuario AS id_autor , SUM(rate.puntaje) promedio, count(rate.puntaje) AS votos FROM fotos AS img INNER JOIN (SELECT fot.id id, SUM(rate.puntaje) promedio, count(rate.puntaje) votos FROM fotos AS fot INNER JOIN puntuaciones AS rate ON fot.id = rate.id_foto GROUP BY fot.id ORDER BY promedio DESC, votos DESC LIMIT 3) AS tabla1 ON tabla1.id =img.id INNER JOIN album ON img.id_album = album.id

No he probado nada. La idea es sacar lo más rápidamente posible las tres con más valores, evitando el left join que une todos los valores de foto, con todos los datos que arrastra y los nulos, ralentizando todo el proceso. Haz alguna prueba con esto, y revisa los índices. Esto no debería darte problema.

[mayid]

Gracias Jurena. Voy a estudiarlo porque nunca vi algo así (un select dentro de un join). Pero entiendo lo de INNER en vez de LEFT. Que bueno lo señalaras porque yo con tantas cosas pierdo la perspectiva.

[jurena]

Mayid, sería algo así como una subconsulta a la antigua usanza, pero con un buen uso de los índices. Es clave que los campos por los que haces la union (ON ...) sean primary key y índice. Ya nos dirás si te sirve. Respecto a las otras dos consultas, deberías decirnos algo sobre las relaciones usuarios, fotos, albums, categorías. Entiendo por lo que tienes que a un usario pertenece uno o varios álbumes de fotos, álbumes que pueden clasigifcarse según una o a varias categorías; dentro de ese album se incluyen fotos de un total de fotos que pertenece al usuario a través del album. El usuario marcará en el album las fotos principales (que pareces guardar en otra tabla, llamada fotos_principales, y que, a mi juicio, sólo tiene sentido si las fotos son principales o no con otro criterio, fecha, por ej.; porque si fueran principales todo el tiempo, te bastaría con marcarlo en la tabla albums). Lo que parece claro es que las fotos sólo se vinculan al usuario por el álbum. Si eso es así y todas las fotos pertenecen a un álbum, todos los albumes pertenecen al menos a un usuario, se adscriben a una categoría, y en todos los álbumes hay una foto_principal, te sobran los left join y deberías usar siempre INNER JOIN. Cosa distinta es si puedes crear un album sin asignarlo a ninguna categoría, o no tener asignada ninguna foto principal. Tendrías que decirnos cuáles son las situaciones posibles.

[mayid]

Jurena, mil gracias por tu interes. Tu lectura es tal cual. Se trata de que cada usuario mantenga albums de fotos. Y para cada albums existe solo una foto principal. Esta es la web, por cierto.

El tema con la imagen principal es que en el index de albums solo muestro una imagen representativa. Por eso va en tabla aparte. Pero esto no me funciona muy bien porque uso tablas MyISAM y puede darse la situacion de que al borrar algunas fotos se borre la principal. Situacion para la cual hice algunas funciones en PHP que no funcionan del todo ok y tendré que revisar. Quizas con InnoDB todo sería diferente pero como nunca lo usé... no se. Esto que decías de ahorrar LEFT JOINS quizas tenga que ver tambien?

En la consultas que publique primeramente falta el LIMIT. Ese es agregado por mi paginador.

Como sea, lo del sistema de votacion quedó mas ligero una vez que sumé una nueva tabla a base de datos. En un principio me habían dicho que con SUM() todo iría bien, pero con solo unos 500 votos y la consulta que les mostraba más arriba la pagina tardaba unos 4 segundos en cargar :o\ Eso empezó a pasar de un momento al otro, porque al principio andaba bien. Aclaro que mi host tiene mysql 4.

[jurena]

Uff, con esa versión de MySQL, olvídate de subconsultas: tendremos que buscar alguna otra solución.
¿Y si sólo haces esto, cuánto te tarda?
SELECT fot.id id, SUM(rate.puntaje) promedio, count(rate.puntaje) votos FROM fotos AS fot INNER JOIN puntuaciones AS rate ON fot.id = rate.id_foto GROUP BY fot.id ORDER BY promedio DESC, votos DESC LIMIT 3

[mayid]

Esa consulta va rapida.

[jurena]

Entonces el problema no es el sum. ¿Y esto, te funciona? ¿Y si lo hace, cuánto tarda?
SELECT img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas FROM fotos img INNER JOIN (SELECT fot.id id, SUM(rate.puntaje) promedio, count(rate.puntaje) votos FROM fotos AS fot INNER JOIN puntuaciones AS rate ON fot.id = rate.id_foto GROUP BY fot.id ORDER BY promedio DESC, votos DESC LIMIT 3)t1 ON img.id= t1.id

[mayid]

Eso se ejecuta en 0.312ms

Hay mucha diferencia con mi consulta original, que tardaba 3.922ms!

Ahora, como puedo hacer el JOIN con albums? Y donde puedo leer más sobre subconsultas? Voy a buscar.

[jurena]

Ahora lanza esta y dinos el tiempo. Tiene, según creo, todo lo que ponías en la tuya.
SELECT t1.promedio, t1.votos, img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas, album.id AS id_album, album.titulo AS titulo_album, album.id_usuario AS id_autor FROM fotos img INNER JOIN (SELECT fot.id id, SUM(rate.puntaje) promedio, count(rate.puntaje) votos FROM fotos AS fot INNER JOIN puntuaciones AS rate ON fot.id = rate.id_foto GROUP BY fot.id ORDER BY promedio DESC, votos DESC LIMIT 3)t1 ON img.id= t1.id INNER JOIN album ON img.id_album = album.id

Edito: debes aprender sobre subconsultas, claro que sí, pero también debes saber que esa versión de tu hosting de MySQL te obligará a usar sintaxis antiguas, sin subconsultas. Porque lo que yo he hecho no es una subconsulta, sino la conversión del resultado de una consulta en una tabla y su unión mediante inner join con otras tablas. Por eso funciona con esa versión.

[mayid]

Tarda 0.312ms y parece tener todo lo que necesito :o)

Mil gracias. Estoy recuperando la confianza en las funciones de mysql. Entonces es bueno usar SUM(), aunque haya miles de registros y haya que calcular el total una y otra vez? No es necesario llevar aparte una tabla de totales?

[jurena]

Eso pienso yo, pero también hay que saber aproyechar toda la fuerza del motor. Fíjate en que el truco es evitarle al motor traerse datos que no necesita. Si le dices que sume y a la vez se traiga todos esos campos de todas las tablas unidas por left join tardará mucho. Como son sólo tres, yo pensé en traerme sólo los valores del agrupado y el id (como clave está indexada, tanto para el dato como para agrupar) que me permita el inner join (o el left join) con las otras tablas. Yo no trabajo con grandes tablas, pero quienes lo hacen son capaces de trabajar con esas funciones de agregado sin problema. Sobre subconsultas encontrarás muchas páginas. A mí me gusta esta, aunque hay muchas otras:
http://www.aulaclic.es/sql/t_5_2.htm
Un consejo que yo leí a nuestro gnzsoloyo, trabaja en las consultas como si fuera una cebolla, por capas, de dentro hacia afuera. En el caso nuestro, el corazón de la cebolla era sacar las sumas...

saludos, Mayid