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 SELECT passenger_id, email FROM passengerdetails WHERE email LIKE 'bob%'
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
countr_email_idx is listed as the index that is used after all. Why does this happen?