MySQL Index Failure Causes: EXPLAIN Analysis & Real-World Fixes

EXPLAIN Output Columns for Index Analysis

When you prefix a query with EXPLAIN, MySQL returns over a dozen columns. For daily troubleshooting, focus on two groups: columns that indicate whether an index is used (type, key, key_len) and columns that show how deeply the index is used (rows, Extra). Scan type first to see if an index is used, then check key_len to determine how many columns of a composite index are actually used.

The type column shows the access method, ordered from fastest to slowest: system (single row), const and eq_ref (single-row hits), ref and range (index still working), index and ALL (full index or table scan—treat as problems). Note that this order reflects access efficiency, not actual latency; a range scan of 300,000 rows can be slower than an ALL scan of 3,000 rows.

key is the index the optimizer finally chose; possible_keys lists candidates it considered. A mismatch is common—the reason appears in later columns. key_len shows the actual bytes of the index used; for composite indexes it tells exactly how many columns are used, which is more reliable than guessing.

rows is the estimated rows to scan, not actual. EXPLAIN ANALYZE (MySQL 8.0.18+) runs the query and returns actual rows, but use cautiously in production as it executes the full query. filtered is the percentage of rows remaining after index filtering; rows × filtered equals rows passed to the next step. A low filtered value means the index filters well, but rows may still be large.

Extra provides direct clues about data retrieval and overhead. Key values:

Using index : covering index, no table lookup needed.

Using index condition : Index Condition Pushdown (ICP) active; conditions filtered at storage engine layer.

Using where : rows retrieved then filtered at Server layer.

Using filesort : index cannot satisfy ORDER BY; extra sort required.

Using temporary : temporary table used, common with GROUP BY.

When Using where and Using index condition appear together, some conditions were filtered at index layer and the rest at Server layer—this is normal. The real red flags are filesort and temporary on large tables.

String Column Compared to Numeric Constant

The opening case: order_no is varchar(32) with index idx_order_no, but the query uses a numeric literal without quotes.

-- order_no is varchar(32), right side is numeric literal
EXPLAIN SELECT * FROM t_order WHERE order_no = 202610270001;

type      ALL
possible  idx_order_no
key       NULL
key_len   NULL
rows      311842
filtered  10.00
Extra     Using where

possible_keys shows idx_order_no but key is NULL—the index was considered then discarded. MySQL manual states: when a string column is compared to a number, the index cannot be used for fast lookup because many strings (e.g., '1', ' 1', '1a') cast to the same double value. The B+ tree is ordered by original string values, not by converted numbers, so MySQL casts each row's column to double and compares—equivalent to applying a function on the column.

Adding quotes changes the plan dramatically: same index, key_len becomes 130, rows drops from 311,842 to 1.

EXPLAIN SELECT * FROM t_order WHERE order_no = '202610270001';

type      const
key       idx_order_no
key_len   130
rows      1
filtered  100.00
Extra     NULL

key_len = 130: 32 characters × 4 bytes (utf8mb4) = 128, plus 2 bytes for variable-length column length prefix. Since order_no is NOT NULL, no extra 1-byte null flag—often overlooked when calculating key_len manually.

Conversion direction is asymmetric and often reversed. When a numeric column is compared to a string constant, the constant is converted, the column is untouched, and the index remains usable.

-- user_id is bigint, right side is string constant, index still works
EXPLAIN SELECT * FROM t_order WHERE user_id = '8801';

This query shows type=ref, key_len=8—normal. The rule: whichever side is a string gets converted to double; if that string is the column, the index is lost. Java implicit parameter binding often triggers this: a Mapper method declares String but caller passes Long, so the prepared statement binds as numeric. Another source: JSON deserialization produces numeric fields that are directly used in varchar conditions. These bugs are invisible in code and only surface via EXPLAIN on slow queries.

Functions and Expressions on Indexed Columns

DATE(created_at) = '2026-10-20'

is readable but forces a full table scan.

-- Index column wrapped in function, B+ tree order unusable
EXPLAIN SELECT * FROM t_order WHERE DATE(created_at) = '2026-10-20';

type      ALL
key       NULL
rows      311842
Extra     Using where

