select a.breed_name, a.id,is_vip, a.type_id from ml_seedling a join ml_user b on a.supplier_id = b.id join ml_nursery c on a.n_id=c.id and a.s_status = 1 AND a.is_old=0 AND b.u_status = 1 AND a.price >0 and a.breed_name like '%银杏%' and a.breed_id = 362 order by is_vip DESC, a.create_time DESC limit 0,20
1.如果 不加order by 速度很快
2.order by 后用字段s_status 速度也很快(n_id 和s_status 做了联合索引)
总结:1
order by 后的字段,如果要走索引,须与where 条件里的某字段建立复合索引!!或者说orcer by后的字段如果要走索引排序,它要么与where 条件里的字段建立复合索引【这里建立复合索引的时候,需要注意复合索引的列顺序为(where字段,order by 字段),这样才能满足最左列原则,原因可能是order by字段并能算在where 查询条件中!】,要么它自身要在where 条件里被引用到!
总结:2
表a id为普通字段,上面建有索引
select * from a order by id (用不上索引)
select id from a order by id (能用上索引)
select * from a where id=XX order by id (能用上索引)
意思是说order by 要避免使用文件系统排序,要么把order by的字段出现在select 后,要么使用order by字段出现在where 条件里,要么把order by字段与where 条件字段建立复合索引!