MySQL
这个SQL如何优化
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区explain select d.*,ds.* from (select did as did ,sum(down_count) as c from mcuhome_downs_salt group by did order by c desc) ds , mcuhome_downs d where ds.did = d.did order by ds.c desc limit 10
1 PRIMARY <derived2> ALL 2
1 PRIMARY d eq_ref PRIMARY PRIMARY 3 ds.did 1
2 DERIVED mcuhome_downs_salt ALL 2 Using temporary; Using filesort
如果我增加以下索引
create index count_did on mcuhome_downs_salt(did ,down_count)
explain select d.*,ds.* from (select did as did ,sum(down_count) as c from mcuhome_downs_salt group by did order by c desc) ds , mcuhome_downs d where ds.did = d.did order by ds.c desc limit 10
结果是这个
1 PRIMARY <derived2> ALL 2
1 PRIMARY d eq_ref PRIMARY PRIMARY 3 ds.did 1
2 DERIVED mcuhome_downs_salt index count_did 6 2 Using index; Using temporary; Using filesort
变成了走 index索引了
show index from mcuhome_downs_salt
mcuhome_downs_salt 0 PRIMARY 1 dsid A 2 BTREE
mcuhome_downs_salt 1 count_did 1 did A BTREE
mcuhome_downs_salt 1 count_did 2 down_count A BTREE
但是还是有部分检索是走全表扫描，请问这个SQL还有优化的余地吗。
1 PRIMARY <derived2> ALL 2
1 PRIMARY d eq_ref PRIMARY PRIMARY 3 ds.did 1
2 DERIVED mcuhome_downs_salt ALL 2 Using temporary; Using filesort
如果我增加以下索引
create index count_did on mcuhome_downs_salt(did ,down_count)
explain select d.*,ds.* from (select did as did ,sum(down_count) as c from mcuhome_downs_salt group by did order by c desc) ds , mcuhome_downs d where ds.did = d.did order by ds.c desc limit 10
结果是这个
1 PRIMARY <derived2> ALL 2
1 PRIMARY d eq_ref PRIMARY PRIMARY 3 ds.did 1
2 DERIVED mcuhome_downs_salt index count_did 6 2 Using index; Using temporary; Using filesort
变成了走 index索引了
show index from mcuhome_downs_salt
mcuhome_downs_salt 0 PRIMARY 1 dsid A 2 BTREE
mcuhome_downs_salt 1 count_did 1 did A BTREE
mcuhome_downs_salt 1 count_did 2 down_count A BTREE
但是还是有部分检索是走全表扫描，请问这个SQL还有优化的余地吗。
...全文
