Introduction to extended options in MySQL EXPLAIN statements

Source: Internet
Author: User
Tags mysql query optimization

The following articles mainly describe the practical application and specific operation steps of the extended option in the MySQL EXPLAIN statement. We all know that there is an explain command in the MySQL database, its main function is to analyze the Running Effect of select statements. For example, explain can obtain select statements.

Indexes used, sorting, and so on. In addition, the extended extension of explain can be used on the basis of the original explain

Some additional query optimization information can be obtained through the show warnings command of MySQL. The following is a simple example.

First, execute MySQL explain for the statement to be analyzed and add the extended option.

MySQL> explain extended select * from account \ G;

1. row

 
 
  1. id: 1  
  2. select_type: SIMPLE  
  3. table: account  
  4. type: ALL  
  5. possible_keys: NULL  
  6. key: NULL  
  7. key_len: NULL  
  8. ref: NULL  
  9. rows: 1  
  10. filtered: 100.00  
  11. Extra:  
  12. 1 row in set, 1 warning (0.00 sec) 

Run Show Warnings.

MySQL> show warnings \ G;

1. row Level: Note

 
 
  1. Code: 1003  
  2. Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`  
  3. 1 row in set (0.00 sec) 

From the output results of show warnings, we can see that the original select * is optimized by MySQL

Select 'dbunit '. 'account'. 'id' AS 'id', 'dbunit'. 'account'. 'name' AS 'name '.

Explain extended not only tells us what MySQL query optimization can do, but also tells us what MySQL

Query Optimization cannot do anything. MySQL performance Extended EXPLAIN in this article, the author uses explain

Extended + show warnings found the areas where the MySQL query optimizer cannot query and optimize.

From explain extended SELECT * FROM sbtest WHERE id> 5 AND id> 6 AND c = "a" AND pad = c

The statement output shows that the MySQL query optimizer cannot combine the two query conditions id> 5 and id> 6 into an id> 6.

In the MySQL performance explain extended article, the third example and the quiet room MySQL explain extended option article,

The two authors have also made further experiments on explain extended. From these two articles, we can draw a conclusion from

In the explain extend output, we can see the SQL Execution method, which is helpful for analyzing SQL.

The content in this article is excerpted from the explain extended option of the quiet room.

The following code is used to analyze the extended options of explain copied to the static room.

 
 
  1. MySQL>explain extended select * from t where a in (select b from i);  
  2. +—-+——————–+——-+——+  
  3. | id | select_type | table | type |  
  4. +—-+——————–+——-+——+  
  5. | 1 | PRIMARY | t | ALL |  
  6. | 2 | DEPENDENT SUBQUERY | i | ALL |  
  7. +—-+——————–+——-+——+  
  8. 2 rows in set, 1 warning (0.01 sec) 

The SUBQUERY seems to have nothing to do with external queries. Why does MySQL display dependent subquery,

What about external queries? We can see the cause from the explain extended result.

MySQL> show warnings \ G

1. row

 
 
  1. Level: Note  
  2. Code: 1003  
  3. Message: select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`c` AS `c`  
  4. from `test`.`t` where  
  5. <in_optimizer>(`test`.`t`.`a`,  
  6. <exists>(select 1 AS `Not_used` from `test`.`i`  
  7. where (<cache>(`test`.`t`.`a`) = `test`.`i`.`b`)))  
  8. 1 row in set (0.00 sec) 

Here, MySQL has rewritten the SQL and optimized in.

The above code and analysis are excerpted to the extended option of explain in the quiet room

However, you must note that the query statement after "optimization" is obtained from EXPLAIN extended + show warnings.

It may not be the final optimized SQL statement, or the information displayed by MySQL explain extended is not enough to indicate that MySQL is the most

The final result of query statement optimization. This is also the second article in MySQL formance's explain Extended article.

The example illustrates this situation.

 
 
  1. MySQL> EXPLAIN extended SELECT t1.id,t2.pad FROM sbtest t1, sbtest t2 WHERE t1.id=5 
  2. AND t2.k=t1.k;  
  3. +—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  4. | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |  
  5. +—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  6. | 1 | SIMPLE | t1 | const | PRIMARY,k | PRIMARY | 4 | const | 1 | |  
  7. | 1 | SIMPLE | t2 | ref | k | k | 4 | const | 55561 | |  
  8. +—-+————-+——-+——-+—————+———+———+——-+——-+——-+  
  9. 2 rows IN SET, 1 warning (0.00 sec)  
  10. MySQL> SHOW warnings \G  

1. row Level: Note

 
 
  1. Code: 1003  
  2. Message: SELECT `test`.`t1`.`id` AS `id`,`test`.`t2`.`pad` AS `pad` FROM `test`.`sbtest` `t1`  
  3. JOIN `test`.`sbtest` `t2` WHERE ((`test`.`t2`.`k` = `test`.`t1`.`k`) AND (`test`.`t1`.`id` = 5))  
  4. 1 row IN SET (0.00 sec) 

From the Explain results, we can see that table t1 queries use the "const" type, that is, when MySQL queries

T1.k is first found by t1.id = 5 and then the t1.k value is used to query data in table t2. Obviously, the query optimization result is not in

In the next Show Warings output, find.

Summary

We can also reference the statements in this article in the extended option of explain. "From the output of MySQL explain extend, we can

It is helpful to analyze the SQL Execution method ".

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.