Reading MySQL EXPLAIN: Index Design, Statistics and Slow Query Fixes

By 0xmote · Aug 5, 2026 · 24 min read · Updated Sep 27, 2026 Intermediate tutorial

Key takeaways

Slow MySQL queries are the most common backend performance bottleneck. This guide teaches you to read EXPLAIN and EXPLAIN ANALYZE output, design indexes correctly, keep optimizer statistics honest, and work through the cases where MySQL still refuses to use the index you added.

Why Query Optimization Matters

A single unindexed query scanning millions of rows can saturate disk I/O and the buffer pool for everything else on the server. When MySQL latency spikes, the cause usually boils down to one of four things: missing or badly ordered indexes, queries written so they can’t use an existing index, poor join order, or stale optimizer statistics. None of these are fixed by guessing — they are fixed by reading the execution plan and removing the most expensive step, then reading the plan again.

This guide targets InnoDB on MySQL 8.x. The same workflow applies if you use an ORM: capture the final SQL it generates and run EXPLAIN on that, not on what you think the ORM sends.

EXPLAIN Basics

Prefix any SELECT (or UPDATE/DELETE) with EXPLAIN to see the execution plan:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

The optimizer uses table statistics and a cost model to decide which index to use, in what order to join tables, and which access method to use for each table. EXPLAIN unfolds that decision into rows you can read. Key columns:

Column	What to look for
`type`	Access method — see table below
`possible_keys`	Indexes the optimizer considered
`key`	Index actually chosen (`NULL` = no index)
`rows`	Estimated rows examined — an estimate, not a count
`filtered`	Estimated % of those rows that survive the WHERE clause
`Extra`	Additional info — watch for “Using filesort”, “Using temporary”

`type` values (best to worst)

Type	Meaning
`system` / `const`	At most one matching row via PK/unique key — effectively free
`eq_ref`	One row per row from the previous table (PK/unique join) — good
`ref`	Non-unique index lookup — usually fine
`range`	Index range scan — cost depends on how wide the range is
`index`	Full scan of an index — cheaper than `ALL` only if the index is narrow
`ALL`	Full table scan — the first suspect on any large table

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Plain EXPLAIN shows what the optimizer expects. EXPLAIN ANALYZE executes the query and prints the plan as a tree with actual time, row counts, and loop counts for each step. The gap between the two is where the interesting bugs are: a step estimated at 10 rows that actually produced 400,000 tells you the statistics are wrong, and no amount of index tweaking will help until that is fixed (see section 5). Keep in mind that it really runs the query — don’t point it at a slow DELETE on production.

Reading EXPLAIN Output

EXPLAIN SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

+----+-------+--------+---------+---------+-----------+---------+-----------------------------+
| id | table | type   | key     | key_len | ref       | rows    | Extra                       |
+----+-------+--------+---------+---------+-----------+---------+-----------------------------+
|  1 | o     | ALL    | NULL    | NULL    | NULL      | 1500000 | Using where; Using filesort |
|  1 | u     | eq_ref | PRIMARY | 8       | o.user_id |       1 | NULL                        |
+----+-------+--------+---------+---------+-----------+---------+-----------------------------+

Problems here:

type: ALL on orders → a full table scan of ~1.5M rows just to find pending orders
Using filesort → every matching row is sorted before LIMIT 20 can apply

The users side is fine: eq_ref on PRIMARY means one lookup per order.

Fix: add a composite index that serves both the filter and the sort

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

After the index:

| o | ref | idx_status_created | ... | const | 1823 | Backward index scan |

type is now ref (an equality lookup on the first index column), and rows is the estimated number of pending orders instead of the whole table.

Because rows with status = 'pending' are stored in created_at order inside the index, MySQL reads them newest-first (a backward index scan) and stops after 20 — the filesort disappears. A LIMIT query without an index that matches both the WHERE and the ORDER BY is one of the most common shapes behind slow list APIs.

Index Design

Single-column indexes

-- Good: high cardinality column in WHERE clause
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_email ON users(email);

-- Usually pointless: boolean column, only 2 values
CREATE INDEX idx_users_active ON users(is_active);

A low-cardinality index is not always useless — if only 0.1% of rows have is_active = 0 and you query for exactly those, it helps — but for evenly distributed values the optimizer will correctly decide a full scan is cheaper than bouncing between index and table.

Composite indexes — column order matters

The leftmost prefix rule: MySQL can use a composite index from the left. (a, b, c) can serve queries on a, (a, b), or (a, b, c) — but not b alone or c alone.

The practical ordering rule is equality columns first, then the range or sort column. Once MySQL hits a range condition on one column, it can’t use the columns to its right for further narrowing or ordering.

-- Query: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- Equality columns first, sort column last:
ALTER TABLE orders
  ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

