MySQL Bugs: #74773: Explain documentation needs update for Select tables optimized away

Bug #74773	Explain documentation needs update for Select tables optimized away
Submitted:	10 Nov 2014 22:37	Modified:	13 Nov 2014 16:15
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6,5.7	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The documentation for the "Select tables optimized away" EXPLAIN extra information states that it appears for MyISAM tables:

Select tables optimized away

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

but that output appears for InnoDB tables in addition to MyISAM tables.

How to repeat:
use test;
create table test (id int unsigned NOT NULL PRIMARY KEY) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

insert into test (id) values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

explain select max(id) from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

Suggested fix:
Update the documentation

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

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

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE `test` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.53 sec)

mysql 5.7 >
mysql 5.7 > insert into test (id) values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.7 > explain select max(id) from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.03 sec)

mysql 5.7 > SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 >

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

I'll just remove mention of a particular storage engine, in case it might also apply to additional engines in the future.