"Select tables optimized Away" in MySQL explain

"Select tables optimized Away" in MySQL explain

Http://blog.chinaunix.net/uid-10449864-id-2956845.html
2009

Today, when doing SQL statement optimization, in the explain, there is a hint:

Mysql> explain SELECT Max (Up_start) as Up_start from Test WHERE up_start > ' 2008-01-19 00:00:00 ' and Up_start < ' 2008-01-19 23:59:59 ';
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
1 row in Set (0.00 sec)

We can also do such a test:
Use a InnoDB table and a MyISAM table for the Select COUNT (*) Test:

MyISAM table Test
mysql> Explain select count (*) from MyISAM;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- -+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------- -----+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select Tables Optimized Away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------- -----+
1 row in Set (0.00 sec)

InnoDB table Test
Mysql> Explain select COUNT (*) from InnoDB;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | Simple | InnoDB | Index | NULL | PRIMARY | 4 | NULL | 4 | Using Index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in Set (0.00 sec)

In the results of these 2 outputs, the extra column outputs the "Select tables optimized Away" statement.
2nd, it is clear that MyISAM has saved the total number of records and returned the results directly, while InnoDB also needs a full table scan.

This is not mentioned in the MySQL manual, but look at the data in the other columns to guess what it means: The select operation is optimized to no longer be optimized (MySQL simply returns data without traversing the table or index).

In the MySQL official site to turn to two paragraphs related to the description, confirmed the above views, the original text is as follows:
For explains on simple count queries (i.e. explain select count (*) from people) the extra section would read "Select tables Optimized away. " Due to the fact, MySQL can read the result directly from the table internals and therefore does not need to PE Rform the Select.

The official address is as follows:
Http://mysql2.mirrors-r-us.net/doc/refman/5.0/en/explain.html

"Select tables optimized Away" in MySQL explain