EXPLAIN EXTENDED followed by SHOW WARNINGS shows a transformed version of the 
original SQL statement. The transformed version sometimes includes special 
markers that are not legal SQL, and whose meaning is not always clear.

Example:

mysql> explain extended select * from t where t.a in (select b from t);
+----+--------------------+-------+-------+---------------+---------+---------+-
-----+------+----------+--------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | 
ref  | rows | filtered | Extra                    |
+----+--------------------+-------+-------+---------------+---------+---------+-
-----+------+----------+--------------------------+
|  1 | PRIMARY            | t     | index | NULL          | PRIMARY | 8       | 
NULL |    4 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t     | index | NULL          | PRIMARY | 8       | 
NULL |    4 |   100.00 | Using where; Using index |
+----+--------------------+-------+-------+---------------+---------+---------+-
-----+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` 
where <in_optimizer>(`test`.`t`.`a`,<exists>(select 1 AS `Not_used` from 
`test`.`t` where (<cache>(`test`.`t`.`a`) = `test`.`t`.`b`)))
1 row in set (0.00 sec)

Here, <in optimizer>, <exists>, and <cache> are special constructs.

The purpose of this task is to list all such such constructs and explain their 
meaning. This will likely require the help of someone from the optimizer team.

Related bugs: BUG#29998, BUG#48134

You must be logged in to tag this worklog

No Comments yet