How to Learn the extended options of MySQL explain statements

Source: Internet
Author: User
Tags mysql query optimization
How to Learn the extended option of MySQL explain statement
MySQL has an explain command that can be used to analyze the running effect of the SELECT statement. For example, explain can obtain the SELECT statement.
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, explain the statement to be analyzed and add the extended option.
Mysql> explain Extended Select * from account \ G;
* *************************** 1. row ***************************
ID: 1
Select_type: simple
Table: Account
Type: All
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 1
Filtered: 100.00
Extra:
1 row in set, 1 warning (0.00 Sec)

run show warnings
mysql> show warnings \ G;
**************************** 1. row **************************
level: Note
code: 1003
message: Select 'dbunit '. 'account '. 'id' as 'id', 'dbunit '. 'account '. 'name' as 'name' from 'dbunit '. 'account'
1 row in SET (0.00 Sec)
from the output results of show warnings, we can see that the original select * was optimized by MySQL to
select 'dbunit '. 'account '. 'id' as 'id', 'dbu Nit '. 'account'. 'name' as 'name '.
explain extended not only tells us what MySQL query optimization can do, but also tells us what MySQL query optimization can do.
. In this article, the author uses explain
extended + show warnings to find the areas that cannot be optimized in the MySQL query optimizer.
from the explain extended select * From sbtest where ID> 5 and ID> 6 and c = "a" and pad = C
statement output, we know MySQL query optimization. you cannot combine the two query conditions ID> 5 and ID> 6 into an ID> 6.

In MySQL performance's explain extendedArticleIn the third example and the extended option Article of the explain in the static room,
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.

/******************************CodeAnd the extended option **************/
Mysql> explain extended select * from t where a in (select B from I );
+ ---- + -------------------- + ------- + ------ +
| ID | select_type | table | type |
+ ---- + -------------------- + ------- + ------ +
| 1 | primary | T | all |
| 2 | dependent subquery | I | all |
+ ---- + -------------------- + ------- + ------ +
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 ***************************
Level: Note
Code: 1003
Message: Select 'test '. 'T '. 'A' as 'A', 'test '. 'T '. 'B' as 'B', 'test '. 'T '. 'C' as 'C'
From 'test'. 't'where
<In_optimizer> ('test'. 'T'. 'A ',
<Exists> (select 1 as 'not _ used' from 'test'. 'I'
Where (<cache> ('test'. 'T'. 'A') = 'test'. 'I'. 'B ')))
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 static 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 in the explain extended statement 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.
/*************************************** **************************************** **********************/
Mysql> explain extended select t1.id, t2.pad from sbtest T1, sbtest T2 where t1.id = 5
And t2.k = t1.k;
+ ---- + ------------- + ------- + --------------- + --------- + ------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- +
| 1 | simple | T1 | const | primary, k | primary | 4 | const | 1 |
| 1 | simple | T2 | ref | K | 4 | const | 55561 |
+ ---- + ------------- + ------- + --------------- + --------- + ------- +
2 rows in set, 1 warning (0.00 Sec)

Mysql> show warnings \ G
* *************************** 1. row ***************************
Level: Note
Code: 1003
Message: Select 'test '. 't1 '. 'id' as 'id', 'test '. 't2 '. 'pad 'As 'pad' from 'test '. 'sbtest' 't1'
Join 'test '. 'sbtest' 't2' where ('test '. 't2 '. 'K' = 'test '. 't1 '. 'k') and ('test '. 't1 '. 'id' = 5 ))
1 row in SET (0.00 Sec)

/************************************ **************************************** * ******************/
from the explain results, we can obtain that table T1 is used for query. "const" type, that is to say, during MySQL query,
t1.id = 5 first finds t1.k and then uses the value of t1.k to query data in table T2, obviously, the query optimization result is not found in the
show warings output.
conclusion
or reference the statements in this article about the extended option of explain." From the output of explain extend, we can see
the SQL Execution method, which is helpful for analyzing SQL ".

related resources

explain extended MySQL performance
explain extended option of the Quiet Room
MySQL Reference Manual syntax (obtain select-related information)

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.