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

[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