商品銷售情況統計查詢優化
為了優化mysql商品銷售情況統計查詢，以下是建議的優化方案：
-
去掉創建索引：
- 刪除 goods 表的 create_time 索引。
- 刪除 orders 表的 goods_id 索引。
-
修改索引：
- 將 orders 表的 goods_id 索引修改為 (create_time, goods_id, amount, status)。
-
優化sql查詢：
- 使用以下優化后的sql查詢：
SELECT g.title, COUNT(*) AS total, COALESCE(SUM(o.amount), 0) AS total_amount, COALESCE(SUM(IF(o.status = 1, o.amount, 0)), 0) AS success_amount, COALESCE(SUM(IF(o.status = 2, o.amount, 0)), 0) AS failed_amount, COALESCE(SUM(o.status = 1), 0) AS success_total, COALESCE(SUM(o.status = 2), 0) AS failed_total FROM orders AS o JOIN goods AS g ON g.id = o.goods_id WHERE o.create_time BETWEEN 'xxx' AND 'yyy' GROUP BY o.id ORDER BY total DESC LIMIT 10
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
