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
Best Answer
needs
INDEX(id_tennant, id_product, dates, multiply, id)
GROUP BY id_production_unit_detail
does not make sense since you are not doingSELECT id_production_unit_detail, MAX(ID)
and then usingid_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
?If so, then it would be more efficient to move the test against
type
intoWHERE
instead ofHAVING
.(There may be more to improve on. Do these things, then come back for more abuse/advice.)