When you see Using Index
showing up in Extra
column of EXPLAIN
output, it means a covering index is used, it is MySQL is able to locate every field for a given table within an index without lookups to table itself.
It’s simply another reason to re-consider using asterisks (*
) in SELECT
statements.
Simplest example:
mysql> EXPLAIN SELECT * FROM people; +-+-+--++-...-+-| | 1 | SIMPLE | people | ALL | ... | | +-+-+--+-+-...-+-+ | id | select_type | table | type | ... | Extra | +-+-+--+-+-...-|-+
It can be also very handy when using joins, and don’t really need data from one (or more) of joined columns.
For me more stuff like this see Jay Pipes “Target Practice: A Workshop in Tuning MySQL Queries” (OSCON 2007).