mysql - 关于left join 和limit 索引的问题?
情况是这样，表record 大概300多W，表record_exten大概58W，图片是两条执行计划,可以看到order by的表不同，导致的查询计划的rows相差很大~ 请问是什么原因~~~
情况是这样，表record 大概300多W，表record_exten大概58W，图片是两条执行计划,可以看到order by的表不同，导致的查询计划的rows相差很大~ 请问是什么原因~~~
伊谢尔伦2017-04-17 13:03:32
Due to different order by fields, the indexes used in the table record_exten in the two queries are different. Obviously the index PRIMARY is more optimized than DeptID.
order by using index
1) The default is to take rownum first and then order by
2) If order by satisfies the situation of using index, order by first, and then take rownum
order by using index is conditional:
1) All columns in ORDER BY must be included in the same index and maintain the order in the index.
2) All columns in ORDER BY must be defined as non-null.
3) In addition, if the column in ORDER BY appears in the where condition, the index
Detailed view: http://happymen001.iteye.com/blog/674764
