why index is not using in order by (foreign key)

Vote count:

0

when i use id (primary key) with order by clause it uses index named PRIMARY but when i use countrycode (foreign key) with order by clause it does't uses index. my output is below.

mysql> SHOW CREATE TABLE City; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | City | CREATE TABLE City ( ID int(11) NOT NULL AUTO_INCREMENT, Name char(35) NOT NULL DEFAULT '', CountryCode char(3) NOT NULL DEFAULT '', District char(20) NOT NULL DEFAULT '', Population int(11) NOT NULL DEFAULT '0', PRIMARY KEY (ID), KEY CountryCode (CountryCode), CONSTRAINT city_ibfk_1 FOREIGN KEY (CountryCode) REFERENCES Country (Code) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

mysql> EXPLAIN SELECT * FROM City ORDER BY ID; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | City | index | NULL | PRIMARY | 4 | NULL | 4321 | | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City ORDER BY COUNTRYCODE; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4321 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)

asked 38 secs ago

user3785166

1

why index is not using in order by (foreign key)