mysql order by 索引的小问题

[keatkeat1987]

key(column1,column2)

select * from table1 where column1 = 5 order by column2; //有索引
select * from table1 where column1 in(5,6) order by column2; //没有索引

似乎 where in 就不走索引了 。
有其它的方法吗？

[ACMAIN_CHM]

贴出 explain select ... 和 show index from ... 以供分析。

[两袖轻奢]

引用 1 楼 ACMAIN_CHM 的回复:

贴出 explain select ... 和 show index from ... 以供分析。

没看懂 ，望详细一点指点，谢谢！

[rucypli]

不走索引未必是错误的

[PhpEr_贵]

引用 3 楼 fire53 的回复:

select * from table1 where column1 between 5 and 6 order by column2

between 也是范围查找,同样第二列索引页不行.在排序里in也是范围查找,不知道用or行不行,可以 expain select * from table1 where (column1=5 or column1=6) order by column2看看extra列是什么,而且select * 是无法高效的利用索引的建议不要用 *,只返回需要的列

[keatkeat1987]

select SQL_NO_CACHE a.prod_color_id,
a.prod_id,a.prod_sort,
b.code,b.name,b.brand,
c.color,c.main_img,c.is_show,count(color)as color_count, 
group_concat(d.size) as size,
max(d.price) as max_price,
min(d.price) as min_price,
max(ifnull(e.price,0)) as max_discount_price,
min(ifnull(e.price,0)) as min_discount_price,
max(ifnull(e.rate_percent,0)) as max_percent,
min(ifnull(e.rate_percent,0)) as min_percent  
from prod_server a force index(index2)
straight_join prod b on b.id  = a.prod_id 
straight_join prod_color c on c.id = a.prod_color_id and c.is_delete = 0  
straight_join prod_size d on d.id = a.prod_size_id 
left join prod_discount e 
    on e.prod_size_id = a.prod_size_id 
    and e.prod_size_id = a.prod_size_id 
    and e.identity_id = 0 
    and e.start_date <= '2014-05-03 07:00:00' and e.end_date > '2014-05-03 07:00:00' 
where a.prod_id in (1,2,3)  
and b.brand in ('adidas','nike','puma','bmw') 
and c.color in ('red','black','white','blue','yellow') 
and d.size in ('s','m','l','xl','xxl') 
group by a.prod_id,a.prod_color_color   
order by max_price desc limit 10000,10;

数据到几万条的话， order by 会跑到 500ms
如果order by 是 prod_id 的话会比较快到过 100ms
没用到索引就慢多了。有什么方法优化吗？

[王小葱]

select * from table1 where column1 between 5 and 6 order by column2