Bug #8683 explain could show aliases of derived tables
Submitted: 22 Feb 2005 13:12 Modified: 27 May 2006 12:33
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:4.1 OS:Any (*)
Assigned to: CPU Architecture:Any

[22 Feb 2005 13:12] Martin Friebe
Description:
in the example below explain show from which derived tables, the primary select gets its data.

Explain only names this tables as "<derivedN>". since mysql insists on an alias for those tables anyway, it would be nice if this alias could be shown in addition.

How to repeat:
explain select * from  (select 1 a) tbl1; 
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL |    NULL | NULL |    1 |               
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL |    NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
2 rows in set (0.00 sec)

# or if any table t1 exists

explain select * from  (select * from t1) tbl1;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL |    NULL | NULL |   22 |       |
|  2 | DERIVED     | t1         | ALL  | NULL          | NULL |    NULL | NULL |   22 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

Suggested fix:
Modify the result to something like:

explain select * from  (select 1 a) tbl1;
 +----+-------------+------------+--------+---
| id | select_type | table      | type   |
+----+-------------+------------+--------+----
|  1 | PRIMARY     | tbl1 <derived2> | system |
|  2 | DERIVED     | NULL       | NULL   |
+----+-------------+------------+--------+------
[27 May 2006 12:33] Valeriy Kravchuk
Thank you for a reasonable feature request.