MySQL Bugs: #67830: EXPLAIN DELETE shows incorrect possible

Bug #67830	EXPLAIN DELETE shows incorrect possible_keys. EXPLAIN UPDATE, too.
Submitted:	6 Dec 2012 20:10	Modified:	1 Mar 2013 17:10
Reporter:	Sergei Petrunia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.8-rc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It seems, EXPLAIN DELETE shows all indexes in 'possible_keys'.

How to repeat:
Run:

create table t0 (a int);
insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);

create table t1 (a int, b int, filler char(100), key(a), key(b));
insert into t1 
select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' 
from t0 A, t0 B, t0 C;

MySQL [test]> explain delete from t1 order by a limit 2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | a,b           | a    | 5       | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Now, why is index 'b' in possible_keys? It cannot be used to resolve the query. Indeed, if I turn the DELETE into a SELECT, I get possible_keys=NULL:

MySQL [test]> explain select * from t1 order by a limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | index | NULL          | a    | 5       | NULL |    2 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Suggested fix:
Make possible_keys show something meaningful.

EXPLAIN UPDATE has the same problem:

MySQL [test]> explain update t1 set b=3 order by a limit 2;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | t1    | ALL  | a,b           | a    | 5       | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

This is really easy to verify:

[openxs@chief 5.6]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t0 (a int);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> create table t1 (a int, b int, filler char(100), key(a), key(b));
Query OK, 0 rows affected (0.92 sec)

mysql> insert into t1
    -> select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
    -> from t0 A, t0 B, t0 C;
Query OK, 512 rows affected (0.18 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> explain delete from t1 order by a limit 2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | a,b           | a    | 5       | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from t1 order by a limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | index | NULL          | a    | 5       | NULL |    2 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Noted in 5.6.11, 5.7.1 changelogs.

For EXPLAIN DELETE and EXPLAIN UPDATE the possible_keys column listed
all indexes, not just the applicable indexes.