SELECT
t1.device_id
FROM
t_device t1
INNER JOIN
t_online t3
ON t1.device_id=t3.device_id
LEFT JOIN
(
SELECT
s.device_id,
s.avg_tdc
FROM
t_site_houravg_tdc s
WHERE
s.create_time > DATE_ADD( NOW( ), INTERVAL - 1 HOUR )
) AS t2
ON t1.device_id = t2.device_id
WHERE
t1.customer_id = '1'
AND t1.`status` = 1
AND t1.type_id = 1539730699168001
AND (
t2.avg_tdc > 9980
OR t2.avg_tdc IS NULL
)
uj5u.com熱心網友回復：
直接建立一個視圖,再查詢視圖既能提升效率uj5u.com熱心網友回復：
不知道你表里的資料量、資料型別、索引情況。大致也就只能改成這樣了。SELECT
t1.device_id
FROM
t_device t1
INNER JOIN
t_online t3
ON t3.device_id=t1.device_id
LEFT JOIN
t_site_houravg_tdc t2
ON t2.device_id = t1.device_id and t2.create_time > DATE_ADD( NOW( ), INTERVAL - 1 HOUR )
WHERE
t1.customer_id = '1'
AND t1.`status` = 1
AND t1.type_id = 1539730699168001
AND (
t2.avg_tdc > 9980
OR t2.avg_tdc IS NULL
)
uj5u.com熱心網友回復：
謝謝，執行計劃沒改動，執行沒優化
uj5u.com熱心網友回復：
t2 是臨時表 么有索引，當然慢了。可以用新版本mysql。 或不用left join，或再優化。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/45116.html
標籤：MySQL