Performance MySQL query with for loop

0 votes
2 answers
851 views
                          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 db->prepare($sql);
    
    //get the sunday dates between two dates and bind the variables
    for ($i = $dateInitial; $i  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 
                        
Asked by Fabian Sierra (101 rep)
Sep 2, 2019, 02:55 PM
Last activity: May 13, 2025, 04:01 AM
Performance MySQL query with for loop

Related Questions
