求助 SQL 优化问题

Question

select a.CO CO,C3,C12,C13,a.C1 from (select CO,max(C1) C1 from T1   group by CO) a join  T1 b on a.CO=b.CO and a.C1=b.C1  
 为什么这样语句在MSSQL 很快不到一秒在MYSQL要10几秒呢 数据不多菜几万条 求解决

Answer 1

select * from T1 a where not exists(select 1 from t1 where a.co=co and a.c1<c1)
在C0、C1上建立复合索引试试

Answer 2

select CO,C3,C12,C13,(select max(C1) from T1 where CO=b.CO) as C1 
from T1 b

Answer 3

1 PRIMARY <derived2> ALL \N \N \N \N 2278
1 PRIMARY b ref PRIMARY,C1 C1 8 a.C1 33 Using where
2 DERIVED ST22015_main ALL \N \N \N \N 39097 Using where; Using temporary; Using filesort执行计划是这样的

Answer 4

show index from T1 贴出来看一下。
参考下贴中的多种方法http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

Answer 5

EXPLAIN select * from T1 a where not exists(select 1 from t1 where a.co=co and a.c1<c1)
EXPLAIN select a.CO CO,C3,C12,C13,a.C1 from (select CO,max(C1) C1 from T1 group by CO) a join T1 b on a.CO=b.CO and a.C1=b.C1  贴结果

Answer 6

1 PRIMARY b ALL \N \N \N \N 4342708 Using where
2 DEPENDENT SUBQUERY a ref PRIMARY,C1 PRIMARY 98 dzhst2.b.CO 43427 Using where
第二中方法1 PRIMARY <derived2> ALL \N \N \N \N 2285
1 PRIMARY b ref PRIMARY,C1 C1 9 a.C1 3635 Using where
2 DERIVED t1        ALL \N \N \N \N 4341108 Using temporary
块郁闷死了

Answer 7

SHOW INDEX FROM tbl_name 

Answer 8

放心好索引都有的
t1 0 PRIMARY 1 CO A \N \N \N BTREE
t1      0 PRIMARY 2 C21 A 4379108 \N \N BTREE
t1 1 CV 1 CV A \N \N \N YES BTREE
t1 1 C1 1 C1 A \N \N \N YES BTREE
t1 1 C2 1 C2 A \N \N \N YES BTREE
t1 1 C3 1 C3 A \N \N \N YES BTREE
t1 1 C21 1 C21 A \N \N \N BTREE

Answer 9

create index xxx on t1 (CO,.C1);