Tengo 2 tablas que comparten una relación de 1 a muchos. Suponga la siguiente estructura:
users users_metadata ------------- ------------- id | email id | user_id | type | scoreUn usuario puede tener muchos metadatos. La tabla de usuarios tiene 100k filas, la tabla users_metadata tiene 300k filas. Es probable que crezca 10 veces, por lo que todo lo que escriba debe ser óptimo para grandes cantidades de datos.
Necesito escribir una instrucción sql que devuelva solo los correos electrónicos de los usuarios que pasan un par de condiciones de puntaje diferentes que se encuentran en la tabla de metadatos.
// if type = 1 and if score > 75 then <1 point> else <0 points> // if type = 2 and if score > 100 then <1 point> else <0 points> // if type = 3 and if score > 0 then [-10 points] else <0 points> // there are other types that we want to ignore in the score calculationsSi el usuario supera un umbral (por ejemplo, >= 1 punto), quiero que ese usuario esté en el conjunto de resultados; de lo contrario, quiero que se ignore al usuario.
Probé con el usuario una función/cursor almacenado que toma un ID de usuario y recorre los metadatos para descubrir los puntos, pero la ejecución resultante fue muy lenta (aunque funcionó).
Tal como está, tengo esto, y se tarda entre 1 y 3 segundos en ejecutarse.
SELECT u.id, u.email, ( SELECT SUM( IF(k.type = 1, IF(k.score > 75, 1, 0), 0) + IF(k.type = 2, IF(k.score > 100, 1, 0), 0) + IF(k.type = 3, IF(k.score > 0, 1, -10), 0) ) FROM user_metadata k WHERE k.user_id = u.id ) AS total FROM users u GROUP BY u.id HAVING total IS NOT NULL;Siento que a 10x esto va a ser aún más lento. un tiempo de ejecución de consulta de 1 a 3 segundos es demasiado lento para lo que ya necesito.
¿Cuál sería un enfoque más óptimo?
Si uso un lenguaje como PHP para esto también, ¿sería mejor ejecutar 2 consultas, una para obtener user_ids de usuario de user_metadata de usuario de solo usuarios passing , y luego una segunda para SELECCIONAR DÓNDE EN esa lista de ID?
Intente usar un JOIN en lugar de una subconsulta correlacionada.
SELECT u.id, u.email, t.total FROM users AS u JOIN ( SELECT user_id, SUM(CASE type WHEN 1 THEN score > 75 WHEN 2 THEN score > 100 WHEN 3 THEN IF(k.score > 0, 1, -10) END) AS total FROM user_metadata GROUP BY user_id HAVING total >= 1 ) AS t ON u.id = t.user_idHacer la agrupación y el filtrado en la subconsulta hace que la unión sea más pequeña, lo que puede suponer un aumento significativo del rendimiento.
Tampoco es necesario que use GROUP BY u.id en su consulta, ya que esa es la clave principal de la tabla que está consultando; con suerte, MySQL optimizará eso.