skip to content

Index Types & EXPLAIN

InnoDB secondary indexes carry the primary key, and MySQL adds FULLTEXT, spatial and invisible indexes. Interviewers hand you EXPLAIN output and a slow query.

questions

6

In MySQL's EXPLAIN output, what do the type, key, key_len, rows and filtered columns tell you about how a table is read?

level: middleimportance: must knowfreq 78%

answer

  1. access method, best to worst
  2. the index actually chosen
  3. how many key parts are used
  4. estimate times percentage

basics

~20 s

type is the access method, from const and eq_ref down through ref, range and index to ALL, a full scan. key is the chosen index, key_len how much of it is used, and rows times filtered estimates rows passed on.

solid answer

~50 s

`type` is the join type, MySQL's name for the access method, listed in the manual from best to worst: `system`, `const` (at most one row by a full primary key or unique key), `eq_ref` (one row per row of the previous table through a `PRIMARY KEY` or `UNIQUE NOT NULL` index), `ref` (all rows matching a non-unique key or a leftmost prefix), `range`, `index` (a full scan of an index tree) and `ALL` (a full table scan). `possible_keys` lists candidate indexes and `key` the one chosen, `NULL` if none. `key_len` shows how many bytes of the key are used, which tells you how many key parts apply. `rows` is the estimated rows examined, and `filtered` the estimated percentage that survives the table's condition, so `rows` times `filtered` is the estimate passed to the next table.

code

sql · 11 lines
sql
-- orders: PRIMARY KEY (id), KEY idx_customer (customer_id), KEY idx_created (created_at)
-- order_items: PRIMARY KEY (id), KEY idx_order (order_id)

EXPLAIN SELECT * FROM orders WHERE id = 42;                      -- type const
EXPLAIN SELECT * FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE oi.id BETWEEN 1 AND 100;                                 -- orders: type eq_ref
EXPLAIN SELECT * FROM orders WHERE customer_id = 7;              -- type ref
EXPLAIN SELECT * FROM orders
  WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'; -- type range
EXPLAIN SELECT * FROM orders WHERE note LIKE '%gift%';           -- type ALL (note is not indexed)

go deeper

for a junior

Recall the common type values in order, const, eq_ref, ref, range, index, ALL, and that ALL is a full table scan.

for a middle

Explain each column precisely: when eq_ref applies, why index can mean a covering scan or a full scan, how key_len counts key parts and how rows and filtered combine.

for a senior

Read a multi-table plan for where rows explode, distinguish a missing index from an index the optimizer rejected, and know that every number is an estimate.

for a principal

Decide what a team reviews before shipping a query, such as EXPLAIN on new hot-path queries and a rule about ALL on large tables, and what that review cannot catch.

