Why does mysql order by not use index sorting?

Question

order by city_id (primary key index)

order by country_id (no indexing!!!)

Answer 1

Your city_id is the primary key, and country_id is the composite primary key.

Answer 2

changed to

SELECT * FROM city FORCE INDEX(idx_fk_country_id) ORDER BY country_id;

That’s it, you’re forcing the use of an index in SELECT中查询了索引建以外的列，那么ORDER BY就不会使用索引了。你可以用FORCE INDEX.

Another point is the so-called covering index. The definition of a covering index is: MySQL can return the select field based on the index without querying the file again based on the index to get the result.

What happens when you use select *时，你没有强制指定索引，那么mysql为了得到你的查询的字段而查询文件，然后再进行排序操作，这就没有用到覆盖索引。而你使用了force index就会强制使用覆盖索引，这样就不会出现filesort.

Answer 3