[SOLUCIONADO] Query que cuelga el ordenador (para Búsqueda Avanzada)

[ersito]

Hola a todos. Tengo un problema al intentar programar la Búsqueda Avanzada de mi web.

Tengo las siguientes tablas en mi BBDD (que, según entiendo, es la correcta conforme a las Formas de Normalización):

items, que contiene los objetos a buscar (80.000 registros).

atributo_1, que contiene el listado de los posibles valores del atributo 1 (1.000 registros)
interrelacion_1, relaciona el ID del ítem con los valores que tiene para el atributo 1, que pueden ser 0, 1, 2, etc (3.000 registros)
atributo_2, que contiene el listado de los posibles valores del atributo 2 (10.000 registros)
interrelacion_2, relaciona el ID del ítem con los valores que tiene para el atributo 2, que pueden ser 0, 1, 2, etc (30.000 registros)
...
atributo_7, que contiene el listado de los posibles valores del atributo 7 (5.000 registros)
interrelacion_7, relaciona el ID del ítem con los valores que tiene para el atributo 7, que pueden ser 0, 1, 2, etc (9.000 registros)

La query que uso para realizar la búsqueda es la siguiente.
El problema es que cuando lanzo la query se me cuelga el ordenador.

Código MySQL:

SELECT COUNT(*) AS MAX 
FROM items, atributo_1, interrelacion_1 , atributo_2,  interrelacion _2, ...,  atributo_7,   interrelacion _7
WHERE items.item_id = interrelacion_1.item_id 
AND interrelacion_1.type_id = atributo_1.type_id 
AND items.item_id = interrelacion _2.item_id 
AND interrelacion _2.genre_id = atributo_2.genre_id 
...
AND items.item_id = interrelacion _7.item_id 
AND interrelacion _7.genre_id = atributo_7.siete_id 
AND items.item_name_en LIKE CONCAT('%','aaa','%') 
AND atributo_1.type_id != -1 
AND atributo_2.genre_id != -1 
...
AND atributo_7.siete_id != -1 
AND items.rating >= 0 
AND items.issue_year >= 1900 
AND items.issue_year <= 3000
 

Gracias de antemano por vuestra ayuda.

[gnzsoloyo]

Y... hay muchas cosas.

Por empezar:

Tengo las siguientes tablas en mi BBDD (que, según entiendo, es la correcta conforme a las Formas de Normalización):

En realidad no lo sabemos. Tendríamos que confiar en tus capacidades y tu palabra, pero por las experiencias que hemos tenido en el foro...
Como sea, sin verlo no estaremos seguros si está realmente como dices.

Luego:

Código MySQL:

AND atributo_1.type_id != -1 
AND atributo_2.genre_id != -1 
...
AND atributo_7.siete_id != -1
 

Es un asco de performance. La única forma en que se puede validar algo "diferente a " un valor discreto es comparando todos y cada uno de los registros, lo que implicaría un full table scan sobre el resultado de los JOINs.

Además:

Código MySQL:

AND items.item_name_en LIKE CONCAT('%','aaa','%')
 

Esta es una de esas condiciones decididamente espantosas
Esa condición, al DBMS le dice "lee TODOS los registros y buscá eso en cualquier parte de ese campo en cada registro".
No se recomienda usar los LIKE con comodines a ambos lados. Además, si el valor "aaa" entrase por variable, deberías asegurarte que la longitud de la palabra a buscar no sea menor a cinco letras, o se volverá MUY ineficiente.

Código MySQL:

AND items.issue_year >= 1900 
AND items.issue_year <= 3000
 

Si vas a buscar un rango, usa BETWEEN. En MySQL es ineficiente usar formas como esa.

Finalmente: Sin conocer la selectividad de cada tabla y de cada condición es imposible saber con certeza qué tan bien están armados los JOIN, porque en caso de no ser de buena selectividad estarías haciendo una forma de producto cartesiano.
Habría que realizar algunas pruebas con las tablas reales (me parece que estás dandonos nombres supuestos, lo que es mala práctica según las reglas del foro, que te sugiero leer), y con suposiciones o simulaciones las soluciones nunca son buenas..

