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:
None 
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
Description:
EXPLAIN shows odd key selection with InnoDB table.

How to repeat:
Execute the script below.

drop table if exists t1;
create table t1(c1 char(100) primary key, c2 char(100)) engine=innodb;
insert into t1 values('aaa', 'apple');
insert into t1 values('bbb', 'book');
insert into t1 values('ccc', 'chair');
create index idx1 on t1(c2);
explain select * from t1 where c2 like '%apple%' or c2 like '%chair%';
explain select * from t1 where c1 <> 'aaa';

The last two EXPLAIN show these results, respectively.
+--+-----------+-----+-----+-------------+----+-------+---+----+-----+
|id|select_type|table|type |possible_keys|key |key_len|ref|rows|Extra|
+--+-----------+-----+-----+-------------+----+-------+---+----+-----+
|1|SIMPLE|t1|index| (null) | idx1 | 101 | | 3 |Using where; Using index |
+--+-----------+-----+-----+-------------+----+-------+---+----+-----+

+--+-----------+-----+-----+-------------+----+-------+---+----+-----+
|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 |
+--+-----------+-----+-----+-------------+----+-------+---+----+-----+

Possible_keys is NULL or PRIMARY. But idx1 is selected as a key. Why?

Suggested fix:
No idea. 
This happens also with 5.0.17 for Linux.
This doesn't happen with MyISAM table.
[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.