why mysql explain says 'using index' while the index used do not contain required fields

Question

Here is the output of an explain command in mysql(innodb):

explain select * from multi_index_test_tbl_1 force index(`query_index_1`) where `text_field1`='0' order by `numeric_field2` desc limit 1000000;
+----+-------------+------------------------+------+---------------+---------------+---------+-------+----------+--------------------------+
| id | select_type | table                  | type | possible_keys | key           | key_len | ref   | rows     | Extra                    |
+----+-------------+------------------------+------+---------------+---------------+---------+-------+----------+--------------------------+
|  1 | SIMPLE      | multi_index_test_tbl_1 | ref  | query_index_1 | query_index_1 | 386     | const | 53547628 | Using where; Using index |
+----+-------------+------------------------+------+---------------+---------------+---------+-------+----------+--------------------------+

The schema of table multi_index_test_tbl_1 is as follow:

CREATE TABLE IF NOT EXISTS `multi_index_test_tbl_1` 
(
    `text_field1` varchar(128) NOT NULL,
    `numeric_field1` float NOT NULL,
    `numeric_field2` float NOT NULL,
    `text_field2` varchar(128) NOT NULL,
    PRIMARY KEY (`text_field1`,`numeric_field1`,`text_field2`),
    KEY `query_index_1` (`text_field1`,`numeric_field2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It seems that query_index_1 is used. And 'using index' appears in Extra while the index query_index_1 doesn't contain all fields in table multi_index_test_tbl_1.

Since mysql doc says that:

Using index (JSON property: using_index)

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

I'm confused what's exactly happening here.

Answer 1

Ah, but it does 'contain required fields'. To elaborate...

InnoDB includes all the columns of the PRIMARY KEY in each 'secondary' index. So,

KEY `query_index_1` (`text_field1`,`numeric_field2`)

is really more like

KEY `query_index_1` (`text_field1`,`numeric_field2`,
                     `numeric_field1`,`text_field2`)

In your example, that includes all the columns. Hence, everything in the SELECT, including * is found in that secondary index. So, "Using index" is 'correct'.

This is one way in which InnoDB is sometimes better than MyISAM.

Try EXPLAIN FORMAT=JSON SELECT ... to get more details.