Indexes and reading EXPLAIN
Composite column order, covering indexes, and how to turn a query plan into a concrete fix instead of a guess.
Composite indexes and the left prefix
A B-tree index is a sorted structure, so it is only useful from the left. An index on (customer_id, created_at) can seek by customer_id and then scan a date range inside it, but it cannot answer a query that filters only on created_at.
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_items_product ON order_items (product_id); -- foreign keys
-- uses the index: leading column is present
SELECT id, total FROM orders WHERE customer_id = 42 AND created_at >= '2026-01-01';
-- cannot seek: leading column missing
SELECT id FROM orders WHERE created_at >= '2026-01-01';| Predicate | Uses (customer_id, created_at)? |
|---|---|
WHERE customer_id = 42 | Yes — prefix seek |
WHERE customer_id = 42 AND created_at > x | Yes — both columns |
WHERE created_at > x | No — needs its own index |
ORDER BY customer_id, created_at | Yes — index order supplies the sort |
Reading a plan
EXPLAIN SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
EXPLAIN FORMAT=JSON
SELECT id FROM orders WHERE customer_id = 42; -- cost detail
EXPLAIN ANALYZE -- 8.0.18+: actually runs it
SELECT id FROM orders WHERE customer_id = 42;| Column | What to look at |
|---|---|
type | const > eq_ref > ref > range > index > ALL; ALL on a large table is the red flag |
key | Which index was chosen — NULL means none |
rows | Estimated rows examined; a huge number with a small result is a sign of a weak predicate |
filtered | Percentage of examined rows kept after the condition |
Extra | Using index is a covering index; Using filesort and Using temporary signal sorting or grouping work |
⚠️
Wrapping an indexed column in a function disables the index:
WHERE DATE(created_at) = '2026-01-01' cannot seek. Write the range instead — created_at >= '2026-01-01' AND created_at < '2026-01-02'.Practical habits
- Index foreign keys explicitly — the automatic index MySQL creates for a constraint may not have the column order your joins want.
- Put equality columns first and range or sort columns after them.
- Call
ANALYZE TABLE ordersafter a large data change so the optimiser's statistics are not stale. - Prefer a covering index — one that contains every column in the query — when the same hot query runs constantly.
- Drop unused indexes: each one costs write time and buffer pool space.
performance_schema.table_io_waits_summary_by_index_usageshows what is never touched.
FAQ
Why does the optimiser ignore my index?
Usually low selectivity: if the filter matches a large share of the table, a scan is genuinely cheaper. Also check for a function on the column, a type mismatch such as comparing a text column to a number, or a leading
LIKE '%term' pattern.Is more than one index per query ever used?
MySQL can merge indexes or use index intersection, but rarely as well as one well-ordered composite index. Design the composite index for the query instead of hoping the optimiser combines two.
Related
Installation, databases, users and engines Replication, backup and operational pitfalls
Last refreshed 2026-09-18.