MySQL Index – Using an Index Not Listed in Possible Keys

indexMySQL

In a very basic setup, I have a table with some fields:

firstname (VARCHAR)
lastname (VARCHAR)
country (INT)
email (VARCHAR)

I created in Index over 2 fields:

CREATE INDEX countr_email_idx ON person (country, email)

This is a simple example from school, so no assumptions on whether the index is of any use should be made.

Now when I query a simple Select over the email field, the MySQL documentation states that because it is not the left-most attribute of my index, it can't be used. This is also reflected in the empty possible_keys value when i run the query with EXPLAIN:

EXPLAIN SELECT passenger_id, email
FROM passengerdetails
WHERE email LIKE 'bob%'

which yields

id | select_type | table            | type  | possible_keys | key              | key_len | ref  | rows  | Extra
1  | SIMPLE      | passengerdetails | index | NULL          | countr_email_idx | 225     | NULL | 36370 | Using where; Using index

However, countr_email_idx is listed as the index that is used after all. Why does this happen?

Best Answer

The documentation for MySQL has the following bit of information regarding the EXPLAIN Output Format:

It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

So while an index seek is not expected, MySQL will still retrieve the data via the index rather than via a normal data row scan.

Addendum regarding primary key indexes

On the assumption that you are using the InnoDB engine and that the PK on the passengerdetails tables is based on the passenger_id, then all other secondary indexes will be based on this primary key as stated in the article 14.11.9 Clustered and Secondary Indexes in the official documentation.

Even if you didn't create a primary clustered index yourself, MySQL will do it for you to ensure speedy queries. This is documented as:

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

and:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Related Question