Bug #23725 Suggestion for documentation: EXPLAIN docs missing some Extra info
Submitted: 27 Oct 2006 15:50 Modified: 10 Jan 2007 18:23
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 (all)
Assigned to: Paul Dubois CPU Architecture:Any

[27 Oct 2006 15:50] Baron Schwartz
Description:
There are three types of information that can appear in the Extra column of EXPLAIN's output, but are not documented.  These are:

No tables used
Impossible WHERE noticed after reading const tables
Select tables optimized away

How to repeat:
Here is an example query that shows all three:

mysql> create table test(day date not null primary key)engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values(current_date);
Query OK, 1 row affected (0.06 sec)

mysql> explain select(exists(select * from (select min(day) as day from test) as x where day < current_date));
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                                      |
|  2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|  3 | DERIVED     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away                        |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Suggested fix:
The documentation should indicate what these values mean.

I thought somewhere "Select tables optimized away" was explained but I can't find it in a search.
[27 Oct 2006 19:32] Sveta Smirnova
Thank you for the report.

Verified as described.
[10 Jan 2007 18:23] Paul Dubois
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.