Agilizar SELECT COUNT con INNER JOIN entre dos tablas enormes en MySQL

Hola, vuelvo a entrar a cristalab despues de muchisimo tiempo y me encuentro con la grata sorpresa de que sigue funcionando y esta absolutamente renovado. Felicitaciones.

Me seria de muchisima utilidad que alguien entendido en MySQL pueda darme una mano con el siguiente problema. Lo simplifico con un ejemplo basico para poder dejar en claro que es lo que sucede.

Tengo las siguientes tablas:

tbl_personas:
id INT AUTOINCREMENT (Primary key)
nombre VARCHAR (200)

tbl_personassabores:
id INT NOT AUTOINCREMENT (Primary key)
persona INT
sabor INT

tbl_saboreshelado
id INT NOT AUTOINCREMENT (Primary key)
sabor VARCHAR (50)

Quiero saber, por ejemplo, a cuanta gente le gusta el helado de chocolate y/o de vainilla (id's 1 y 2 en la tabla tbl_saboreshelado).
La consulta que ejecuto es la siguiente:

SELECT COUNT(DISTINCT p.id) FROM tbl_personas as p LEFT JOIN tbl_personassabores as s ON p.id = s.persona WHERE s.sabor = 1 OR s.sabor = 2

Hasta aca todo bien, si tengo 100 personas y 200 relaciones persona/sabor, no hay problema.
El problema es que si tengo 1.000.000 de personas y 2.000.000 relaciones, la consulta jamas termina.

Tengo entendido que la estructura de MySQL bien utilizada tiene que soportar este tipo de busquedas, pero algo estoy haciendo mal, quizas usar JOIN en este caso no es lo adecuado, pero, en el ejemplo, ¿como puedo encontrar la cantidad de personas que se relacionan con un determinado sabor de helado si no es de esta forma?

Bueno, espero haber sido claro.

Saludos y gracias por su tiempo.

¿por que necesitas la tabla personas? y/o ¿por que usas LEFT JOIN cuando debieras usar INNER JOIN?

Código MySQL :

SELECT 
COUNT(s.personas) 
FROM tbl_personassabores 
WHERE s.sabor = 1 OR s.sabor = 2

o

Código MySQL :

SELECT 
COUNT(DISTINCT p.id) 
FROM tbl_personas as p 
INNER JOIN tbl_personassabores as s ON p.id = s.persona 
WHERE s.sabor = 1 
OR s.sabor = 2

lee esto, siempre es utilhttp://www.cristalab.com/tutoriales/consulta-sql-a-mas-de-dos-tablas-con-join-c77632l/

por cierto, no ahorres tantas lineas, mejor indenta bien tu código para que sea legible

Agrega redundancia a la base de datos y agrega un campo a la tabla que tenga el valor del count, así lo evitas. Es lo mejor, créeme.


saludos

Maikel escribió:

¿desnormalizar una DB como primera opción?
¿de cuanto peso son las tablas de la consulta?
¿que versión de mysql es?
¿tienen un respaldo de la DB?

otra solución a tablas muy grandes es el particionado de tablas, la versión de mysql debe ser actual y creo que solo funca con innoDB

Sí, a veces se justifica desnormalizar una base de datos Inyaka.

saludos

Maikel escribió:

a eso me refiero, antes de desnormalizar debiese ver las otras opciones, mysql actualmente ofrece una gran gama de herramientas para mejorar el rendimiento

de echo el colega aun no nos ha dicho nada hacerca de si la consulta mejoro con las primeras 2 opciones que di:
1 ya que es un count, no unir con las otras tablas.
2 usar un INNER JOIN en ves de un LEFT JOIN

Antes que nada gracias por las respuestas.

Mas alla del ejemplo de los sabores de helado, que quizas no es tan representativo, lo que estoy haciendo es un sistema de administracion de personas donde las personas pueden dividirse por grupos, y cada persona puede pertenecer a un grupo, a varios o a ninguno.

Esto me obliga a tener una tabla personas (cada fila en personas tiene muchas columnas con informacion) y otra tabla que vincule personas y grupos.

Inyaka me pregunta porque uso left join en lugar de inner join, y es justamente porque al traer los resultados de la busqueda, quiero que tambien se vean las personas que no pertenecen a ningun grupo (o sea, no tienen vinculacion en la tabla de grupos)

Lo curioso es que las consultas funcionan perfecto para bases de datos no muy grandes, pero instale este programa con un cliente que tiene una base de datos de 100.000 entradas en personas y 300.000 en grupos, y es imposible realizar la consulta por el tiempo que tarda.

Este es el query que uso para contar los resultados de la busqueda

Código MySQL :

SELECT count(DISTINCT p.id)
FROM personas_personas as p
LEFT JOIN personas_grupos as g ON p.id = g.persona 
WHERE g.grupo = X

(el where es opcional segun el tipo de busqueda realizada)

Probe de usar un INNER JOIN en lugar del LEFT JOIN, y el resultado fue casi inmediato, no entiendo porque la diferencia entre uno y otro. El problema con el INNER JOIN es que en los casos en que no hay busqueda por grupo, no obtengo las entradas de las personas que no pertenecen a ningun grupo.

lee el tutorial sobre consultas cruzadas

cuando usaste left join trajiste a todas las personas con y sin grupo en cambio con inner dejaste a afuera a las personas que no cumplian la condicion

has 2 consultas 1 para las personas con grupo y otra para las personas sin grupo

Inyaka:

Solucione el problema hoy justamente haciendo un if con dos consultas, si la busqueda es por grupos hago una consulta con INNER JOIN, si la consulta es sin distincion de grupos, directamente busco en la tabla personas sin hacer el cruce.

De esta manera funciona como yo queria.

De todas formas sigo sosteniendo que el LEFT JOIN era lo ideal ya que lo que yo queria era justamente traer a todas las personas con y sin grupo y luego filtrar con el WHERE (de hecho esto funcionaba a la perfeccion con tablas pequeñas), pero por algun proceso de MySQL que desconozco, el LEFT JOIN tiene un tiempo de respuesta imposible para la consulta en cuestion.

Les agradezco mucho por sus respuestas que me ayudaron a encontrar la solucion.

Saludos

también esta la opción del UNION, procedimientos almacenado, funciones, etc... la verdad agilizar una consulta es muy entretenido con mysql, solo hay que usar la imaginación (y leer mucho la documentación)