Lesson 0004 · Internals & Performance
You built the perfect index and MySQL scans the whole table anyway — here are the four reasons why, and the fix for each.
In Lesson 0003 you learned to spot the symptom:
possible_keys lists your index, but key comes back
NULL and type is ALL.
This lesson explains the causes. Almost every "why won't it use my index?" bug is
one of four things — and once you see the single principle behind them, you'll predict them
before you even run EXPLAIN.
A B-tree index is a sorted list of the bare column's values. MySQL can only seek it when the query compares that bare column, directly, to a type-compatible constant. Wrap the column in a function, feed it a mismatched type, or open the search on the left, and the sorted order is useless — so MySQL falls back to a full scan.
The moment you wrap an indexed column in a function or arithmetic, the index dies. The
index stores created_at values in order — it knows nothing about the order of
YEAR(created_at).
-- index on created_at is IGNORED — column is wrapped
WHERE YEAR(created_at) = 2026
WHERE created_at + INTERVAL 1 DAY > NOW()
WHERE UPPER(last_name) = 'JONES'Fix A — rewrite as a bare-column range (the sargable form):
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' -- uses the index (range)Fix B — add a functional index (MySQL 8.0.13+), which indexes the expression's value instead of the column's: — MySQL Manual: functional key parts "index expression values rather than column or column prefix values… enable indexing of values not stored directly in the table."
ALTER TABLE people ADD INDEX idx_upper ((UPPER(last_name))); -- now UPPER(last_name)= can seekLIKEAn index is a phone book (Lesson 0002): you can find names starting with "Pat", but not names containing "pat" — those are scattered everywhere. So a wildcard on the left kills the index; a wildcard on the right is fine. — MySQL Manual: "the LIKE value begins with a wildcard character" → index not used; "LIKE 'Patrick%'" → index used.
| Query | Index? |
|---|---|
WHERE key_col LIKE 'Pat%' | used (range) |
WHERE key_col LIKE '%Pat%' | ignored — full scan |
WHERE key_col LIKE '%Pat' | ignored — full scan |
Need "contains" search at speed? That's a job for a FULLTEXT index or a search engine — not a B-tree. A trick for "ends-with": store a reversed copy and index that, turning the suffix search into a prefix search.
Compare an indexed string column to a number and MySQL
must implicitly convert — and because many strings ('1', ' 1',
'1a') all collapse to the number 1, the sorted string index can't be
used.
— MySQL Manual: "For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly."
WHERE str_col = 1 -- IGNORED: string column vs number → implicit conversion
WHERE str_col = '1' -- uses the index: types matchThe same trap hides in joins: joining a VARCHAR user_id to a
BIGINT user_id, or across columns with different collations,
forces a conversion and drops the index on one side. Matching column types across a schema
isn't pedantry — it's index insurance.
This one isn't a bug — it's the optimizer being right. If a filter matches a large
fraction of the table (say status = 'active' where 90% are active), using the
secondary index means a bookmark lookup (Lesson 0001) for almost every row — more
work than just scanning the table once. So the optimizer picks ALL on purpose.
A column with few distinct values has low selectivity; indexing it alone
rarely helps.
Recall rows × filtered from 0003: if
filtered is high (little is filtered out), the index can't save much, and a scan
wins. Use EXPLAIN ANALYZE to check the optimizer guessed the selectivity right;
if not, ANALYZE TABLE to refresh statistics.
Answer from memory — effortful recall is what builds retention. Feedback is immediate.
Why does WHERE YEAR(created_at) = 2026 skip the index on created_at?
created_at values, not YEAR(...) of them. Wrapping the column in a function makes that sorted order useless — rewrite as a bare-column range or add a functional index.Which LIKE pattern can still use a B-tree index?
'pat%') is a prefix the index can range over.For an indexed string column str_col, which filter uses the index?
The optimizer skips an index on purpose when the filter matches:
ALL is correct here.A function on an indexed column can be made seekable (8.0.13+) with:
WHERE created_at BETWEEN ? AND ? on an indexed column gives type:
BETWEEN is the sargable range form — the index seeks the low bound and scans to the high bound. That's type: range (the rung between ref and index).Reuse the people table (index name (last_name, first_name)).
Read key and type each time.
-- 1) wrapped column → key: NULL, type: ALL
EXPLAIN SELECT * FROM people WHERE UPPER(last_name) = 'JONES';
-- 2) sargable rewrite → key: name, type: ref
EXPLAIN SELECT * FROM people WHERE last_name = 'Jones';
-- 3) leading wildcard → key: NULL; right wildcard → key: name, type: range
EXPLAIN SELECT * FROM people WHERE last_name LIKE '%ones';
EXPLAIN SELECT * FROM people WHERE last_name LIKE 'Jon%';
-- 4) revive the wrapped query with a functional index
ALTER TABLE people ADD INDEX idx_upper ((UPPER(last_name)));
EXPLAIN SELECT * FROM people WHERE UPPER(last_name) = 'JONES'; -- now uses idx_upper1 scans (key: NULL). 2 seeks
(key: name, type: ref). 3: the leading-wildcard
query scans; 'Jon%' becomes a range. 4: after the
functional index, the UPPER() query finally seeks idx_upper. You
just watched all four rules — and two fixes — in the plan. Drop the extra index with
ALTER TABLE people DROP INDEX idx_upper; when done.
MySQL 8.0 Reference Manual — Comparison of B-Tree and Hash Indexes
(the LIKE rules) plus
Type Conversion in Expression Evaluation
(the string-vs-number rule). For the fix, see
CREATE INDEX — functional key parts.