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 * 是无法高效的利用索引的建议不要用 *,只返回需要的列
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 没用到索引就慢多了。有什么方法优化吗?
expain select * from table1 where (column1=5 or column1=6) order by column2看看extra列是什么,而且select * 是无法高效的利用索引的建议不要用 *,只返回需要的列
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
没用到索引就慢多了。有什么方法优化吗?