MySQL Bugs: #29998: EXPLAIN docs should explain the 'table' column more fully

Bug #29998	EXPLAIN docs should explain the 'table' column more fully
Submitted:	24 Jul 2007 3:44	Modified:	14 May 2012 17:24
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	All	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
From http://dev.mysql.com/doc/refman/5.0/en/explain.html,

"table

The table to which the row of output refers."

It's more complex than that with derived tables and unions, though; in these cases the 'table' column seems to have references to the tables that are contained within the derived table or union.  There can be both forward and backward references, and you have to know in which order to read them to resolve the references.

It took me several hours of trying various things to get to where I think I understand how to read this column.  Perhaps others would find a fuller explanation helpful too.

How to repeat:
Here is a confusing case:

mysql> explain select * from (select 1 as foo from mysql.user as user_1 union select 1 from mysql.user as user_2) as der_1 union select * from  (select 1 as foo from mysql.user as user_3 union all select 1 from mysql.user as user_4) as der_2;
+----+--------------+------------+--------+...
| id | select_type  | table      | type   |...
+----+--------------+------------+--------+...
|  1 | PRIMARY      | <derived2> | system |...
|  2 | DERIVED      | user_1     | index  |...
|  3 | UNION        | user_2     | index  |...
| NULL | UNION RESULT | <union2,3> | ALL    |...
|  4 | UNION        | <derived5> | ALL    |...
|  5 | DERIVED      | user_3     | index  |...
|  6 | UNION        | user_4     | index  |...
| NULL | UNION RESULT | <union5,6> | ALL    |...
| NULL | UNION RESULT | <union1,4> | ALL    |...
+----+--------------+------------+--------+...

It is not clear what all those numbers in angle brackets mean, and in what order one should read them.  If you know how they work, you can understand the query plan, but it took me quite a while to learn.

Thank you for the report.

Verified as described.

Will be covered by WL#5144.

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.

http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

"
table

The name of the table to which the row of output refers. This can also be a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N, or a value like <derivedN> to indicate that the row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.
"

Also, for the id column:

"
id

The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.
"