The type explanation of the--explain of the database operation

• (1) SYSTEM
• (2) CONST
• (3) Eq_ref
• (4) REF
• (5) Ref_or_null
• (6) RANGE
• (7) Index_scan
• (8) All
• (9) Unique_subquery
• (Ten) Index_subquery
• (one) Index_merge
• (+) FT

Types of connection operations

(1) SYSTEM

Const exception, when there is only one tuple match on the table

(2) CONST

When the Where condition is filtered, there is at most one tuple match on the table, such as WHERE id = 2 (ID is the primary key, and a value of 2 has either one or none)

(3) Eq_ref

The table that participates in the join operation is the inner table (in the algorithm implemented by the code, the two tables are concatenated as the inner loop of the loop, so the table is called the inner table).

Based on the index (there is a unique index on the connection field or the primary key index, and the operator must be "=" predicate, the index value cannot be null) to do the scan, so that the appearance of a tuple, the inner table only the only one by one-tuple corresponding to it.

(4) REF

Can be used for single-table scans or connections. The table that participates in the connection operation is the inner table.

Index-based (the index on the connection field is a non-unique index, the operator must be a "=" verb, the connection field value is not null) to scan, so that a tuple of the appearance, the inner table can have a number of bars corresponding to it.

(5) Ref_or_null

Similar to ref, only search conditions include the case where the value of the connected field can be null, such as where Col = 2 or col is null

(6) RANGE

Range scanning, index-based scoping, support for operations such as Between,in,>=,like classes

(7) Index_scan

The index is scanned based on the index on the leaf node of the index to find the data that satisfies the condition (no need to access the data file)

(8) All

Full table scan or range Scan: Do not use index, sequential scan, directly read the data on the table (Access data file)

(9) Unique_subquery

In a subquery, a scan based on a unique index, similar to Eq_ref

(Ten) Index_subquery

In a subquery, a scan is based on an index other than a unique index

(one) Index_merge

Multi-range scanning. The join fields for each table connected to the two tables are indexed and indexed, and the results are merged together. Applicable to the and, the intersection operation of the set.

(+) FT

Full text, fulltext search

The type explanation of the--explain of the database operation