WL#5144: Document EXPLAIN EXTENDED output syntax
Affects: Server-5.1
—
Status: Complete
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 (`test`.`t`.`a`,(select 1 AS `Not_used` from
`test`.`t` where ((`test`.`t`.`a`) = `test`.`t`.`b`)))
1 row in set (0.00 sec)
Here, , , and 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#31430, BUG#48134, BUG#63575
Related worklogs: WL#4897
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.