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

When I use the ID (primary key) in order with the sequence, it uses the primary name index, But when I use the country code (foreign key) it does not use the index with the order in order to minimize my output.

  mysql> Create Table City; + ------- + ----------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------- + | Table | Create a table. + ------- + ----------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------------------------------- + | City | Create TABLE `Citi` (| | ID`Int (11) Null atrgentment,` `` `Not all around '', 'Contra quad' four (3) tap default '|, |` District` four 20) Faucet default '', | 'Population' integer (11) No less than '0', | Primary key (`id`), | key 'contraneous contrast (` countrycode)), | | CONSTRAINT `city_ibfk_1` foreign key (` countrycode ') reference' country` (`code`). |) Engine = InDebbi Outangent = 4080 DEFAULT CHARSET = latin1 | + ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- + mysql & gt; Select Xplan * City Order from ID; + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------ + ------ + ------- + | ID | Select_type | Table | Type | Possible_Keys | Key | Key_len | Referee | Rows | Extra | + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------ + ------ + ------- + | 1 | Simple | City | Index | Faucet Primary | 4 | Faucet 4321 | | + ---- + ------------- + ------- + ------- + -------------- - + --------- + --------- + ------ + ------ + ------- + 1 in row (0.00 Sec) mysql & gt; Select COUNTRYCODE from city order; + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ---------------- + | ID | Select_type | Table | Type | Possible_Keys | Key | Key_len | Referee | Rows | Extra | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ---------------- + | 1 | Simple | City | All | Faucet Faucet Faucet Faucet 4321 | Using Fileport | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ---------------- + 1 Set in line (0.00 seconds)  

in innodb primary The keys are called "cluster-indexed".

This means that the rows have been ordered according to the PK values ​​physically respectively.

Because of this, the rows have been sorted naturally, so they are cheap to sort ASC or DESC .

Another story is that when you call order from the second column.

Use mysql to read both the index and the data pages, which dramatically increases the IO, mysql decides to sort it into memory instead (IO according to its hygienic memory sort If you want to sort mysql by using that index to sort you:

1. The number of rows increases in dozens of thousands of stories
2. Select only a small subset such as LIMIT 10

Then use the mysql Maybe index Decides.