EN VI

MySQL is not using the primary key that is implicitly appended to this secondary index?

MySQL is not using the primary key that is implicitly appended to this secondary index

Starting point: as I understand, MySQL will implicitly add the primary key at the end of every secondary index.

I have a table with:

a primary index on id
a secondary index on project_id

So, I thought MySQL would would use the secondary index project_id for the following query, without needing a filesort:

SELECT * FROM db.table_name WHERE project_id = 5 ORDER BY `id` LIMIT 1;

However, explain shows me that PRIMARY is being used as the key:

If I use force_index on the project_id column, the index is used, but the (estimated) number of estimated rows and 'using filesort' shows me that the primary key is not being used for the order by.

SELECT * FROM db.table_name FORCE INDEX (`project_id`) WHERE project_id = 5 ORDER BY `id` LIMIT 1;

It's a MyISAM table, I don't know if that matters here...

So: why isn't the filesort avoided? What am I missing?

Note: Database and table names are edited.

Solution:

It's a MyISAM table, I don't know if that matters here...

It definitely does.

The feature of the primary key appended to the secondary key is specific to InnoDB's implementation.

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html says:

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.

(emphasis mine)

MyISAM doesn't store indexes that way. In MyISAM, primary key indexes and secondary indexes are similar; both store the value to be indexed with offsets to the records where these values are found in the table data. Primary key values are not combined with secondary keys.

You should be aware that MyISAM is gradually being phased out of MySQL. MyISAM does not support ACID properties, nor does it support transactions, foreign keys, row-level locks, concurrent updates, etc. As a rule, I haven't used MyISAM except in rare cases since 2010, when InnoDB became the default storage engine.