MySQL is ignoring my index
I am completely new to setting up indexes. I am currently just experimenting with this to see how it works and in what cases the database will use the index.
I have a simple table with three columns; id, name and status. I have set an index for the name, which is a CHAR (30) column. Contrary to 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, using the following query uses the index:
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
Can anyone explain this to me?
"Index usage" means that it uses the index as "coverage index". This happens when it only needs to access the index to satisfy the request.
If, on the other hand, there is no "Use Index" but an index is named in the "key" column, then it uses that index as described in the "ref" column.
So in both cases it uses an index, but only COUNT () uses it as a coverage index.
source to share
For each query, the columns " key
" point to " name
" - so I would say that your two queries used an index called " name
", which is probably in "" - and this is what you want (quoting the manual ):
The key column specifies the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to search for a row, that index is specified as key value.
Also, you only pass the " 1
" line , which is good (no full scan or anything like that).
" type
" says " " which seems good: ref
All rows with corresponding index values are read from this table for each combination of rows from the previous table .... 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 column " ref
" indicates " const
" - not sure what that means exactly, but as far as I know, it's good.
What makes you think your index is not being used for a single column?
As a link, for more information: 7.2.1. Optimizing Queries with EXPLAIN
source to share