How to use explain to query SQL execution plan in MySql

SELECT type, which can be any of the following:

• SIMPLE: Simple SELECT (without UNION or subquery)

• PRIMARY: the outermost SELECT

• UNION: The second or subsequent SELECT statement in a UNION

• DEPENDENT UNION: The second or subsequent SELECT statement in a UNION depends on the outer query

• UNION RESULT: The result of UNION

• SUBQUERY: The first SELECT in a subquery

• DEPENDENT SUBQUERY: The first SELECT in a subquery that depends on the outer query

• DERIVED: SELECT of the exported table (subquery in the FROM clause)

The type of join. Here are the various join types, ranked from best to worst:

• The system: table has only one row (= system table). This is a special case of the const join type.

• const: The table has at most one matching row, which will be read when the query starts. Because there is only one row, the column values ​​in this row can be considered constants by the rest of the optimizer. const tables are fast because they are only read once!

• eq_ref: For each combination of rows from the previous table, read a row from this table. This is probably the best join type, except for const types.

• ref: For each combination of rows from the previous tables, all rows with matching index values ​​will be read from this table.

• ref_or_null: This join type is like ref, but adds that MySQL can specifically search for rows containing NULL values.

• index_merge: This join type indicates that the index merge optimization method is used.

• unique_subquery: This type replaces the ref of the following form of IN subquery: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index search function that can completely replace the subquery and is more efficient.

• index_subquery: This join type is similar to unique_subquery. Can replace IN subqueries, but only for non-unique indexes in subqueries of the following form: value IN (SELECT key_column FROM single_table WHERE some_expr)

• range: retrieve only the rows in a given range, using an index to select the rows.

• index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

• ALL: For each combination of rows from the previous table, a full table scan is performed, indicating that the query needs to be optimized.

Generally speaking, you need to ensure that the query reaches at least the range level, preferably the ref level.

This column contains detailed information about how MySQL solved the query.

• Distinct: After MySQL finds the first matching row, it stops searching for more rows for the current row combination.

• Select tables optimized away MySQL returns data without traversing the table or index at all, indicating that it has been optimized to the point where it cannot be optimized any further.

• Not exists: MySQL can perform LEFT JOIN optimization on the query. After finding a row that matches the LEFT JOIN criteria, it will no longer check more rows in the table for the previous row combination.

• range checked for each record (index map: #): MySQL did not find a good index to use, but found that if the column values ​​from the previous table are known, perhaps a partial index can be used.

• Using filesort: MySQL needs an extra pass to figure out how to retrieve the rows in sorted order, which means the query needs to be optimized.

• Using index: Retrieve column information from a table using only the information in the index tree without further searching to read the actual rows.

• Using temporary: In order to solve the query, MySQL needs to create a temporary table to hold the results, which means that the query needs to be optimized.

• Using where: The WHERE clause is used to restrict which rows are matched to the next table or sent to the client.

• Using sort_union(...), Using union(...), Using intersect(...): These functions illustrate how to merge index scans for the index_merge join type.

• Using index for group-by: Similar to the Using index method for accessing a table, Using index for group-by means that MySQL has found an index that can be used to query all columns of a GROUP BY or DISTINCT query without having to search the hard disk to access the actual table.