我有以下查詢大約需要 9.8 秒，我想知道有沒有辦法使用索引優化查詢，
我已經有一個關于“uc.recieved_date”的索引
除此之外，我無法優化 SQL 選擇，因為它是一個圖表，所以無法使用 LIMIT 將其分解為更小的部分。
SELECT (count(*)-1) AS total, a.Date AS created_dates
FROM
(
SELECT (CURDATE() - INTERVAL c.number DAY) AS date
FROM (SELECT singles tens hundreds number FROM
( SELECT 0 singles
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) singles JOIN
(SELECT 0 tens
UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60
UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) tens JOIN
(SELECT 0 hundreds
UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
) hundreds
ORDER BY number DESC) c
WHERE c.number BETWEEN 0 and 364
) a
LEFT OUTER JOIN `usr_count` uc
ON DATE(uc.received_on) = a.Date
WHERE
a.Date BETWEEN CURDATE() - INTERVAL 28 DAY AND CURDATE() GROUP BY a.Date
ORDER BY a.Date ASC
uj5u.com熱心網友回復：
建立一個永久的“數字”表。（您的“交叉加入”需要一些時間。）
在那張
PRIMARY KEY(number)桌子上。（需要WHERE和ORDER BY）考慮切換到 MariaDB，它有一個偽表
seq_0_to_999可以立即提供“數字”。既然你有一個
LEFT JOIN，你不需要AND ... IS [NOT] NULL嗎？（否則做它INNER JOIN。）ON DATE(uc.received_on) = a.Date可能非常低效。（參見“sargable”。）一個可能的解決方法是ON uc.received_on >= a.Date AND uc.received_on < a.Date INTERVAL 1 DAY
做其中一些，然后回來尋求更多建議。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/419738.html
標籤：