Para que te des una idea, si la relación fuese exactamente la que dices, (80 000 * 1 000 * 1 000 * 5 000 * 9 0000), el total de registros en un producto cartesiano rondaría los 108.000.000.000.000.000.000.000...
TE conviene analizar antes que nada la performance de la query con el uso de EXPLAIN. Eso te dará una aproximación acerca de dónde falla tu consulta.

Explicanos qué es lo que intentaste para resolverlo y veamos qué cosas ya probaste. Asi no volvemos sobre los senderos caminados.

[ersito]

Muchas gracias por contestar gnzsoloyo.

A ver, ea query es solo un ejemplo que recoge campos para ver qué hay en base de datos. Ya sé que no está bien, pero era solo demostrar que una consulta así no puede hacer que se vuelgue el ordenador (que tarde sí, que casque el ordenador no).

El ejemplo completo que hace que falle sería este.
Esta consulta saca los generos de unos items (hay unos 1000 géneros y la tabla que relaciona géneros con items tiene unos 70.000 registros)

Código MySQL:

SELECT COUNT(i.item_id)
FROM items i, item_tipos it, item_tipos_asociados ita
WHERE i.item_id = ita.item_id
AND ita.type_id = it.type_id
AND it.type_id = 3
 

Saco los items con el género 3. Hay unos 300 géneros y unos 50.000 relaciones de género-item. Esto devuelve 50 resultados.

Lo mismo para categorías:

Código SQL:

SELECT COUNT(i.item_id)
FROM items i, items_categorias ic, items_categorias_asociados icd
WHERE i.item_id = icd.item_id
AND icd.item_category_id = ic.item_category_id
AND ic.item_category_id = 3
 

Y para las categorias 3. Hay unos 100 géneros y unos 80.000 relaciones de categorias-item. hay 10 reultados

Lo mismo con publishers (60 publishers y 30.000 resultados de relacion publisher-item) y developer (400 developer y 20.000 resultados de relación developer-item):

Código MySQL:

SELECT COUNT(i.item_id)
FROM items i, item_publishers ip, item_publisher_asociados ipa
WHERE i.item_id = ipa.item_id
AND ipa.publisher_id = ip.publisher_id
AND ip.publisher_id = 3
 

Código MySQL:

SELECT COUNT(i.item_id)
FROM items i, item_developers id, item_developer_asoc ida
WHERE i.item_id = ida.item_id
AND ida.developer_id = id.developer_id
AND id.developer_id = 3;
 

Con 40 resultados el primero y 20 el segundo.

Si ahora hago un union de todas las consultas, sólo para ver lo que tarda, ya sé que con solo un union se repiten resultados y no es correcta. Es sólo para ver lo que tarda:

Código MySQL:

SELECT COUNT(i.item_id)
FROM items i, item_generos ig, item_generos_asociados iga
WHERE i.item_id = iga.item_id
AND iga.genre_id = ig.genre_id
AND ig.genre_id = 3
 
UNION
 
SELECT COUNT(i.item_id)
FROM items i, item_tipos it, item_tipos_asociados ita
WHERE i.item_id = ita.item_id
AND ita.type_id = it.type_id
AND it.type_id = 3
 
UNION
 
SELECT COUNT(i.item_id)
FROM items i, items_categorias ic, items_categorias_asociados icd
WHERE i.item_id = icd.item_id
AND icd.item_category_id = ic.item_category_id
AND ic.item_category_id = 3
 
UNION
 
SELECT COUNT(i.item_id)
FROM items i, item_publishers ip, item_publisher_asociados ipa
WHERE i.item_id = ipa.item_id
AND ipa.publisher_id = ip.publisher_id
AND ip.publisher_id = 3
 
UNION
 
SELECT COUNT(i.item_id)
FROM items i, item_developers id, item_developer_asoc ida
WHERE i.item_id = ida.item_id
AND ida.developer_id = id.developer_id
AND id.developer_id = 3;
 

Ahí es cuando se cuelga el ordenador y ya no hace nada.
Ese el es problema, ya que no hay tantos resultados para que se cuelgue la máquina.

[gnzsoloyo]

A ver, ea query es solo un ejemplo que recoge campos para ver qué hay en base de datos. Ya sé que no está bien, pero era solo demostrar que una consulta así no puede hacer que se vuelgue el ordenador (que tarde sí, que casque el ordenador no).

