Mysql explain syntax

The EXPLAINtbl_name or: EXPLAINSELECTselect_optionsEXPLAIN statement can be used as a synonym for DESCRIBE. It can also be used to obtain information about the SELECT statement to be executed by MySQL. The syntax of EXPLAINtbl_name is the same as that of DESCRIBEtbl_name or SHOWCOLUMNSFROMtbl_name. Before a SELECT statement

The EXPLAINtbl_name or: EXPLAIN SELECTselect_options EXPLAIN statement can be used as a synonym for DESCRIBE. It can also be used to obtain information about the SELECT statement to be executed by MySQL. The syntax of EXPLAINtbl_name is the same as that of DESCRIBEtbl_name or show columns FROMtbl_name. Before a SELECT statement

EXPLAINtbl_name

Or:

EXPLAIN SELECTselect_options

The EXPLAIN statement can be used as a synonym for DESCRIBE, or used to obtain information about the SELECT statement to be executed by MySQL.

• The EXPLAIN tbl_name syntax is the same as DESCRIBE tbl_name or show columns from tbl_name.
  
• When the keyword "EXPLAIN" is used before a SELECT statement, MYSQL explains how to run the SELECT statement, and displays the table connection and connection sequence.
  

This topic describes the second type of EXPLAIN usage.

With the help of EXPLAIN, you will know when to add an index to the table to use the index to search for records so that SELECT can run faster.

If the index is not properly used, you can run analyze table to update the statistical information of the TABLE, such as the base of the key, which can help you make better choices in terms of optimization. For details, see "analyze table Syntax ".

You can also check whether the optimizer connects data tables in the optimal order. To connect the optimizer to the table name in the SELECT statement, SELECT STRAIGHT_JOIN can be used at the beginning of the query, not just SELECT.

EXPLAIN returns a row of records, which includes information about each table used in the SELECT statement. These tables are listed in the results in the read order of the MySQL query to be executed. MySQL uses the single-sweep (multi-join) method to resolve the connection. This means that MySQL reads a record from the first table, finds the corresponding record in the second table, searches in the third table, and so on. When all the tables are scanned, It outputs the selected fields and traces back to all the tables until they cannot be found, some tables may have multiple matching records. The next record will be read from the table and processed from the next table.

In MySQL version 4.1, the format of EXPLAIN output results has changed, making it more suitable for the structure of UNION statements, subqueries, and derived tables. It adds two new fields: id and select_type. If you use MySQL 4.1 earlier than MySQL, you will not be able to see these fields.

The relevant information of each table is displayed in each line of the EXPLAIN result. Each line of record contains the following fields:

Id
The identifier of this SELECT statement. Each SELECT statement in a query has an ordered value. Select_type
The SELECT type may include the following: SIMPLE
Simple SELECT (UNION or subquery is not used)
PRIMARY
SELECT of the outermost layer.
UNION
The second layer uses UNION after SELECT.
DEPENDENT UNION
The second SELECT in the UNION statement depends on external subqueries.
SUBQUERY
The first select in the subquery
DEPENDENT SUBQUERY
The first subquery in the subquery depends on the external SUBQUERY.
DERIVED
Derived table SELECT (subquery in the from clause)
Table
Record the referenced table. Type
Table connection type. The following lists various types of table connections, from the best to the worst:
System
The table has only one record (equal to the system table ). This is a special case of the const table connection type.
Const
A table can contain at most one matching record, which is read from the beginning of the query. Since there is only one record, the field values recorded by this row in the remaining optimization programs can be treated as a constant value. The const Table query is very fast, because only one read is required! Const is used to compare a fixed value with a primary key or UNIQUE index. In the following queries, tbl_name is the const table:

SELECT * FROMtbl_nameWHEREprimary_key=1;SELECT * FROMtbl_nameWHEREprimary_key_part1=1 ANDprimary_key_part2=2;

Eq_ref
A row of records is read from the table and associated with the records read from the previous table. Different from the const type, this is the best connection type. It is used for connecting all parts of the index and the index is of the primary key or UNIQUE type. Eq_ref can be used to retrieve fields when "=" is compared. The comparative values can be fixed values or expressions, and the fields in the table can be used in the expressions. They are ready before reading the table. In the following examples, MySQL uses the eq_ref connection to process ref_table:

SELECT * FROMref_table,other_tableWHEREref_table.key_column=other_table.column;SELECT * FROMref_table,other_tableWHEREref_table.key_column_part1=other_table.columnANDref_table.key_column_part2=1;

