Skip to content

CONCEPT Cited by 1 source

MySQL access type (EXPLAIN type column)

The type column in MySQL EXPLAIN output describes how MySQL will find the rows it needs in a table — the access method, not the join algorithm. The column is named "join type" in the MySQL manual but the PlanetScale article observes it's "really more accurate to say the access type" — the value appears even on queries with no joins at all.

The values form a rough performance ladder from best (no table access needed) to worst (full table scan).

(Source: sources/2026-04-21-planetscale-how-to-read-mysql-explains.)

The performance ladder

From the post, rendered with the article's traffic-light colour coding:

type What it means
🟢 NULL Query resolvable during optimisation — table not accessed at execution time at all.
🟢 system The table is empty or has exactly one row.
🟢 const Value of the column is a constant (one row matches). Primary-key lookup or UNIQUE index lookup with equality on all key columns.
🟢 eq_ref Clustered or primary-key / UNIQUE index with all key columns NOT NULL, used in a join. One-row-per-preceding-row lookup.
🟢 ref Indexed column accessed via equality (=). Multiple matching rows expected. ref_or_null is the variant that additionally does a second lookup for NULL.
🟡 fulltext JOIN using the table's FULLTEXT index.
🟡 index Entire index is scanned. Cheaper than ALL because rows are read in index order — but still touches every index row. Expensive if the rows are not in the buffer pool.
🟡 range Range scan — limited index scan between two bounds (BETWEEN, <, >, IN(...) with multiple values). Better than index because it doesn't scan the whole index.
🔴 ALL Full table scan. MySQL reads every row of the table.

Green = good performance, yellow = okay, red = bad. This colour coding comes directly from the article.

Worked interpretation

  • type: const / rows: 1 — primary-key or unique-index point lookup. Optimal equality-filter shape.
  • type: eq_ref / rows: 1 — join column is the primary/unique key of the joined table, one row per driving-side row. Also optimal for joins.
  • type: ref / rows: small — non-unique index equality lookup. The canonical shape for WHERE email = 'x' with an index on email. Rows count = estimated number of duplicates.
  • type: range / rows: mid — range query on an indexed column. WHERE created_at > NOW() - INTERVAL 1 DAY with an index on created_at.
  • type: index / rows: N — full index scan. Often seen when the query can be answered from the index alone (covering index) even without a filter; or when the optimiser thinks reading the whole index is cheaper than the whole table.
  • type: ALL / rows: N — full table scan. Red flag unless N is small or the table is tiny. This is the before-state in the canonical composite-index before/after: rows: 299,202 before the index, rows: 1 with type: ref after.

Special access types beyond the ladder

A few additional values exist outside the simple performance ordering:

  • index_merge — MySQL's Index Merge optimisation is using multiple indexes on the same table jointly to resolve a query. The key column contains a list of indexes used. Limited but real: MySQL may choose this over a single composite index in some shapes. See the post's composite-index discussion for why a dedicated composite index usually beats Index Merge on two single-column indexes.
  • unique_subquery — replaces eq_ref for certain IN subqueries shaped like value IN (SELECT primary_key FROM single_table WHERE some_expr).
  • index_subquery — similar to unique_subquery but for non-unique indexes in the subquery.

Why this column is the first thing to read

The type column is the single highest-signal column in EXPLAIN output for quick triage. Reading it first:

  1. ALL or index? — table/index scan, probably missing or mismatched index, dig deeper.
  2. range? — indexed range scan, check rows and key_len to see if the optimiser is using the expected prefix.
  3. ref / eq_ref / const? — indexed equality, usually good. Check rows to confirm cardinality expectation.
  4. NULL / system? — optimised away, no table access needed.

The post's Kendra Puppo worked example walks this ladder twice: first with type: ALL + rows: 299,202 (no usable index), then after a CREATE INDEX fullnames ON employees(last_name, first_name) with type: ref + rows: 1. The access-type jump from ALL to ref is the structural win.

Seen in

  • sources/2026-04-21-planetscale-how-to-read-mysql-explains — canonical wiki source: Savannah Longoria renders the full performance ladder as a colour-coded table, notes the MySQL-manual naming ("join type" vs "access type") discrepancy, and documents index_merge, unique_subquery, index_subquery as out-of-ladder special cases.
Last updated · 378 distilled / 1,213 read