Description:
I have several examples, where the information shown in explain, is not correct, or not expected.
The 1st paragraph IMHO explain is showing the wrong relations
The 2nd and 3rd paragraphs are a question of expectation.
The last (2nd note on example 4) paragraph is IMHO not according to the documentation, but the docu might be changed
1st example
# explain select * from (select 1 union select 2) t1;
The primary select, refers to the union result, not to the first table in the union
2nd/ 3rd example
# explain select 1;
# explain select * from (select 1 a) tbl1;
explain states the number of rows as NULL (not known), in example 3 it shows NULL for the derived, but know suddenly for the outer table that there is 1 row
example 4
# select * from u1 union all select * from u2;
explain shows the number of rows, for each induvidual table, but not for the union result (this could be known, at least for a union all)
example 4
# select * from u1 union all select * from u2;
according to the doc PRIMARY is the outermost query. In this example the UNION RESULT is the outermost query, not u1.
In this case, it needs to be decided, if the union result should be shown as primary, or union result.
Otherwhise the docu should point out that in a union the primary is not the outermost (the queries are in sequence, not embeded into each other), but the first query of the union, (which determines column types/names, if given)
How to repeat:
explain select * from (select 1 union select 2) t1;
+----+--------------+------------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+--------------+------------+------+---------------+
| 1 | PRIMARY | <derived2> | ALL | NULL |
| 2 | DERIVED | NULL | NULL | NULL |
| 3 | UNION | NULL | NULL | NULL |
|NULL | UNION RESULT | <union2,3> | ALL | NULL |
+----+--------------+------------+------+---------------+
explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
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 |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
create table u1 select 1 a; create table u2 select 2;
explain select * from u1 union all select * from u2;
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | u1 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | UNION | u2 | system | NULL | NULL | NULL | NULL | 1 | |
|NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------+------+---------+------+------+-------+
Suggested fix:
-
