MySQL Execution plan

MySQL Execution plan

• Optimizing queries with explain

• Explain output format

• Extended Explain output format

• Get execution plan information for a named connection

• Estimating Query Performance

EXPLAIN Syntax {EXPLAIN|DESCRIBE| DESC} tbl_name[col_name | Wild]{EXPLAIN|DESCRIBE| DESC}    [Explain_type]{explainable_stmt|  forCONNECTION connection_id}explain_type: {EXTENDED|Partitions|FORMAT=format_name}format_name: {traditional|json}explainable_stmt: {SELECTStatement| DELETEStatement| INSERTStatement| REPLACEStatement| UPDATEstatement}

1. Using explain to optimize queries

The Explain statement provides information about how MySQL executes the statement:

• The explain applies to select,delete,insert,replace and UPDATE statements.

• When explain is used with an explanatory statement, MySQL displays information about the statement execution plan in the optimizer. In other words, MySQL explains how it will handle statements, including information about how tables are connected and in what order.

• When explain FOR CONNECTION connection_id is executed, the execution plan with the statement that will be displayed connection_id executes.

• For the SELECT statement, explain generates additional execution plan information that can be displayed using show warnings.

• Explain is useful for checking queries involving partitioned tables.

• The format option allows you to select an output format. Traditional displays the output in tabular format. If the Define format option is not displayed, the default value is traditional. The JSON format displays information in JSON format.

With the help of explain, you can see where you should add an index to the table to execute the statement faster by using the index to find rows. You can also use explain to check whether the optimizer joins tables in the best order. To prompt the optimizer to use the connection order corresponding to the order of the named tables in the SELECT statement, use SELECT STRAIGHT_JOIN instead of select to start the statement. (see "SELECT Syntax".) However, Straight_join may prevent the use of indexes because it disables half-connection conversions. See "Using semi-join transformations to refine subqueries, derived tables and view references."

Optimizer tracking can sometimes provide information that complements the explain information. However, the optimizer tracking format and content may vary between versions. For more information, see internal mysql: Trace Optimizer.

If you are having problems when you think you should use the index, run Analyze table to update the tables statistics that might affect the optimizer's choices, such as the cardinality of the key. See "ANALYZE table Syntax".

Attention
Explain can also be used to get information about the columns in the table. EXPLAIN tbl_namewith DESCRIBE tbl_name and SHOW COLUMNS FROM tbl_name synonymous.

2. Explain output format

The Explain statement provides information about how MySQL executes the statement. Explain applies to,, SELECT DELETE INSERT , REPLAC E, and UPDATE statements.

Explain returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order in which MySQL reads them when processing the statements. MySQL uses a nested loop join method to resolve all connections. This means that MySQL reads a row from the first table, then finds a matching row in the second table, the third table, and so on. After all the tables are processed, MySQL outputs the selected columns and backtracking through the table list until it finds a table with more matching rows. Read the next row from the table and continue to the next table.

The explain output includes partition information. In addition, for the SELECT statement, explain generates extended information, which can be displayed using the explain SHOW WARNINGS .

Note
In older versions of MySQL, EXPLAIN PARTITIONS EXPLAIN EXTENDED partition and extension information is used and generated. These syntaxes can still be backwards compatible, but partitioning and extended output are now enabled by default, so the partitions and extended keywords are redundant and deprecated. Their use will result in warnings, and they will be removed from the explain syntax in future versions of MySQL.

You cannot use the deprecated partitions and extended keywords together in the same explain statement. In addition, none of these keywords can be used with the format option.

• Explain output columns

• EXPLAIN Join type

• EXPLAIN Extra Information

• Explain output interpretation

2.1 Explain output column