-- Query: WHERE status = 'pending' ORDER BY created_at DESC
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

MySQL 8.0 supports real descending index columns (created_at DESC). It only matters for mixed directions like ORDER BY a ASC, b DESC; for a single sort column, MySQL can scan an ascending index backward.

Covering indexes

If every column a query touches lives in the index, InnoDB never has to jump from the secondary index to the clustered index to fetch the row:

-- Query
SELECT id, total, created_at FROM orders WHERE user_id = 42 AND status = 'shipped';

-- Covering index
CREATE INDEX idx_covering ON orders(user_id, status, total, created_at);

id is not listed because every InnoDB secondary index already stores the primary key as its row pointer — adding it explicitly just wastes space. EXPLAIN shows Using index in Extra when the query is answered from the index alone.

The trade-off: every index is written on every INSERT and on each UPDATE that touches its columns, and wide covering indexes compete for buffer pool memory. Build them for hot queries you have measured, not speculatively.

Slow Query Log

Enable it to capture queries that exceed a threshold:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- log queries over 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

SET GLOBAL doesn’t survive a restart — put the same settings in my.cnf once you’ve settled on values. log_queries_not_using_indexes can be very noisy on tables small enough that a full scan is correct; turn it off if it drowns out the real problems.

Parse the log:

# Summarize top offenders by total time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Or use pt-query-digest (Percona Toolkit) for fingerprinted, grouped output
pt-query-digest /var/log/mysql/slow.log | head -100

pt-query-digest groups queries by fingerprint (literals stripped), so a query that takes 50ms but runs 20,000 times an hour rises above a single 5-second report query. That ranking by total time is usually the right priority order. performance_schema.events_statements_summary_by_digest gives similar data without parsing log files.

Statistics: When the Plan Is Wrong for the Data

The optimizer’s choices are only as good as its statistics. InnoDB estimates cardinality by sampling index pages, and after a bulk load, a large delete, or a gradual shift in data distribution, those estimates can drift far from reality. Symptoms: rows in EXPLAIN is wildly different from what EXPLAIN ANALYZE measures, or a query that was fast yesterday picks a different index today with no code change.

-- Recompute index statistics for the table
ANALYZE TABLE orders;

On InnoDB, ANALYZE TABLE resamples statistics rather than rebuilding anything, so it’s fast, but schedule it in a quiet window on busy production tables. Run it after bulk loads and whenever a plan looks wrong after a deploy.

For skewed, non-indexed columns — say status where 98% of rows are 'delivered' — MySQL 8.0 histograms give the optimizer a much better selectivity estimate:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

Histograms are not maintained automatically; rebuild them periodically if the distribution changes.

Optimizer hints (FORCE INDEX, /*+ INDEX(o idx_user_status_created) */, JOIN_ORDER) exist for the cases where the optimizer is still wrong after statistics are fixed. Use them sparingly and only for measured bottlenecks: a hint freezes a decision that was right for today’s data, and it silently becomes wrong when the data changes. The default fixes should remain a better index or a better-shaped query.

Common Anti-Patterns

Functions on indexed columns

-- BAD: function on the column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2026;
SELECT * FROM orders WHERE DATE(created_at) = '2026-09-01';

-- GOOD: rewrite as a range on the raw column
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

The index stores created_at values, not YEAR(created_at) values, so MySQL has to compute the function for every row. If you genuinely need to filter on an expression, MySQL 8.0.13+ supports functional indexes: CREATE INDEX idx_email_lower ON users ((LOWER(email)));.

Leading wildcard LIKE

-- BAD: leading % can't use a B-tree index
SELECT * FROM products WHERE name LIKE '%phone%';

-- GOOD: trailing % can use the index as a range
SELECT * FROM products WHERE name LIKE 'phone%';

-- For contains-search: FULLTEXT index
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone' IN BOOLEAN MODE);

SELECT *

-- BAD: pulls every column, rules out covering indexes
SELECT * FROM orders WHERE user_id = 42;

-- GOOD: only columns you need
SELECT id, total, status FROM orders WHERE user_id = 42;

OR across different columns

-- On the same column, OR and IN are equivalent — the optimizer turns both into a range
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- The real problem: OR across different columns
SELECT * FROM orders WHERE user_id = 42 OR coupon_id = 7;

For a = ? OR b = ?, a single composite index can’t serve both branches. MySQL may use an index merge (type: index_merge, Using union(...)) if both columns have their own index, but it often decides a full scan is cheaper. If EXPLAIN shows ALL, split the query:

SELECT * FROM orders WHERE user_id = 42
UNION
SELECT * FROM orders WHERE coupon_id = 7;

Each branch now uses its own index. Use UNION ALL if duplicates are impossible or acceptable — it skips the deduplication step.

Implicit type conversion

