MySQL "Using index" in EXPLAIN's extra column

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).

  1. by sobstel • August 2011 • blog archive