Mysql>ExplainSelect *  fromEmployees.t1whereT1.emp_noinch(SelectEmp_no fromemployees.salaries);+----+--------------+-------------+------------+--------+----------------+------------+---------+------------- --------+---------+----------+-------------+|Id|Select_type| Table       |Partitions|Type|Possible_keys| Key        |Key_len|Ref|Rows|Filtered|Extra|+----+--------------+-------------+------------+--------+----------------+------------+---------+------------ ---------+---------+----------+-------------+|  1 |Simple|T1| NULL       |  All    | NULL           | NULL       | NULL    | NULL                | 2837194 |   100.00 |Usingwhere ||  1 |Simple| <Subquery2> | NULL       |Eq_ref| <Auto_key>     | <Auto_key> | 4       |Employees.t1.emp_no|       1 |   100.00 | NULL        ||  2 |Materialized|Salaries| NULL       | Index  | PRIMARY, Emp_no|Emp_no| 4       | NULL                | 2838426 |   100.00 |UsingIndex |+----+--------------+-------------+------------+--------+----------------+------------+---------+------------- --------+---------+----------+-------------+3Rowsinch Set,1Warning (0.00Sec

Each output line of the explain provides information about a table. As the following table:

Column	JSON Name	meaning
Id	select_id	Query serial number
Select_type	None	Query type
Table	table_name	Table referenced by the output line
Partitions	Partitions	Matching partitions
Type	Access_type	Type of connection used
Possible_keys	Possible_keys	Indicates which indexes MySQL can use in this table to help with queries. If blank, indicates that no indexes are available
Key	Key	The actual selected index
Key_len	Key_length	The length of the index to use. The shorter the length the better, without loss of accuracy
Ref	Ref	Shows which column of the index is being used
Rows	Rows	The number of rows that MYSQL considers must be checked to return the requested data
Filtered	Filtered	Percentage of rows filtered by table criteria
Extra	None	Additional Information

• ID (JSON name:select_id)
  • MySQL query Optimizer The sequence number that is queried in the selected execution plan. Represents the order in which a SELECT clause or action table is executed in a query, the higher the ID value, the higher the priority, and the first execution. The IDs are the same, and the execution sequence is top to bottom.

• Select_type (JSON name:none)
  • The type of select, which can be any of the types listed in the following table.

Select_type Value	JSON Name	meaning
Simple	None	Simple select (does not use a union or subquery)
PRIMARY	None	The outermost Select
UNION	None	The second or subsequent select query in UNION that does not depend on the result set of an external query
DEPENDENT UNION	Dependent (true)	The second or subsequent SELECT statement in the Union, dependent on the external query
UNION RESULT	Union_result	Result set for UNION query
Subquery	None	The first select query in a subquery that does not depend on the result set of an external query
DEPENDENT subquery	Dependent (true)	The first select in a subquery, dependent on the result set of an external query
DERIVED	None	Used in cases where there is a subquery in the FROM clause. MySQL executes these subqueries recursively, putting the results in a temporary table
Materialized	Materialized_from_subquery	Materialized sub-query
Uncacheable subquery	Cacheable (False)	The result set cannot be cached by a subquery and must be evaluated again for each row of the outer query
Uncacheable UNION	Cacheable (False)	Second or subsequent select query in UNION, which belongs to a non-cacheable subquery

• • DEPENDENTUsually indicates the use of a related subquery.
  • DEPENDENT SUBQUERYEvaluation UNCACHEABLE SUBQUERY differs from evaluation. For dependent subquery, subqueries are reassessed only for each set of different values for variables from its external context. For uncacheable subquery, subqueries are re-evaluated for each row in the external context.
  • The cacheable nature of a subquery differs from the cache of query results in the query cache (see "How the query cache operates"). The subquery cache occurs during query execution, and the query cache is used to store the results only after the query execution is complete. When you use explain to specify format = JSON, the output does not have a single property that is directly equivalent to Select_type; The Query_block property corresponds to the given select. You can use properties that are equivalent to most of the select subquery types that you just displayed (example materialized materialized_from_subquery) and display when appropriate. Simple or primary has no JSON equivalents.
  • The Select_type value of a non-SELECT statement displays the statement type of the affected table. For example, for a DELETE statement, Select_type is a delete.
• Table (JSON name:table_name)
  The name of the table referenced by the output row. This can also be one of the following values:
  • <unionM,N>: This row refers to the set of rows with an ID value of M and N.
  • <derivedN>: The row refers to the derived table result of a row with an ID value of N. For example, a derived table might come from a subquery in the FROM clause.
  • <subqueryN>: The row references the result of the materialized subquery for the row with the ID value n
• Partitions (JSON name:partitions)
  • Records the partitions that will match the query. For non-partitioned tables, this value is null.
• Type (JSON name:access_type)
  • The connection type. Used to describe different types of connections
• Possible_keys (JSON Name:possible_keys)
  • possible_keysIndicates which indexes MySQL can use in this table to facilitate querying. If blank, indicates that no indexes are available
• Key (JSON Name:key)
  • keyThe column represents the (index) that MySQL actually decides to use key . If MySQL decides to use one of the Possible_keys indexes to find a row, the index will be listed as key value.
  • keyAn index that does not exist in the Possible_keys value may be named. This can happen if all Possible_keys indexes are not suitable for finding rows, but all columns selected by the query are columns of other indexes. That is, the named index overrides the selected column, so although it is not used to determine which rows to retrieve, index scans are more efficient than data row scans.
  • For InnoDB, a secondary index may overwrite the selected column even if the query also selects a primary key, because InnoDB stores the primary key value along with each secondary index. If key is null, then MySQL cannot find the index that is used to execute the query more efficiently.
  • To force MySQL to use or ignore the indexes listed in the Possible_keys column, use in the query FORCE INDEX , USE INDEX or IGNORE INDEX . Please refer to "index hint".
  • For the MyISAM table, running analyze table helps the optimizer to select a better index. The same is true for MyISAM tables, Myisamchk--analyze.
• Key_len (JSON name:key_length)

MySQL Execution plan