-- BAD: phone is VARCHAR, comparing to a number converts every row's value
SELECT * FROM users WHERE phone = 5551234;

-- GOOD: match types
SELECT * FROM users WHERE phone = '5551234';

When a string column is compared to a number, MySQL converts the column value to a number for every row, so the index on phone can’t be used. The same trap appears in joins between columns of different types or collations (INT vs BIGINT is fine; VARCHAR vs INT, or utf8mb4_general_ci vs utf8mb4_0900_ai_ci, is not). This one is easy to miss because the query returns correct results — it’s just slow — and ORMs that bind every parameter as a string can trigger it the other way around on numeric columns.

JOIN Optimization

-- Join on indexed columns
SELECT o.id, u.email
FROM orders o
JOIN users u ON o.user_id = u.id  -- orders.user_id needs an index; users.id is the PK
WHERE o.status = 'pending';

-- Check join type in EXPLAIN:
-- eq_ref = good (primary/unique key join)
-- ref    = acceptable (non-unique index)
-- ALL    = bad (no usable index on the join column)

Fix N+1 queries with a JOIN:

-- N+1 (bad): 1 query + N queries
SELECT id FROM orders WHERE user_id = 42;
-- For each order: SELECT * FROM order_items WHERE order_id = ?

-- Fixed with JOIN
SELECT o.id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 42;

N+1 doesn’t show up in the slow query log, because each individual query is fast. It shows up as a request that issues hundreds of queries. Count queries per request in your application logs or APM to catch it.

Pagination Optimization

-- BAD: OFFSET 100000 reads and discards 100000 rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- GOOD: keyset (cursor) pagination
SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 20;

With keyset pagination, the cost of fetching a page doesn’t grow with page depth — the index seeks straight to last_seen_id. The trade-off is that you can’t jump to “page 5,000” directly, and the sort key must be unique (add id as a tiebreaker: ORDER BY created_at, id with WHERE (created_at, id) > (?, ?)).

EXPLAIN FORMAT=JSON and FORMAT=TREE

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42\G
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE user_id = 42\G

The JSON format shows cost estimates per step, used_key_parts (how much of a composite index was actually used — more precise than eyeballing key_len), and whether a condition was pushed down to the storage engine. FORMAT=TREE is the same tree EXPLAIN ANALYZE prints, without running the query.

Index Maintenance

-- Index usage (requires performance_schema, MySQL 8.0+)
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- Rebuild a table and all its indexes (online for InnoDB, but I/O heavy)
ALTER TABLE orders ENGINE=InnoDB;

-- Table and index size
SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length + index_length DESC;

sys.schema_unused_indexes only knows about activity since the last server restart. An index used only by a monthly report looks unused for 29 days, so check uptime before dropping anything. MySQL 8.0 lets you test the drop safely first: ALTER TABLE orders ALTER INDEX idx_x INVISIBLE; hides the index from the optimizer while still maintaining it, and you can make it visible again instantly if something slows down.

Troubleshooting

Symptom	What to check
`key` is `NULL` despite a new index	Functions on the column, implicit type/collation conversion, wrong composite order, stale statistics, low selectivity
`rows` far from `EXPLAIN ANALYZE` actuals	`ANALYZE TABLE`, histograms on skewed columns
`Using temporary`	`GROUP BY` / `DISTINCT` / `ORDER BY` on different columns — simplify or align the index with the grouping
`Using filesort` on a `LIMIT` query	Put the `ORDER BY` column last in an index that starts with the equality columns
Plan changed after a deploy with no query change	Statistics drift after data changes — re-analyze; pin with a hint only as a last resort
Fast locally, slow in production	Cold buffer pool, concurrent load and lock contention, much larger/skewed data, replication lag on read replicas

The last row explains a lot of confusion. A local database with 10,000 rows fits entirely in memory and every plan looks fast. Test on a copy with production-scale data, or at least compare EXPLAIN ANALYZE row counts between environments.

Symptom-to-fix lookup

Problem	Fix
Full table scan (type: ALL)	Add index on WHERE/JOIN columns
Using filesort	Add the ORDER BY column after the equality columns in the index
Slow LIKE search	FULLTEXT index or trailing-only wildcard
OR across columns	UNION of two indexed queries
N+1 queries	JOIN or ORM eager loading
Slow OFFSET pagination	Keyset (cursor) pagination
Index not used	Remove function wrapper / type conversion from WHERE column
Estimates wrong	`ANALYZE TABLE`, histograms
Duplicate indexes	`sys.schema_redundant_indexes` → make invisible, then drop

Treat the table as a starting point, not a verdict: after any fix, run EXPLAIN ANALYZE again and compare actual rows and time with the estimate. If the estimate and the actual row count still disagree badly, the problem is statistics rather than indexing, and adding more indexes will not help.