Rewrite as a half-open interval with the bare column on the left side to restore index usage.

EXPLAIN SELECT * FROM t_order
WHERE created_at >= '2026-10-20 00:00:00'
AND created_at < '2026-10-21 00:00:00';

type      range
key       idx_created
key_len   7
rows      1284
Extra     Using index condition

Same pattern applies to YEAR(created_at), SUBSTRING(order_no,1,8), CAST(amount AS SIGNED), and arithmetic like amount * 100 > 5000. Move computation to the constant side: amount > 50 is mathematically equivalent but allows index use.

Date range endpoints need care. If created_at is DATETIME(3), writing < '2026-10-21' excludes 2026-10-21 00:00:00.000 but leaves a 1-millisecond gap before midnight. Use full timestamps on both ends to avoid missing data.

Character set conversion can also kill indexes. Joining a utf8 table to a utf8mb4 table forces conversion of the utf8 side to utf8mb4. If the converted column is the driving table's join column, its index becomes unusable.

-- t_channel is utf8, t_order is utf8mb4
-- Join column loses index order after conversion
SELECT o.id, c.name
FROM t_order o
JOIN t_channel c ON c.channel_code = o.channel_code;

The driven table's type degrades to ALL or index, Extra shows Using join buffer. Fix: unify character sets or explicitly specify COLLATE on join columns in DDL. Changing charset requires table rebuild—calculate lock time before deploying.

Composite Index Column Order and Range Conditions

Single-column indexes are straightforward; composite indexes require key_len to verify usage. Assume index idx_user_created (user_id, created_at), both NOT NULL: user_id bigint (8 bytes), created_at DATETIME(3) (7 bytes). Full two-column key_len = 15.

-- Only first column used, key_len = 8
EXPLAIN SELECT * FROM t_order WHERE user_id = 8801;

-- Skip first column, composite index unusable
EXPLAIN SELECT * FROM t_order WHERE created_at >= '2026-10-01';

The second query shows type=ALL, key=NULL. It might be served by another index (idx_created), but idx_user_created cannot be used because the leftmost column is missing—B+ tree cannot locate a starting point without the first column.

When both columns participate, order matters. Equality first, range second: both columns used for positioning (key_len=15). Range first, equality second: only first column used for positioning (key_len=8), second column filtered via ICP (Using index condition).

-- Equality then range, both columns position, key_len 15
EXPLAIN SELECT * FROM t_order
WHERE user_id = 8801
AND created_at >= '2026-10-01';

-- Range then equality, second column only ICP filtered
EXPLAIN SELECT * FROM t_order
WHERE user_id > 8800
AND created_at = '2026-10-15 10:00:00';

The second query's key_len stays 8; created_at not counted in key_len. Extra shows Using index condition—ICP filters at storage engine, reducing table lookups but not narrowing the index scan range. These two effects are often conflated.

The saying "columns after a range condition are completely invalid" is imprecise: they lose positioning ability. In the B+ tree, created_at values within user_id > 8800 are segmented and globally unordered, so no binary search within the range. But the values still reside in index pages, enabling on-the-spot filtering and saving random I/O for table lookups.

At scale this matters: 300,000 rows, user_id > 8800 hits 20,000 rows; if created_at filters to 200, ICP saves ~19,800 table lookups. At 0.1 ms per lookup, that's ~2 seconds saved.

Index design rule: put equality columns first, range columns last to maximize key_len. But cardinality remains paramount—a low-cardinality status column (3 values) placed leftmost with a high-cardinality column after it performs far worse than the reverse, a difference key_len alone won't reveal; you must measure actual table lookups.

Optimizer Cost-Based Index Rejection

key=NULL doesn't always mean index failure; sometimes the optimizer chooses a full scan after cost calculation. Example: status has only 0,1,2 with 92% rows having status=1. Using the index would require 280,000 table lookups (random I/O), while a full table scan reads sequentially—optimizer picks the latter.

-- status low cardinality, lookup cost exceeds full scan
EXPLAIN SELECT * FROM t_order WHERE status = 1;

type=ALL. But if the query only needs the primary key or all selected columns are in the index (covering index), the plan changes:

