[Solved] How can I optimize this MYSQL query? (huge delay to obtain the result)

by


Seems like a candidate for conditional aggregation:

SELECT `Dia_Semana`, `Fetcha_Deposte`
   , IFNULL(ROUND(`Suma_Bondiolas s/hueso`, 2), 0) AS `Suma_Bondiolas s/hueso`
   , IFNULL(ROUND(`Suma_huesos_Bondiola`, 2), 0) AS `Suma_huesos_Bondiola`
   , `Suma_Bondiolas s/hueso` / `Suma_huesos_Bondiola` AS `Huesos Bondiola/Bondiolas sin huesos`
FROM (
    SELECT ELT(WEEKDAY(fecha) + 1, 'Lunes', 'Martes', 'Miercoles', 'Jueves', 'Viernes', 'Sabado', 'Domingo') AS `Día_Semana`
     , fecha AS `Fecha_Desposte`
     , SUM(CASE WHEN producto IN (1625,1725,1695,1732,2001,2201) THEN kilos ELSE 0 END) AS `Suma_Bondiolas s/hueso`
     , SUM(CASE WHEN producto IN (1427,1527,1695,1698) THEN kilos ELSE 0 END) AS `Suma_huesos_Bondiola`
    FROM rindes 
    WHERE fecha >='2018-10-29' AND fecha <= '2019-01-30' 
    GROUP BY Fecha_Desposte, `Día_Semana`
) AS subQ
ORDER BY Fecha_Desposte DESC
;

Note the subquery format of this was not strictly necessary, but it prevents having to duplicate the first two sum expressions for the final column.

Also, if the product id values used are a small subset of the data, adding AND producto IN ([all product ids in both sum expressions]) to subQ’s WHERE could speed things up too. I’ve heard more recent version of MySQL have optimized the IN operator a bit, but I’m not sure if it can take advantage of indexes now; but it would be worth trying to index (fecha, producto) if you add the condition.

0

solved How can I optimize this MYSQL query? (huge delay to obtain the result)