In MySQL's EXPLAIN output, what do the type, key, key_len, rows and filtered columns tell you about how a table is read?
answer
- access method, best to worst
- the index actually chosen
- how many key parts are used
- estimate times percentage
basics
~20 stype 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-- 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
Recall the common type values in order, const, eq_ref, ref, range, index, ALL, and that ALL is a full table scan.
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.
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.
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