我正在使用以下查询,如果按照“supervisor”分组,则需要约52秒,否则只需2或3秒即可完成:
SELECT SQL_CALC_FOUND_ROWS MONTHNAME(o.sale_on) AS DATE,
SUM(od.current_status_id=1) AS sales,
IFNULL(SUM(CASE WHEN od.current_status_id=2
OR od.current_status_id=3 THEN
(SELECT SUM(amount)
FROM order_refund_chargeback
WHERE order_detail_id = od.order_detail_id) END),0) AS total_outflow,
IFNULL(
(SELECT GROUP_CONCAT(CONCAT(user_first_name," ",user_last_name))
FROM user_detail
WHERE user_id IN
(SELECT manager_id
FROM `user_org_map`
WHERE user_id = o.assigned_to)),"") AS supervisor,
IFNULL(mco.country_name,"") AS country
FROM orders o
RIGHT JOIN order_detail od
USING (order_id)
LEFT JOIN user_detail ud
ON ud.user_id = o.assigned_to
LEFT JOIN customer_detail cd
ON cd.customer_id= o.customer_id
LEFT JOIN master_product mp
ON mp.product_id = od.product_id
LEFT JOIN master_campaign mc
ON mc.campaign_id = cd.campaign_id
LEFT JOIN master_country mco
ON mco.country_id = cd.country
WHERE 1=1
AND YEAR(o.sale_on) =2015
GROUP BY supervisor
ORDER BY supervisor ASC
LIMIT 0, 12
有什么建议可以优化这个过程,以便减少时间吗?
supervisor
分组,但选择了sale_on
。这不匹配。我在那里停止阅读你的查询,但你的子查询也似乎非常奇怪。这些是依赖子查询,在结果中的每一行都会执行。难怪你的查询很慢。使用连接重写它,添加一个explain select ...
输出,如果你想优化它,展示你的create table
语句等等。我真的很惊讶你如何在这个问题上得到了5个赞。 - fancyPants