"Select tables optimized Away" in MySQL explain

Source: Internet
Author: User
Tags mysql manual mysql official site

"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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.