En primer lugar, eso no es cierto. Una consulta mal optimizada puede hacer caer en error el servidor de la base de datos, por abuso de recursos, o se abortada sin mensaje de error, con lo que la aplicación no se entera del fallo y se queda "colgada" hasta salir por timeout.
Si las cosas no se optmizan, y no se evalúan los limites del sistema, los fallos no de administran correctamente, y todo parece haberse vuelto loco.

Ahora bien, vayamos al segundo término de tu consulta:Aunque puedan parecer consultas simples en cada uno de los casos, hay tres cosas que debes tener en cuenta cuando trabajas con bases de datos:
1) No todos los DBMS implementaron las mismas cláusulas de la misma forma, por lo que hay diferencia de comportamientos entre ellos. Eso se puede apreciar en el uso de los JOIN implícitos, ya que Oracle, por ejemplo, los asume como INNER JOIN, y espera a ver la definición en la relación del WHERE antes de generar el matching entre registros. Pero no es el caso de MySQL.
MySQL no tiene buena optimización en el WHERE, por lo que en realidad hace el matchig primero y filtrá después. En ese contexto, hace un producto cartesiano y a ese producto le aplica la condición. El problema es que eso genera una tabla de 800.000 registros en memoria swap, que luego filtra (el proceso de filtrado también se hace swapeando a disco). Como es demasiado overhead, el proceso acumulativo de los UNION puede ser calamitoso.
2) MySQL optimiza correctamente el from, por lo que es mucho mejor trabajar con JOIN explícitos y no implícitos (la coma) .Así le indicas que lea sólo lo que necesitas y luego filtre.
Finalmente, el UNION contiene un DISTINCT implícito, por lo que hay un subproceso no visible que realiza cada UNION y luego los discrimina. Eso puede generar muchos errores, y además baja de performance.

¿Como sería una solución aproximada?
Bueno, por lo pronto, elimina los JOIN implícitos, y veamos si mejora la performance, luego, no cuentes cada segmento por el item, sino por lo devuelto. Cuenta todo al final y veamos si mejora:

Código MySQL:

SELECT SUM(total) item_id
FROM (
    SELECT COUNT(1) total
    FROM items i 
        INNER JOIN item_generos_asociados iga ON i.item_id = iga.item_id
        INNER JOIN item_generos ig ON iga.genre_id = ig.genre_id
    WHERE ig.genre_id = 3
    UNION ALL
    SELECT COUNT(1) total
    FROM items i
        INNER JOIN item_tipos_asociados ita ON i.item_id = ita.item_id
        INNER JOIN item_tipos it ON ita.type_id = it.type_id
    WHERE it.type_id = 3
    UNION ALL
    SELECT COUNT(1)
    FROM items i
        INNER JOIN items_categorias_asociados icd ON i.item_id = icd.item_id
        INNER JOIN items_categorias ic ON icd.item_category_id = ic.item_category_id
    WHERE 
        ic.item_category_id = 3
    UNION ALL
    SELECT COUNT(1) total
    FROM items i
        INNER JOIN item_publisher_asociados ipa ON i.item_id = ipa.item_id
        INNER JOIN item_publishers ip ON ipa.publisher_id = ip.publisher_id
    WHERE 
        ip.publisher_id = 3
    UNION ALL
    SELECT COUNT(1) total
    FROM items i
        INNER JOIN item_developer_asoc ida ON i.item_id = ida.item_id
        INNER JOIN item_developers id ON ida.developer_id = id.developer_id
    WHERE 
        id.developer_id = 3) tablaTemp;
 

Probemos así...

Finalmente, y no menos importante: En SQL el orden de los factores si altera el producto. Esto implica que el orden de las tablas debe seguir la lógica de la relación. No se ponen en cualquier posición, o el DBMS te puede generar resultados ineficientes.

En cuanto al EXPLAIN que te comenté antes, es una cláusula de análisis de performance que puedes ver en el manual de referencia y te permite ver anticipadamente cuán performante es la query que usas, y qué modificaciones podrían hacerse.

https://dev.mysql.com/doc/refman/5.6...g-explain.html

[ersito]

Al quitar los join implícitos se ha resuelto.
Con el ejemplo que me has puesto, la consulta pasa a tardar unos escasos 300 milisegundos.

El ejemplo funciona perfectamente.
¡¡¡¡¡ Muchísimas gracias !!!!!!!!!!

[gnzsoloyo]