8.8.2 EXPLAIN Output Format

8.8.2 EXPLAIN Output Format

The EXPLAIN statement provides information about the execution plan for the SELECT statement:

EXPLAIN returns the row information for each table used by the SELECT statement, which lists the order in which MySQL reads the output of the statements when they are processed.

MySQL solves all connections using nested loops, which means that MySQL reads a record from the first table,

In the 2nd table, the third table, and so on, find the matching row,

When all the tables are processed, the MySQL output selects the columns.

When used with the Extended keyword, EXPLAIN generates additional information that can be performed by

Show warnings statement after the EXPLAIN statement, EXPLAIN EXTENDED also shows the filtered columns.

Attention:

You can't use extended and partitions keywords together in the same explain statement,

EXPLAIN output Columns EXPLAIN export format

EXPLAIN join Types EXPLAIN Join type

EXPLAIN Extra Information Expalin additional information

EXPLAIN output Interpretation EXPLAIN outputs interpretation

Interpret output columns:

This section describes the output columns that explain explain production, and later provides additional information about the type and additional columns.

EXPLAIN Each output line provides information about a table, each row contains the values summarized in 8.1,

The output column of the EXPLAIN, described in more detail in the table. The column name is displayed in the first column of the table, and the second column provides the equivalent property name, which is displayed in the format of JSON when output is used

Column JSON Name Meaning
ID select_id the Select identifier
Select_type None the SELECT type
Table table_name The table for the output row
partitions partitions the matching partitions
Type Access_type the Join type
Possible_keys Possible_keys The possible indexes to choose
Key key The index actually chosen
Key_len key_length The length of the chosen key
Ref ref the columns compared to the index
Rows rows estimate of rows to examined
Filtered filtered percentage of rows filtered by table condition
Extra None Additional Information

Mysql> explain SELECT cpi.personname, CCD.CLIENTSN, Ccd.income, Ccd.pay, Ccd.accountbalance, Ccd.createdtime, Ccd.remark from
-> (SELECT * from Clientcashdetail ccd_int where
-> 1 >
-> (SELECT count (CLIENTSN) from Clientcashdetail
-> where clientsn= ccd_int.clientsn and Ccd_int.createdtime < Createdtime and Createdtime < TIMESTAMP (@dated_tim e))
-> and Ccd_int.createdtime < TIMESTAMP (@dated_time)
->) CCD
-> right JOIN clientpersonalinfo cpi on CPI.CLIENTSN = CCD.CLIENTSN
-> where CCD.CLIENTSN in (SELECT clientsn to Clientpersonalinfo where PersonName in (
-> ' Chua Beng Huat ',
-> ' Yuan Xiu Feng ',

->))
-> ORDER by Cpi.personname,  CCD.CLIENTSN,  ccd.createdtime DESC;

+--+ —————— –+ —————— –+--–+ ————— + ————-+ ——— + ——————-+--+ ——————————— +
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+--+ —————— –+ —————— –+--–+ ————— + ————-+ ——— + ——————-+--+ ——————————— +
| 1 | PRIMARY | CPI | All | PRIMARY | NULL | NULL | NULL | 937 | Using temporary; Using Filesort |
| 1 | PRIMARY | Clientpersonalinfo | Eq_ref | PRIMARY | PRIMARY | 4 | Zjzc.cpi.clientSn | 1 | Using where |
| 1 | PRIMARY | | Ref | | | 4 | Zjzc.cpi.clientSn | 10 | NULL |
| 2 | DERIVED | Ccd_int | All | NULL | NULL | NULL | NULL | 5999 | Using where |
| 3 | DEPENDENT subquery | Clientcashdetail | All | NULL | NULL | NULL | NULL | 5999 | Using where |
+--+ —————— –+ —————— –+--–+ ————— + ————-+ ——— + ——————-+--+ ——————————— +
5 rows in Set (0.11 sec)

Attention:

The JSON attribute is empty and does not appear in the JSON format

ID (JSON name:select_id) first column Select ID

The select identifier, which is the number in the query in the select-specific order. The value can be null,

If the row points to a union result of another row, in which case the table column displays a value like