MySQL explain

The type field indicates the table join type selected by MySQL, which refers to the method of scanning the table during query execution. The join types, ranked from best to worst, are: system, const, eq_ref, ref, range, index, ALL. Here is a detailed introduction to each type:

system

Description: Indicates that the table has only one row (equivalent to a system table). This is one of the best join types because it means the table is very small, requiring almost no scanning.

Example: Suitable for querying static configuration or dictionary tables.

const

Description: Indicates that the table has at most one matching row, typically used for primary key or unique index queries. This join type is very efficient.

Example: SELECT * FROM users WHERE id = 1;

eq_ref

Description: For each row from the previous table, MySQL reads a row from the current table. Typically used in join queries involving a primary key or unique index.

Example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

ref

Description: Indicates an index lookup for each row from the previous table. Suitable for queries using non-unique indexes or prefix indexes.

Example: SELECT * FROM orders WHERE customer_id = 1;

range

Description: Retrieves rows only within a given range, using an index to select rows. This method is more efficient than a full table scan but less so than ref.

Example: SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';

index

Description: A full index scan, traversing the entire index tree without accessing the table. Typically faster than a full table scan (ALL), but still potentially very resource-intensive.

Example: SELECT id FROM orders;

ALL

Description: A full table scan, which is the worst join type. MySQL must check every row in the table to find the matching records.

Example: SELECT * FROM orders;