Explain SQL EXPLAIN syntax (1)

1. EXPLAIN tbl_name

EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or show columns from tbl_name.

2. EXPLAIN [EXTENDED] SELECT select_options

If the keyword "EXPLAIN" is placed before the SELECT statement, MySQL will EXPLAIN how it processes the SELECT statement and provide the order of how the table is joined and joined.

With the help of EXPLAIN, you can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records.

You can also know whether the optimizer joins the table in an optimal order. To force the optimizer to set a SELECT statement to join in the table naming order, the statement should start with STRAIGHT_JOIN, not just SELECT.

EXPLAIN returns a row of information for each table in the SELECT statement. Tables are listed in the order they are read by MySQL during query processing. MySQL scans multiple connections once (single-sweep multi-join) to resolve all connections. This means that MySQL reads a row from the first table, finds a matching row in the second table, and then in 3rd tables. After all the tables are processed, It outputs the selected columns and returns the table list until it finds a table with more matching rows. Read the next row from the table and continue processing the next table.

When the EXTENDED keyword is used, EXPLAIN generates additional information, which can be viewed using show warnings. This information shows the optimizer limits the tables and column names in the SELECT statement, what the SELECT statement looks like after rewriting and executing the optimization rule, and may include other annotations of the optimization process.

Each output row of EXPLAIN provides information about a table, and each row includes the following columns:

Id:SELECT identifier. This is the serial number of the SELECT query.

Select_type:SELECT type.

Table:Table Name

Type:Join type