0

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

  • Looping is slow. Can you do the task without looping? That is a single (but more complex) SELECT? – Rick James Sep 2 '19 at 18:45
0
                      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.)

| improve this answer | |
  • Thanks for you answer, I did your observations and the query lasts 7 seconds, I need to GROUP BY by id_production_unit_detail, because I need the MAX(id) by id_production_unit_detail and I need to check for every Sunday, because the history of each id_production_unit_detail is saved in the table, for this reason I need to do it that way. – Fabian Sierra Sep 2 '19 at 20:01
  • Hmmm... I don't understand the intent of the query well enough to figure out where to take the discussion from here. – Rick James Sep 2 '19 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.