这个SQL如何优化

[xiongbing528]

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还有优化的余地吗。

[xiongbing528]

这条SQL有优化的余地吗

[rucypli]

select did as did ,sum(down_count) as c from mcuhome_downs_salt group by did order by c desc
加快这个是关键