mysql un recuento para cada unión: optimización




join optimization (2)

RESULTADOS: He usado tres métodos:

  1. Tres subconsultas, 1 unión en cada una (la mía)
  2. Tres subconsultas, sin unión, filtrado con where (SlimsGhost)
  3. Triple unión (llama solar)

He hecho algunas estadísticas con "explicar" y "perfiles" que explican el trabajo que debe realizar cada consulta y los siguientes resultados no fueron sorprendentes: stats

Resultados relativos:

  1. 100%
  2. 79%
  3. 1715%

POSTE ORIGINAL

La idea es unir 4 tablas, usando el mismo PK cada vez y luego contar cuántas filas daría cada combinación por separado.

La respuesta obvia es hacer cada unión ... por separado con subconsultas.

Pero, ¿es posible hacerlo con una consulta? ¿Sería más eficiente?

select "LES CIGARES DU PHARAON" as "Titre",
          (select count( payalb.idPays)
          from album alb
                     left join pays_album payalb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Pays",
          (select count( peralb.idPers)
          from album alb
                     left join pers_album peralb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Personnages",
          (select count( juralb.idJur)
          from album alb
                     left join juron_album juralb using ( idAlb )
          where alb.titreAlb = "LES CIGARES DU PHARAON") as "Jurons"
; 
+------------------------+------+-------------+--------+
| Titre                  | Pays | Personnages | Jurons |
+------------------------+------+-------------+--------+
| LES CIGARES DU PHARAON |    3 |          13 |     50 |
+------------------------+------+-------------+--------+

filas del álbum de la mesa: 22

tabla pays_album filas: 45

tabla personnage_album filas: 100

tabla juron_album filas: 1704

Esto es lo que probé:

select alb.titreAlb as "Titre",
         sum(case when alb.idAlb=payalb.idAlb then 1 else 0 end) "Pays",
         sum(case when alb.idAlb=peralb.idAlb then 1 else 0 end) "Personnages",
         sum(case when alb.idAlb=juralb.idAlb then 1 else 0 end) "Jurons"
from album alb
          left join pays_album payalb using ( idAlb )
          left join pers_album peralb using ( idAlb )
          left join juron_album juralb using ( idAlb )
where alb.titreAlb = "LES CIGARES DU PHARAON"
group by alb.titreAlb
;
+------------------------+------+-------------+--------+
| Titre                  | Pays | Personnages | Jurons |
+------------------------+------+-------------+--------+
| LES CIGARES DU PHARAON | 1950 |        1950 |   1950 |
+------------------------+------+-------------+--------+

pero cuenta el número total de filas de la tabla unida completa, ... (1950 = 3 * 13 * 50)

esquema: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_schema.png

contenido de las tablas: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_description

Si quieres jugar para jugar con él:

db_init: https://github.com/LittleNooby/gbd2015-2016/blob/master/tintin_ok.mysql

https://code.i-harness.com


En cuanto a "menos trabajo para la base de datos", creo que lo siguiente sería una E / S correcta y menos lógica para su esquema. Sin embargo, comprenda que NO PUEDE saber con seguridad a menos que mire los planes de explicación (esperados y reales).

Aún así, recomiendo probar esto: accede a la tabla "alb" solo una vez, mientras que su consulta original necesitaría acceder a ella cuatro veces (una vez para obtener el registro del álbum "base", y luego tres más para las tres subconsultas).

select alb.titreAlb as "Titre",
     (select count(*) from pays_album t2 where t2.idAlb = alb.idAlb) "Pays",
     (select count(*) from pers_album t2 where t2.idAlb = alb.idAlb) "Personnages",
     (select count(*) from juron_album t2 where t2.idAlb = alb.idAlb) "Jurons"
from album alb
where alb.titreAlb = "LES CIGARES DU PHARAON"

Para fines de optimización, una buena regla general es unir menos, no más. De hecho, debe intentar unir la menor cantidad de filas posible con la menor cantidad posible de filas. Con cualquier combinación adicional, multiplicará los costos en lugar de agregar costos. Porque mysql básicamente generará una gran matriz multiplicada. Sin embargo, gran parte de eso se optimiza mediante índices y otras cosas.

Pero para responder a su pregunta: en realidad es posible contar con una sola gran combinación, suponiendo que las tablas tengan claves únicas e idalb es una clave única para el álbum. Entonces, y solo entonces, puedes hacerlo de manera similar a tu código:

select alb.titreAlb as "Titre",
       count(distinct payalb.idAlb, payalb.PrimaryKeyFields) "Pays",
       count(distinct peralb.idAlb, peralb.PrimaryKeyFields) "Personnages",
       count(distinct juralb.idAlb, juralb.PrimaryKeyFields) "Jurons"
from album alb
left join pays_album payalb using ( idAlb )
left join pers_album peralb using ( idAlb )
left join juron_album juralb using ( idAlb )
where alb.titreAlb = "LES CIGARES DU PHARAON"
group by alb.titreAlb

donde PrimaryKeyFields representa los campos de clave principal de las tablas unidas (debe buscarlos).

Distinct eliminará el efecto que tienen las otras combinaciones en el recuento. Pero desafortunadamente, en general, distinct no eliminará el efecto que las uniones tienen en el costo.

Aunque, si tiene índices que cubren todos los campos (idAlb + PrimaryKeyFields) de sus tablas, eso podría ser incluso tan rápido como la solución original (porque puede optimizar lo distinct para no hacer una clasificación) y se acercará a lo que usted estaban pensando (solo caminando por cada tabla / índice una vez). Pero en un escenario normal o en el peor de los casos, debería funcionar peor que una solución razonable (como la de SlimGhost), porque es dudoso que encuentre la estrategia óptima. Pero juegue con él y verifique las explicaciones (y publique los hallazgos), tal vez mysql hará algo loco.





optimization