Explain keywords required for mysql Optimization

I. Syntax

EXPLAIN select sid,filename,filetype from (SELECT * from by_disk_file where uid=830814) a

Result:

Id: indicates the SQL Execution sequence. The SQL statements are executed from large to small.

Select_type: Query type, which can be:

1. SIMPLE

Simple SELECT (do not use UNION or subquery)

For example:

explain SELECT * from by_disk_file where uid=830814

2. PRIMARY

Query operations on the outermost layer

For example:

explain select * from (SELECT * from by_disk_file where uid=830814 )

3. UNION

The second or subsequent SELECT statement in UNION

For example:

explain select * from by_disk_file where uid=830814 union all select * from by_disk_file

4. DEPENDENT UNION

The second or subsequent SELECT statement in UNION depends on the external query.

For example:

explain select * from by_disk_file where uid in(select id from by_common_member where username='mm' union all select id from by_common_member where username='gg')

5. UNION RESULT

UNION result

For example:

explain select * from by_disk_file where uid=830814 union all select * from by_disk_file UNION ALL select * from by_disk_file where uid=830814

The UNION query procedure (based on the above content ):

Define a UNION result set-> UNION statement (from right to left)-> the first statement-> get all results

6. SUBQUERY

The first query in the subquery

explain select * from by_disk_file where uid=(select uid from by_common_member where username='mm')

7. DEPENDENT SUBQUERY

A query in a subquery depends on the external query.

explain select * from by_disk_file where sid in (select sid from by_disk_file where uid=830814)

 

explain select * from by_disk_file where sid =(select sid from by_disk_file where uid=830814 LIMIT 1)

 

Note: If the external query is not a range query, it will only be found once, it is SUBQUERY

If a range query similar to in is used, the value is dependent subquery.

8. DERIVED

Query of the derived table (subquery of the from clause)

explain select sid,filename,filetype from (SELECT * from by_disk_file where uid=830814) a