MySQL Internals Manual  /  ...  /  The index Join Type The index Join Type

Consider this query:

SELECT column1 FROM Table1;

If column1 is indexed, then the optimizer may choose to retrieve the values from the index rather than from the table. An index which is used this way is called a covering index in most texts. MySQL simply uses the word index in EXPLAIN descriptions.

For this query:

SELECT column1, column2 FROM Table1;

the optimizer will use join type = index only if the index has this definition:

CREATE INDEX ... ON Table1 (column1, column2);

In other words, all columns in the select list must be in the index. (The order of the columns in the index does not matter.) Thus it might make sense to define a multiple-column index strictly for use as a covering index, regardless of search considerations.