I recently received this question from someone on Twitter:

Hi Richard I couldn't find answer to my doubt..I searched all over the internet..does MySQL always use clustered index(primary key) for select * from table?

I had a composite primary key of 3 cols col1 col2 col3 and there was a foreign key on col4 ,MySQL chose to order by foreign key when I did select *

I wasn't provided with any more context, so I'm having to make the following assumptions:

• They're using InnoDB
• They're doing a table join (using the foreign key), or at least have a WHERE condition on the foreign key value
• They're using at least MySQL 5.6
• They had no explicit ORDER BY in their SQL query

The answer can be found in the MySQL manual on InnoDB index types:

In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

What is most likely happening in this case is that the MySQL query optimiser is recognising that the secondary index contains the primary key as well as the foreign key field. This makes the secondary index more suitable for use (given the join or WHERE condition) as the table data would not need to be read for each row to get the foreign key field - we have an index scan rather than a table scan.

If there is no explicit ORDER BY clause in the SQL, it will sort results by the order of the index used - in this case, that's (col4,col1,col2,col3). This is why the results appear to be sorted by foreign key, rather than primary key, and serves as a good reminder that if you need a result set sorted in a particular order, you should always explicitly declare it.