大佬看看这条 sql 语句怎么优化

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

For Existing Member Sign In

This topic created in 2584 days ago, the information mentioned may be changed or developed.

SELECT count(traffic_id) FROM traffic WHERE traffic_enterTime >= date_sub(curdate(),interval 1 day) AND traffic_enterTime <= date_sub(curdate(),interval 0 day) UNION SELECT count(DISTINCT traffic_ip) FROM (SELECT * FROM traffic WHERE traffic_enterTime >= date_sub(curdate(),interval 1 day) AND traffic_enterTime <= date_sub(curdate(),interval 0 day)) as subfrom;

5 replies • 2019-03-31 14:22:32 +08:00

ckjianzhong7

Mar 30, 2019

SELECT count(traffic_id) FROM traffic WHERE traffic_enterTime >= date_sub(curdate(),interval 1 day) AND traffic_enterTime <= date_sub(curdate(),interval 0 day) UNION SELECT count(DISTINCT traffic_ip) FROM traffic WHERE traffic_enterTime >= date_sub(curdate(),interval 1 day) AND traffic_enterTime <= date_sub(curdate(),interval 0 day);

des

Mar 30, 2019

数据结构没有，有没有索引，数据量，EXPLAIN 都没有，你想怎么优化？
在 traffic_enterTime 加个索引先？

murmur

Mar 30, 2019

没有 explain 你让我们对着你的帖子 explain 么
程序员也不是神啊

GGGG430

Mar 30, 2019 via iPhone

拆分为多个子 sql，不要在 sql 里用函数

gosleep

Mar 31, 2019 via iPhone

看过优化好像用 explain 返回的结果可以获取行数，搜一下…