Bug #18639 | EXPLAIN shows odd key | ||
---|---|---|---|
Submitted: | 30 Mar 2006 3:28 | Modified: | 6 Nov 2006 14:21 |
Reporter: | Yoshiaki Tajika (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.0.21-BK, 5.0.19 | OS: | Linux (Linux, Windows 2000) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[30 Mar 2006 3:28]
Yoshiaki Tajika
[30 Mar 2006 11:22]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described also with 5.0.21-BK (+ ChangeSet@1.2120, 2006-03-30 08:13:49+02:00) on Linux: mysql> drop table if exists t1; Query OK, 0 rows affected (0.07 sec) mysql> create table t1(c1 char(100) primary key, c2 char(100)) engine=innodb; Query OK, 0 rows affected (0.10 sec) mysql> insert into t1 values('aaa', 'apple'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('bbb', 'book'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('ccc', 'chair'); Query OK, 1 row affected (0.01 sec) mysql> create index idx1 on t1(c2); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where c2 like '%apple%' or c2 like '%chair%'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t1 | index | | idx1 | 101 | | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.02 sec) mysql> explain select * from t1 where c1 <> 'aaa'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t1 | index | PRIMARY | idx1 | 101 | | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain extended select * from t1 where c1 <> 'aaa'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t1 | index | PRIMARY | idx1 | 101 | | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` <> _latin1'aaa') 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.21 | +-----------+ 1 row in set (0.00 sec) So, I see no reason to use (or show in EXPLAIN results) that idx1. For MyISAM it works OK: mysql> alter table t1 engine=MyISAM; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 where c1 <> 'aaa'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where 1 row in set (0.01 sec) It is a bug. ANALYZE table does not help also.
[30 Mar 2006 11:46]
Heikki Tuuri
This is probably an optimizer bug, since InnoDB does not decide the index nor does it print it in EXPLAIN.
[31 Aug 2006 16:34]
Igor Babaev
This is not a bug. The column 'possible_keys' contains the list of names of the indexes that can be used for an index search by the values built from constants or/and from columns of the tables that are accessed earlier by the execution plan. So for the first query there are no such indexes, while for the second query the primary index can be used. The column 'key' contains the index we use to access the needed values of the table. For both queries the index idx1 is indicated as it is covering for each of them. A similar behaviour can be demonstrated for MyISAM tables: mysql> create table t1(c1 char(100) primary key, c2 char(100)) engine=myisam; Query OK, 0 rows affected (0.05 sec) mysql> insert into t1 values('aaa', 'apple'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('bbb', 'book'); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values('ccc', 'chair'); Query OK, 1 row affected (0.00 sec) mysql> create index idx1 on t1(c2); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> explain select c2 from t1 where c2 like '%apple%' or c2 like '%chair%'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t1 | index | NULL | idx1 | 101 | NULL | 3 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) Note that here idx1 is not covering for the query explain select c2 from t1 where c1 <> 'aaa';
[5 Sep 2006 8:40]
Yoshiaki Tajika
You mean that "index covered query" worked in this case, don't you? Now I personally accept this internal behaviour as designed. But manual documentation is not enough then. Such complex meaning can't be read from the manual description. It made me (mis)understand that `possible_keys` are the candicates, and one(possibly, none) of them becomes the actual `key`. I don't think that my way of understanding is minority. I hope improvement of the manual.
[2 Nov 2006 23:14]
Paul DuBois
Changing category to Server: Documentation because this is a doc problem. Assigning to myself.
[2 Nov 2006 23:15]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Updating description of key value as follows: The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value. If is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it does not act to retrieve the selected columns, an index scan is more effecient than a data row scan. For InnoDB, a secondary index might cover the selected columns even if the query also selects the primary key because InnoDB stores the primary key value with each secondary index. If key is NULL, MySQL found no index to use for executing the query more efficiently.
[6 Nov 2006 9:52]
Yoshiaki Tajika
I found a typo, "If is possible" -> "It is possible". And, the expression below is technically difficult to me :-( "... so although it does not act to retrieve the selected columns,..." If possible, another easier expression is welcome.
[6 Nov 2006 10:55]
Valeriy Kravchuk
Thank you for your personal attention to our documentation.
[6 Nov 2006 14:21]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. I've fixed the typo, and revised the last sentence: That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.