使用 for 循环的性能 MySQL 查询

Question

在这种方法中，首先我必须获得两个日期之间的星期日日期，在这种情况下大约是 1 年。然后我在 for 循环中查看日期并将它们设置为查询。我prepared statements用来让它更快。

//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);

但尽管如此，它并没有那么快。查询持续 5 秒，我希望它更快。

我还为表格编制了索引。我想要一些关于如何最好地优化此查询的意见，或者我执行查询的方式是否足够。

这是我之前做过的一个关于为什么我使用GROUP BY和MAX(id)

Answer 1

优化它的最简单方法是创建一个程序，该程序：将开始和结束日期作为参数循环遍历日期以查找星期日并将它们放入临时表中使用临时表加入您的查询并一次返回所有通过这种方式，您可以执行一次实际查询而不是 52 次。应该会快很多。您需要检查的另一件事是内部查询的GROUP BY子句。查询返回刚刚MAX(id)所以GROUP BY不需要。临时表的使用示例：create procedure sp_sample( in_start date, in_end date)begindeclare v_date date;SELECT in_start + INTERVAL 6 - weekday(in_start) day into v_date;drop temporary table if exists sundays_tmp;create temporary table sundays_tmp (d date);while (v_date<in_end) do&nbsp; insert into sundays_tmp values (v_date);&nbsp; select v_date + INTERVAL 7 day into v_date;end while;-- Your query hereend

Answer 2

FLOOR((TO_DAYS(`date`&nbsp;-&nbsp;737080)/7)会给你一个“周数”，你可以使用它GROUP BY。使用它应该可以让您一次性获得所有结果，而不需要在其中包含应用程序循环SELECT。我不明白 的重要性MAX(id)，所以我无法解决其余的问题。