MySQL ignores my index

Question

I'm quite new to setting indexes myself. I'm currently just experimenting with it to discover how it works and in what cases a database will make use of the index.

I've got a simple table with 3 columns; an id, a name and a status. I've set an index on the name which is a CHAR(30) column. Against my expectations, MySQL ignores this index in the following query:

SELECT * FROM people WHERE name = 'Peter'

id  select_type  table   type  possible_keys  key   key_len  ref    rows  Extra

1   SIMPLE       people  ref   name           name  90       const  1     Using where

However, when using the following query, the index is used:

SELECT COUNT(*) FROM people WHERE name = 'Peter'

id  select_type  table   type  possible_keys  key   key_len  ref     rows  Extra

1   SIMPLE       people  ref   name           name  90       const   1     Using where; Using index

Could anyone please explain this to me?

Answer 1

For each query, the "key" columns indicates "name" -- so, I'd say your two queries both used the index called "name", which probably is on the "name" column -- and this is what you want (quoting the manual) :

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

Also, you are only going through "1" row, which is good (no full-scan or anything like that).

The "type" says "ref", which seems to be a good thing :

All rows with matching index values are read from this table for each combination of rows from the previous tables. ... If the key that is used matches only a few rows, this is a good join type.

ref can be used for indexed columns that are compared using the = or <=> operator.

And the "ref" column indicates "const" -- not sure what it means exactly, but as far as I know, it's a good thing.

What makes you think your index is not used for one column ?

Just as a reference, for more informations : 7.2.1. Optimizing Queries with EXPLAIN

Answer 2

"Using index" means it's using the index as a "covering index". This happens when it only needs to access the index to satisfy the query.

If, on the other hand, "Using index" is absent, but in the "key" column, the index is named, then it's using that index in the way described in the "ref" column.

So in both cases it's using the index, but only the COUNT() uses it as a covering index.