Skip to content

MySQL index access methods

In MySQL query execution plans, the type column in the output of EXPLAIN indicates the specific access method (or join type) used to retrieve data from tables.^[600-developer-database-mysql-mysql-index.md] This field details how MySQL optimized the retrieval of rows, ranging from full table scans to highly efficient index lookups.^[600-developer-database-mysql-mysql-index.md]

Access Method Categories

Access methods are generally categorized by their efficiency. The most efficient types typically involve accessing a single row or a distinct range of rows via unique keys, while less efficient methods involve scanning entire indexes or tables.

Efficient Access (Single Row or Unique Lookups)

  • system: This is a special case of the const type, occurring when the query targets a system table containing only a single row.^[600-developer-database-mysql-mysql-index.md]
  • const: Used when the query matches a row using a Primary Key or a Unique Index, and the result is known to be a single record.^[600-developer-database-mysql-mysql-index.md] Because there is only one match, MySQL treats this row as a constant for the duration of the query optimization.
  • eq_ref: Typically encountered during join queries.^[600-developer-database-mysql-mysql-index.md] This method is used when an index is retrieved for each row from the previous table, utilizing a PRIMARY KEY or UNIQUE NOT NULL index to associate the tables.^[600-developer-database-mysql-mysql-index.md]

Range and Index Scans

  • ref: This indicates that MySQL is using a non-unique index to look up data.^[600-developer-database-mysql-mysql-index.md] It will scan the index to find all rows matching a specific value or set of values.
  • ref_or_null: A variation of ref that is specifically optimized to look up values that are either the requested value or are NULL.^[600-developer-database-mysql-mysql-index.md]
  • range: This method signifies an index range lookup.^[600-developer-database-mysql-mysql-index.md] Instead of scanning the entire index, MySQL retrieves rows based on a specified range (e.g., using operators like BETWEEN, >, <).

Index Scans and Subqueries

  • index: MySQL will traverse the entire index tree.^[600-developer-database-mysql-mysql-index.md] While this is a scan, it is generally faster than scanning the data rows (ALL) because the index entries are typically smaller than the full row data.
  • index_subquery: Used within subqueries, this method indicates that an index is being accessed using the ref access method on the result of the subquery.^[600-developer-database-mysql-mysql-index.md]
  • unique_subquery: Similar to index_subquery, but used when the subquery employs eq_ref (i.e., accessing unique keys).^[600-developer-database-mysql-mysql-index.md]

Full Scans and Specialized Indexes

  • fulltext: This access type is triggered when the query utilizes a FULLTEXT index.^[600-developer-database-mysql-mysql-index.md]
  • ALL: This represents the least efficient access method, where MySQL performs a full table scan, reading data from every row in the table sequentially.^[600-developer-database-mysql-mysql-index.md] In the context of the provided material, this is the only method listed that effectively represents not using an index for row lookup, as all other types imply some form of index usage.^[600-developer-database-mysql-mysql-index.md]
  • [[MySQL Indexes]]
  • [[Query Execution Plan]]
  • [[Slow Query Log]]

Sources

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