samedi 18 juin 2016

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

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.

Aucun commentaire:

Enregistrer un commentaire