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

Source: Internet
Author: User

    • (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


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


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


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.