-- Covering index avoids lookups, index scan cheaper than full scan
EXPLAIN SELECT COUNT(*) FROM t_order WHERE status = 1;

type=index, Extra=Using index. Same table, same filter, different SELECT list changes the plan—so index effectiveness cannot be judged without considering the SELECT columns.

OR conditions: if both sides have indexes, optimizer may use index_merge; if one side lacks an index, the whole condition degrades to full scan.

-- remark has no index, user_id index dragged down
EXPLAIN SELECT * FROM t_order WHERE user_id = 8801 OR remark LIKE '%urgent%';

Leading-wildcard LIKE ('%20261027') also forces full scan because B+ tree cannot locate by suffix. Such needs belong in Elasticsearch, or add a reversed string column for prefix matching.

!= and NOT IN similarly force full scans (exclude a value). Rewrite as two range conditions ( > and < ) when possible.

End-to-End Slow Query Troubleshooting

Alert: order export API times out. Internal query is a paginated query with filter parameters from UI.

Slow log shows Query_time=3.82s, Rows_examined=311,842, Rows_sent=20—scan/return ratio 4 orders of magnitude, index not working.

# Query_time: 3.823104  Lock_time: 0.000087  Rows_sent: 20  Rows_examined: 311842
SET timestamp=1793088000;
SELECT id, order_no, amount, status, created_at
FROM t_order
WHERE channel_code = 'ALIPAY' AND status IN (1, 2)
ORDER BY created_at DESC
LIMIT 0, 20;

EXPLAIN: key=NULL, Extra=Using where; Using filesort. Candidate index idx_channel_status (channel_code, status) exists in possible_keys but not chosen.

type      ALL
key       NULL
rows      311842
Extra     Using where; Using filesort

Root cause: status has low cardinality; channel_code='ALIPAY' matches 60% of rows. Using the index would require ~180,000 table lookups—optimizer estimates full scan + sort cheaper. EXPLAIN ANALYZE confirms actual time matches estimate, ruling out stale statistics.

Rewrite must address the business need: "orders for a channel with given statuses, sorted by time descending, top 20." The sort column created_at is not in the index, causing filesort.

-- Add sort column to index so index itself satisfies ORDER BY
ALTER TABLE t_order ADD INDEX idx_channel_created (channel_code, created_at);

-- status becomes post-lookup filter; time lower bound narrows scan range
SELECT id, order_no, amount, status, created_at
FROM t_order
WHERE channel_code = 'ALIPAY'
AND created_at >= '2026-09-27 00:00:00'
ORDER BY created_at DESC
LIMIT 0, 20;

type      range
key       idx_channel_created
key_len   55
rows      4218
Extra     Using index condition

key_len=55 breakdown: channel_code varchar(16) → 16×4=64? Wait, the article says 16×4+2+7=55? Actually: channel_code varchar(16) utf8mb4 max 16×4=64 bytes, but key_len shows 55. The article explains: 16×4=64? Let's recalc: The article says key_len 55 = 16×4 + 2 + 7? That would be 64+2+7=73. Hmm, maybe channel_code is varchar(16) but actual stored length? The article states: key_len 55 = 16×4 + 2 + 7? Wait, it says: "key_len 55 的构成是 16 乘 4 加 2 再加 7，channel_code 是 varchar(16)，变长前缀占 2 字节，created_at 是 DATETIME(3)，占 7 字节。" That's 16*4=64, +2=66, +7=73. But key_len is 55. There's a discrepancy. However, we must faithfully reflect the article's explanation. The article says key_len 55 is composed of 16*4 + 2 + 7. That's what the author claims. We'll include that explanation as written.

The key_len of 55 indicates both columns fully used for positioning. The added time lower bound not only enables the index but also gives the export API a necessary time window—otherwise LIMIT cannot save the sort when user selects "all time."

Verification: same parameters, Rows_examined drops from 311,842 to 4,180, Query_time from 3.82s to 0.09s, type=range, filesort gone. Side effect: new index adds write overhead (maintaining another B+ tree). Order table writes 200k rows/day; write amplification hits redo log and dirty page flushing. Before adding, audit usage of old idx_channel_status—merge if possible, don't just add without removing.