## One row per table MySQL's `EXPLAIN` prints one row per table in the plan, in the order the tables are read. In MySQL 8.4 the default output is the traditional table, set by `explain_format` (default `TRADITIONAL`; `JSON` and `TREE` are the alternatives). The columns this answer covers are the ones that say **how a table is reached**: | Column | JSON name | Meaning | |---|---|---| | `type` | `access_type` | the join type: how rows are found | | `possible_keys` | `possible_keys` | indexes the optimizer could choose | | `key` | `key` | the index actually chosen | | `key_len` | `key_length` | bytes of that index's key that are used | | `ref` | `ref` | columns or constants compared to the key | | `rows` | `rows` | estimated rows to examine | | `filtered` | `filtered` | estimated percentage kept by the table condition | ## The type column, from best to worst The manual lists the join types ordered from best to worst. The ones you meet daily: 1. **`system`**: the table has exactly one row; a special case of `const`. 2. **`const`**: at most one matching row, read once at the start, because every part of a `PRIMARY KEY` or `UNIQUE` index is compared to a constant (`WHERE id = 42`). 3. **`eq_ref`**: in a join, exactly one row is read from this table for each row of the earlier tables, using all parts of a `PRIMARY KEY` or `UNIQUE NOT NULL` index. Apart from `system` and `const`, it is the best type. 4. **`ref`**: all rows with a matching key value are read, because the index is not unique or only a leftmost prefix is used. Good when the key matches few rows. 5. **`range`**: an index retrieves rows within one or more ranges (`BETWEEN`, `>`, `IN()`, `LIKE 'abc%'`). `ref` is `NULL` here. 6. **`index`**: the whole index tree is scanned. If the index covers the query, `Extra` says `Using index` and this is cheaper than reading rows; if not, it is a full table scan in index order. 7. **`ALL`**: a full table scan. Normally not good on the first table not marked `const`, and usually very bad in all other cases. Between `ref` and `range` the list also holds `fulltext`, `ref_or_null` (like `ref` plus a search for `NULL`), `index_merge` (several indexes of one table combined, with `key` listing them all) and the subquery types `unique_subquery` and `index_subquery`. The order is a guide, not a verdict. A `ref` that matches half the table can cost more than a narrow `range`, which is why `type` has to be read together with `rows`. ## key, possible_keys and key_len - **`possible_keys` `NULL`** means no index is relevant to the conditions; that is the clearest sign a missing index is the problem. - **`key`** is the index used, or `NULL` if none. It can name an index absent from `possible_keys`: when no candidate helps find rows but some index covers every selected column, the optimizer scans that index instead of the table. - **`key_len`** is the length in bytes of the part of the key that is used. On a multi-column index it tells you how many leading columns the lookup actually used. A nullable column adds one byte, so a nullable `INT` key part shows 5, not 4. - **`ref`** shows what the key is compared with: `const`, a column of an earlier table, or `func` when it is an expression (`SHOW WARNINGS` after `EXPLAIN` shows which). ## rows and filtered: the estimates - **`rows`** is the number of rows MySQL believes it must examine. For InnoDB it is an estimate from index statistics and may be off. - **`filtered`** is the estimated percentage of those rows kept by the conditions on this table, with 100 meaning no filtering. `rows` × `filtered` / 100 is the estimate joined with the next table: 1000 rows at 50.00 means about 500. - A large `rows` with a tiny `filtered` on `type: ALL` means the table is read in full to keep a handful of rows: usually an index is missing, unusable for this condition, or judged too costly. ## Reading a plan quickly - Find the first row whose `type` is `ALL` or `index` without `Using index`. - Check `possible_keys`: `NULL` points at a missing index; a list with `key` `NULL` means the optimizer judged the candidates too costly. - Use `key_len` to see whether a multi-column index is used in full or only its first column. - Multiply `rows` by `filtered` down the plan to see where row counts explode. `EXPLAIN` shows estimates only. Running the query and timing each step is `EXPLAIN ANALYZE`, covered with query and server tuning.

  • In MySQL's EXPLAIN, how can key name an index that is not listed in possible_keys?
    When none of the candidate indexes helps locate rows but some index contains every column the query selects, the optimizer scans that index instead of the table, because the index tree is smaller than the rows. The row shows `type` `index` with `Using index`. In InnoDB this includes secondary indexes covering primary key columns, which every secondary entry stores.
  • In MySQL's EXPLAIN, a composite index (a, b, c) on INT NOT NULL columns shows key_len 4. What does that tell you?
    Each `INT NOT NULL` key part is 4 bytes, so `key_len` 4 means only column `a` is used for the lookup; `b` and `c` are not part of the access, perhaps because the query skips `b` or uses a range on `a`. With all three used it would be 12. Nullable columns add one byte each, so account for `NULL` before counting.

  • type index means an efficient index lookup
  • ref is always better than range whatever rows says
  • rows is the exact number of rows the query returns
  • possible_keys lists the index the query will use
  • key_len is the length of the whole index
open as a page

In MySQL with InnoDB, what does a secondary index entry contain, and how does the optimizer use the primary key columns stored there?

level: middleimportance: must knowfreq 65%

basics

~20 s

Each InnoDB secondary index entry holds the indexed columns followed by the row's primary key, which is used to find the row. The optimizer treats those appended columns as extra key parts for lookups, sorting and covering.

open as a page

A MySQL product-search query's EXPLAIN shows type ALL, key NULL and Extra 'Using where; Using filesort'. What does each part tell you, and how would you fix the query's access path?

level: seniorimportance: must knowfreq 62%

basics

~20 s

type ALL with key NULL means a full scan with no usable index; Using where means the server discards most rows read; Using filesort means an extra sort pass. Index the equality columns, then the ORDER BY column.

open as a page

In MySQL with InnoDB, which kinds of index can you create, and which data structure backs each kind?

level: juniorimportance: should knowfreq 50%

basics

~20 s

InnoDB builds PRIMARY KEY, UNIQUE and plain indexes as B-trees, the primary key being the clustered index that holds the rows. FULLTEXT indexes are inverted word lists, SPATIAL indexes are R-trees, and a requested HASH index becomes a B-tree.

open as a page

In MySQL 8.4, how do prefix, descending and functional index key parts differ, and what does each restrict about the queries using it?

level: middleimportance: should knowfreq 38%

basics

~20 s

A prefix part col(N) indexes the first N characters, so it cannot cover the column or fully order it. A DESC part stores keys in descending order. A functional part ((expr)) indexes an expression, used only when the query repeats it exactly.

open as a page

In MySQL, before dropping a suspected-unused secondary index on a large table, how would you test the drop with an invisible index, and what does invisibility leave unchanged?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

Run ALTER TABLE ... ALTER INDEX idx INVISIBLE, watch plans, the slow query log and Performance Schema, then drop it or make it VISIBLE again instantly. An invisible index is still maintained on writes and still enforces uniqueness.

open as a page