Use of MySQL EXPLAIN statements

The MySQL EXPLAIN statement can obtain information about how MySQL executes the SELECT statement. The following describes how to use this statement for your reference.

EXPLAIN tbl_name
Or: EXPLAIN [EXTENDED] SELECT select_options

The MySQL EXPLAIN statement can be used as a synonym for DESCRIBE, or obtain information about how MySQL executes the SELECT statement:

EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or show columns from tbl_name. ·
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 the MySQL EXPLAIN statement, you can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records. If an incorrect index is used, run analyze table to update the TABLE statistics, such as the keyword set. This will affect the optimizer's selection.

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.

The MySQL EXPLAIN statement 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 the single-sweep multi-join connections multiple times at a time to solve 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.

Insufficient mysql Indexes

MySql connection string description

Three statuses of mysql triggers

Create MySQL trigger syntax

Provides you with an in-depth understanding of MySQL ifnull () Functions