表tabagoods_id goods_in_num
1 1
1 2
1 3
2 1
2 2
2 4
3 1
3 6做了一个索引indexa
字段:goods_id,goods_in_num数据量:500万条左右
select goods_id,max(`goods_in_num`) max_num
from taba
group by goods_id
结果大致如下
goods_id max_num
1 3
2 4
3 6my.ini中设置了
tmp_table_size=200M需要时间60-120秒,感觉太长了要怎么优化或者怎么建表比较好点?
1 1
1 2
1 3
2 1
2 2
2 4
3 1
3 6做了一个索引indexa
字段:goods_id,goods_in_num数据量:500万条左右
select goods_id,max(`goods_in_num`) max_num
from taba
group by goods_id
结果大致如下
goods_id max_num
1 3
2 4
3 6my.ini中设置了
tmp_table_size=200M需要时间60-120秒,感觉太长了要怎么优化或者怎么建表比较好点?
from taba
group by goods_id这样基本上已经没有可优化的了。毕竟连where语句都没有。
1 1,a,b,c,d,e,f,g
1 2,a1,b1,c1,d1,e,f,g
1 3,a2,b2,c2,d2,e1,f,g
2 1,a3,b3,c3,d3,e2,f,g
2 2,a4,b4,c4,d4,e3,f1,g
2 4,a5,b5,c5,d5,e4,f,g2
3 1,a6,b6,c6,d6,e5,f,g3
3 6,a7,b7,c7,d7,e7,f,g4做了一个索引indexa
字段:goods_id,goods_in_num数据量:500万条左右select * from
taba a,
(
select goods_id,max(`goods_in_num`) max_num
from taba
group by goods_id
)b
on a.goods_id=b.goods_id and a.goods_in_num =b.max_num结果大致如下
goods_id goods_in_num,col1,col2,col3,col4,col4……
1 3,a2,b2,c2,d2,e1,f,g
2 4,a5,b5,c5,d5,e4,f,g2
3 6,a7,b7,c7,d7,e7,f,g4my.ini中设置了
tmp_table_size=200M需要时间60-120秒,感觉太长了要怎么优化或者怎么建表比较好点?