Performance MySQL query with for loop

Question

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster.

//Get the first day and last day
$dateInitial = strtotime('2018-08-21');
$dateFinal   = strtotime('2019-08-21');
$final       = array();

$sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity
              FROM (
                    SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing,
                           SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production,
                           ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting
                    FROM (
                          SELECT MAX(id) AS id
                          FROM sw_sowing
                          WHERE status != 0
                          AND id_tenant = :id_tenant
                          AND date <= :dates
                          AND multiply != 1
                          AND id_product = 1
                          GROUP BY id_production_unit_detail
                    ) AS sw
                    INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                    INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                    INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
                    WHERE pr_varieties.code != 1
                    GROUP BY sw_sowing.id_product, sw_sowing.type
                    HAVING type NOT IN('ER','PR')
              ) AS s";

$statement    = $this->db->prepare($sql);

//get the sunday dates between two dates and bind the variables
for ($i = $dateInitial; $i <= $dateFinal ; $i = strtotime('+1 day', $i)) {
        if (date('N', $i) == 7){
          $values = [
            ':dates'      => date("Y-m-d", $i),
            ':id_tenant'  => 1
          ];

          $types = [
            ':dates'      => Column::BIND_PARAM_STR,
            ':id_tenant'  => Column::BIND_PARAM_INT
          ];

          $result   = $this->db->executePrepared($statement, $values, $types);
          $final[]  = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        }
      }
      return $final;

But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster.

I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate.

This is a question that I did before about why I use GROUP BY and MAX(id)

https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql

Answer 1

                      SELECT MAX(id) AS id
                      FROM sw_sowing
                      WHERE status != 0
                      AND id_tenant = :id_tenant
                      AND date <= :dates
                      AND multiply != 1
                      AND id_product = 1

needs INDEX(id_tennant, id_product, dates, multiply, id)

GROUP BY id_production_unit_detail does not make sense since you are not doing SELECT id_production_unit_detail, MAX(ID) and then using id_production_unit_detail in some way.

Eh? Doing this for each Sunday?? AND date <= :dates -- That means you are scanning more and more of the table. And, if there are no entries in a week, returning the same data sometimes. There are much better ways to get the data for a given week withouut scanning the rest of the data. And it lends itself better to doing everything at once (instead of looping through Sundays).

I assume these are all the same type?

SELECT ..., sw_sowing.type, ...
    GROUP BY ..., sw_sowing.type
    HAVING type NOT IN('ER','PR')

If so, then it would be more efficient to move the test against type into WHERE instead of HAVING.

(There may be more to improve on. Do these things, then come back for more abuse/advice.)

Answer 2

Only considering the query itself,

Tag query blocks with QB_n as follows

select /*QB_1*/ s.id_product, s.product, s.plant_sowing, s.plant_production,
s.area_planting, CONCAT(s.id_product, '_', s.weeks) as identity
from (select /*QB_3*/ sw_sowing.id_product, pr_products.product, sw_sowing.type,
YEARWEEK('2022-10-01', 3) as weeks, SUM(sw_sowing.quantity) as plant_sowing,
SUM(if(ROUND(DATEDIFF(TIMESTAMPADD(DAY, 6 - WEEKDAY('2022-10-01'), '2022-10-01'),
sw_sowing.date) / 7) >= sw_sowing.weeks_prod ,sw_sowing.quantity, 0)) as plant_production,
SUM(sw_sowing.quantity) / pr_products.plant_m2 as area_planting
     from (select /*QB_2*/ MAX(s.id) as id
            from sw_sowing as s
            where s.status <> 0 and s.id_tenant = 2 and s.date <= '2022-10-01' and s.multiply <> 1
  and s.id_product = 1
            group by s.id_production_unit_detail) as sw inner join sw_sowing on sw_sowing.id = sw.id
   inner join pr_products on pr_products.id = sw_sowing.id_product  inner join pr_varieties on pr_varieties.id = sw_sowing.id_variety

     where pr_varieties.code <> 1 and type not in ('ER', 'PR')
     group by sw_sowing.id_product, sw_sowing.type) as s

Here are some indexes which may help the query performance.

CREATE INDEX PawSQL_IDX1919366792 ON sw_sowing(id_product,id_tenant,id_production_unit_detail);
-- When table sw_sowing(referenced in query block QB_2) serves as a DRIVE table in the join planning, PawSQL_IDX1919366792 can be used to do a index LOOKUP with condition(s.id_tenant = 2 and s.id_product = 1), and SORT AVOIDANCE for (group by s.id_production_unit_detail).
CREATE INDEX PawSQL_IDX0767809739 ON sw_sowing(id,id_product,type);
-- When table sw_sowing(referenced in query block QB_3) serves as a DRIVE table in the join planning, PawSQL_IDX0767809739 can be used to do a index LOOKUP with condition(sw_sowing.id = sw.id), and SORT AVOIDANCE for (group by sw_sowing.id_product, sw_sowing.type).
CREATE INDEX PawSQL_IDX0952698883 ON sw_sowing(id,id_product,id_variety);
-- When table sw_sowing(referenced in query block QB_3) serves as a DRIVEN table in the join planning, PawSQL_IDX0952698883 can be used to do a index LOOKUP with condition(pr_products.id = sw_sowing.id_product and pr_varieties.id = sw_sowing.id_variety and sw_sowing.id = sw.id).
CREATE INDEX PawSQL_IDX1594274845 ON pr_varieties(id,code);
-- When table pr_varieties(referenced in query block QB_3) serves as a DRIVEN table in the join planning, PawSQL_IDX1594274845 can be used to do a COVERING index LOOKUP with condition(pr_varieties.id = sw_sowing.id_variety), and index PUSH DOWN for condition(pr_varieties.code <> 1).
CREATE INDEX PawSQL_IDX1788126893 ON pr_products(id,product,plant_m2);
-- When table pr_products(referenced in query block QB_3) serves as a DRIVEN table in the join planning, PawSQL_IDX1788126893 can be used to do a COVERING index LOOKUP with condition(pr_products.id = sw_sowing.id_product).