MySQL EXPLAIN execution plan types¶

The type column in the output of EXPLAIN indicates the specific access type (join type) used to find rows in a table.^[600-developer-database-mysql-mysql-index.md] It is a crucial metric for assessing query performance, as it describes how MySQL retrieves data from the table^[600-developer__database__mysql__mysql-index.md] — ranging from a full table scan to the instantaneous lookup of a single row.^[600-developer-database-mysql-mysql-index.md]

Access Types (from worst to best)¶

The following types are listed generally from least to most efficient^[600-developer-database-mysql-mysql-index.md].

ALL¶

Indicates a Full Table Scan. MySQL scans the entire table to find matching rows. This is typically the slowest access type and usually indicates that no usable indexes were found for the query^[600-developer-database-mysql-mysql-index.md, 600-developer__database__mysql__mysql-index.md].

index¶

Performs an Index Scan. MySQL scans the entire index tree rather than the table data. While faster than ALL (as the index is usually smaller than the data), it is still a full scan^[600-developer-database-mysql-mysql-index.md].

range¶

Indicates an Index Range Scan. MySQL retrieves rows based on a specific range of values in an index (e.g., using operators like >, <, BETWEEN, or IN).^[600-developer-database-mysql-mysql-index.md]

ref¶

Indicates a lookup using a Non-Unique Index. MySQL uses an index to find all rows that match a single value^[600-developer-database-mysql-mysql-index.md]. This is common for indexed columns that are not unique (e.g., a lookup by a foreign key or a non-unique name).

eq_ref¶

Used in JOIN operations. MySQL retrieves rows by using the Primary Key or a Unique NOT NULL index to associate tables^[600-developer-database-mysql-mysql-index.md]. For each row from the previous table, exactly one row is read from this table. This is an efficient access type often seen in optimized joins.

const¶

The table has at most one matching row, which is read at the start of the query. This occurs when querying by a Primary Key or Unique Index^[600-developer-database-mysql-mysql-index.md]. Because the result is constant, MySQL treats it as a constant for the rest of the query execution.

system¶

A special case of const. This occurs when the query involves a system table that contains exactly one row^[600-developer-database-mysql-mysql-index.md].

Subquery and Search Types¶

index_subquery: Similar to ref, but specifically used for subqueries^[600-developer-database-mysql-mysql-index.md].
unique_subquery: Replaces eq_ref for subqueries, using a unique index for lookup^[600-developer-database-mysql-mysql-index.md].
ref_or_null: A variation of ref that includes a search for NULL values^[600-developer-database-mysql-mysql-index.md].
fulltext: The lookup is performed using a FULLTEXT index^[600-developer-database-mysql-mysql-index.md].

Index Usage Rule¶

Generally, any access type other than ALL indicates that MySQL is utilizing an index to some degree^[600-developer-database-mysql-mysql-index.md]. Optimizing queries typically involves aiming for ref, eq_ref, const, or system where possible, and avoiding ALL or index scans on large tables.

[[MySQL Index]]
[[Query Optimization]]

Sources¶

600-developer-database-mysql-mysql-index.md
600-developer__database__mysql__mysql-index.md