Ref
All records that match the search value in this table will be taken out and used together with the records obtained from the previous table. Ref is the leftmost prefix used by the Connection Program to use the KEY, or the KEY is not a primary key or a UNIQUE index (in other words, the connection program cannot retrieve only one record based on the KEY value. When only a few matching records are queried based on the key value, this is a good connection type. Ref can also be used to compare fields by using the = Operator. In the following examples, MySQL uses ref to process ref_table:

SELECT * FROMref_tableWHEREkey_column=expr;SELECT * FROMref_table,other_tableWHEREref_table.key_column=other_table.column;SELECT * FROMref_table,other_tableWHEREref_table.key_column_part1=other_table.columnANDref_table.key_column_part2=1;

Ref_or_null
This connection type is similar to ref. The difference is that MySQL will search for records containing NULL values during retrieval. This connection type is optimized from MySQL 4.1.1 and is often used for subqueries. In the following example, MySQL uses the ref_or_null type to process ref_table:

SELECT * FROMref_tableWHEREkey_column=exprORkey_columnIS NULL;

Index_merge
This connection type means that the Index Merge optimization method is used. In this case, the key field includes all the indexes used, and the key_len includes the longest part of the key used.
Unique_subquery
This type replaces ref with IN subqueries IN the following form:

valueIN (SELECTprimary_keyFROMsingle_tableWHEREsome_expr)

Unique_subquery is only used to replace the index lookup function of a subquery, which is more efficient.

Index_subquery
The connection type is similar to unique_subquery. It uses a subquery to replace IN, but it is used when there is no unique index IN the subquery, for example, the following form:

valueIN (SELECTkey_columnFROMsingle_tableWHEREsome_expr)

Range
Only records within a given range are retrieved and an index is used to obtain a record. The key field indicates which index is used. The key_len field contains the longest part of the key used. In this type, the ref field value is NULL. Range IS used to compare a field with a field by any of the following operators: =, <>,>, >=, <, <=, is null, <=>,, or IN:

SELECT * FROMtbl_nameWHEREkey_column= 10;SELECT * FROMtbl_nameWHEREkey_columnBETWEEN 10 and 20;SELECT * FROMtbl_nameWHEREkey_columnIN (10,20,30);SELECT * FROMtbl_nameWHEREkey_part1= 10 ANDkey_part2IN (10,20,30);

Index
The connection type is the same as that of ALL. The difference is that it only scans the index tree. It is usually faster than ALL because the index file is usually smaller than the data file. MySQL uses this connection type when the queried field knowledge is a separate part of the index.
ALL
Scan all the tables and combine the records obtained from the previous table. At this time, if the first table is not identified as const, it is not very good. In other cases, it is usually very bad. Normally, you can add indexes to quickly retrieve records from the table to avoid ALL.
Possible_keys
The possible_keys field indicates the index that MySQL may use when searching table records. Note that this field is completely independent from the table sequence displayed in the EXPLAIN statement. This means that the indexes contained in possible_keys may not be used in actual use. If the value of this field is NULL, it indicates that no index is used. In this case, you can check which fields in the WHERE clause are suitable for adding indexes to improve query performance. In this case, create an index and then use the EXPLAIN command to check the index. If you want to see what indexes are available for the table, you can use show index from tbl_name. Key
The key field shows the indexes actually used by MySQL. When no index is used, the value of this field is NULL. To enable MySQL to forcibly USE or IGNORE the INDEX list in the possible_keys field, you can USE the keyword force index, use index, or ignore index in the query statement. For MyISAM and BDB tables, you can use analyze table to ANALYZE which index is better. For tables of the MyISAM type, the same effect is achieved by running the command myisamchk -- analyze. Key_len
The key_len field shows the index length used by MySQL. If the value of the key field is NULL, the index length is NULL. Note that the value of key_len tells you which indexes MySQL uses in the Union index. Ref
The ref field shows which fields or constants are used to work with keys to query records from the table. Rows
The rows field shows the number of records that MySQL considers to be retrieved in the query. Extra
This field displays the additional MySQL information in the query. The following is an explanation of several different values of this field:
Distinct
When MySQL finds the first record of the matching Union result of the current record, it no longer searches for other records. Not exists
When MySQL performs a left join optimization during query, when it finds that the current table matches the left join condition with the previous record, it will no longer search for more records. The following is an example of this type of query:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.idWHERE t2.id IS NULL;

If t2.id is defined as not null. In this case, MySQL scans table t1 and searches for records in T2. When a matched record is found in t2. This means that t2.id will not be NULL, and other records with the same id value will not be searched in T2. It can also be said that for each record in t1, MySQL only needs to perform a search in T2. no matter how many matching records actually exist in T2.

Range checked for each record (index map :#)
MySQL does not find an appropriate available index. Instead, for each row join in the previous table, it performs a test to determine which index to use (if any ), use this index to retrieve records from the table. This process is not very fast, but it is always faster than doing table join joins without any indexes. Using filesort
MySQL needs to perform an additional step to obtain records in an ordered order. The ordering program traverses all records based on the connection type, and stores the keys to be sorted and pointers to records that meet the WHERE condition. These keys have been sorted out, and the corresponding records are also sorted out. Using index
The field information is obtained directly from the index tree, instead of scanning the actual records. Fields used for query are part of an independent index. Using temporary
MySQL needs to create a temporary table Storage result to complete the query. This usually occurs when the query contains the group by and order by clauses, which list fields in different ways. Using where
The WHERE clause will be used to limit which records match the next table or send to the client. Unless you particularly want to obtain or check ALL records, it may indicate a problem when the queried Extra field value is not Using where and the table connection type is ALL or index.

If you want to make the query as fast as possible, you should note that the Extra field values are Using filesort and Using temporary.

You can get a rough idea about how the connection works by the product of the rows field value in the EXPLAIN result. It roughly tells us how many records MySQL will query during the Query Process. If the system variable max_join_size is used to obtain the query result, the product can also be used to determine which multi-Table SELECT statements will be executed.

The following example shows how to optimize the performance of Multi-table joint query by using the information provided by EXPLAIN.

Assume that the following SELECT statement is used, and the EXPLAIN statement is intended to be used for detection:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,            tt.ProjectReference, tt.EstimatedShipDate,            tt.ActualShipDate, tt.ClientID,            tt.ServiceCodes, tt.RepetitiveID,            tt.CurrentProcess, tt.CurrentDPPerson,            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,            et_1.COUNTRY, do.CUSTNAME        FROM tt, et, et AS et_1, do        WHERE tt.SubmitTime IS NULL            AND tt.ActualPC = et.EMPLOYID            AND tt.AssignedPC = et_1.EMPLOYID            AND tt.ClientID = do.CUSTNMBR;

In this example, make the following assumptions:

• The fields to be compared are defined as follows:
  

  Table	Column	Column Type
  Tt	ActualPC	CHAR (10)
  Tt	AssignedPC	CHAR (10)
  Tt	ClientID	CHAR (10)
  Et	Employee ID	CHAR (15)
  Do	CUSTNMBR	CHAR (15)

  
  
• The data table index is as follows:
  

  Table	Index
  Tt	ActualPC
  Tt	AssignedPC
  Tt	ClientID
  Et	Employee ID (primary key)
  Do	CUSTNMBR (primary key)

  
  
• The tt. ActualPC values are unevenly distributed.

Before any optimization measures are taken, the results of the EXPLAIN analysis are as follows:

table type possible_keys key  key_len ref  rows  Extraet    ALL  PRIMARY       NULL NULL    NULL 74do    ALL  PRIMARY       NULL NULL    NULL 2135et_1  ALL  PRIMARY       NULL NULL    NULL 74tt    ALL  AssignedPC,   NULL NULL    NULL 3872           ClientID,           ActualPC      range checked for each record (key map: 35)

Because the field type is ALL for each table value, this result means that MySQL creates a dikar product for ALL tables, that is, the combination of each record. This takes a long time because you need to scan the total number of records in each table. In this case, its product is 74*2135*74*3872 = 45,268,558,720 records. If the data table is larger, you can imagine how long it will take.

The problem here is that when the field definition is the same, MySQL can use indexes for these fields more quickly (for an ISAM table, unless the field definition is the same, otherwise, the index will not be used ). On this premise, VARCHAR and CHAR are the same unless they are defined with different lengths. Since tt. ActualPC is defined as CHAR (10) and et. employee ID is defined as CHAR (15), the length of the two is different.
To solve this problem, we need to use alter table to increase the length of ActualPC from 10 to 15 characters:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Now tt. ActualPC and et. EMPLOYID are both VARCHAR (15)
. Execute the EXPLAIN statement again to see the result:

table type   possible_keys key     key_len ref         rows    Extratt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using             ClientID,                                         where             ActualPCdo    ALL    PRIMARY       NULL    NULL    NULL        2135      range checked for each record (key map: 1)et_1  ALL    PRIMARY       NULL    NULL    NULL        74      range checked for each record (key map: 1)et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

This is not enough. It can do better: the rows value product is 74 times less. This query takes 2 seconds.
The second change is to eliminate the inconsistent length of fields in tt. AssignedPC = et_1.EMPLOYID and tt. ClientID = do. CUSTNMBR:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),    ->                MODIFY ClientID   VARCHAR(15);

The EXPLAIN result is as follows:

table type   possible_keys key      key_len ref           rows Extraet    ALL    PRIMARY       NULL     NULL    NULL          74tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using             ClientID,                                         where             ActualPCet_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

This seems to be the best result.
The legacy problem is that MySQL considers the tt. ActualPC value of the field to be evenly distributed by default, but not in the table tt. Fortunately, we can easily let MySQL analyze the index distribution:

mysql>ANALYZE TABLE tt;

By now, the table connection has been optimized perfectly. The EXPLAIN result is as follows:

table type   possible_keys key     key_len ref           rows Extratt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using             ClientID,                                        where             ActualPCet    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

Note that the value of the rows field in the explain result is also roughly guessed by the MySQL connection optimization program. Check whether the value is basically the same as the actual value. If not, you can use STRAIGHT_JOIN In the SELECT statement to achieve better performance.
Each table is listed in different order in a clause.