How to view MySQL execution plan

Before we show you how to view the MySQL execution plan, let's take a look at a noun explanation that will be mentioned later:

Overwrite index: MySQL can use the index to return the fields in the select list without having to read the data file again according to the index. The index of all data required to satisfy the query is called the Overwrite index (covering index) if you want to use an overlay index, It is important to note that only the required columns are taken out of the select list, not select *, because if all fields are indexed together, the index file is too large and the query performance degrades

Explain view some of the limitations of the execution plan:

• Explain won't tell you about triggers, stored procedures, or user-defined functions that affect queries
• Explain does not consider the various caches
• Explain cannot display the optimizations that MySQL made when executing queries
• Some of the statistics are estimates, not exact values
• Expalin can only interpret select operations, other actions to override as Select to view execution plans

1. Row ***************************             id:1   
Select_type:simple         table:t1          type:ref possible_keys:idx_customer_no           key:idx_customer_no       Key_len:            ref:const          rows:1         extra:using index condition; Using where 1 row in Set (0.00 sec)

ID: is a set of numbers that represents the order in which a SELECT clause or action table is executed in a query.  If the ID is the same, the execution order is from top to bottom; if it is a subquery, the serial number of the ID is incremented, and the higher the ID, the higher the priority, and the first execution. If the ID is the same, it can be thought of as a group, executed from the top down, in all groups, the higher the ID, the higher the priority, the easier it is to execute.

Select_type: Value is Simple,primary,subquery,derived,union,unionresult

• Simple: Indicates that the query does not contain subqueries or union
• Primary: When the query contains any complex sub-sections, the outermost query is marked as primary
• Subquery: Subqueries are included in the Select or where list, the subquery is marked as subquery
• Derived: The subquery contained in the From list is marked as derived
• Union: If the second select appears after the union, it is marked as union, and if the Union is in a subquery in the FROM clause, the outer select is marked as derived
• Unionresult: Select that gets the result from the Union table is marked as union result

Table: Shows which table the data for this row is about

Type: The types of access that represent the way in which rows are found in the table, common types have all,index,range,ref,eq_ref,const,system,null performance from left to right by difference oddities.

• All: The full table scan,mysql will traverse all tables to find the desired row
• Index:full index Scan, traversing only the indexed tree
• Range: Indicates an index range scan, a scan of an index starts at a point, returns a row that matches the value of a domain, a query that is common to between,<,>
• Ref: For a non-unique index scan, returns all rows that match a single value, commonly found in non-unique prefixes that are non-unique indexes, which are unique indexes.
• Eq_ref: Represents a unique index scan, for each index key, only one record in the table matches, common to primary key or unique index scan.
• Const,system represents the use of these types of access when the query portion is optimized and converted into a constant. For example, if you put the primary key in the where list, MySQL can put the query into a constant.  System is a special case of const that uses system when there is only one row in the query table. Null indicates that the table or index is not checked in the execution statement.

Possiblekey: Indicates which index can be used to find rows in the table, and if there are indexes on the fields involved in the query, the index is listed but not necessarily used by the query.

Key: Represents the index used when querying. If an overwrite index is used in the query, the index appears only in key.

Key_len: Represents the number of bytes used by the index, which you can use to settle the index length used in the query

Ref: Represents the link matching criteria for the above table, that is, which columns or constants can be used to find the values on the indexed column.

Rows: estimates the number of rows to read for the desired record, based on MySQL table statistics and index selection.

Extra: Represents additional information that is not in the other column and is also important.

• The using index indicates that the overwrite index is used in the corresponding select.
• Usingwhere indicates that after the storage engine has been searched for records after filtering (Post-filter), if the query fails to use the index, the role of Usingwhere only prompts us to use the Where condition to filter the z result set.
• The using Temporay represents a temporary table to store the result set, which is common in sorting and grouping queries.
• Using Filesort the sort that cannot be done with indexes in MySQL becomes the file sort

How to view MySQL execution plan