What does EXPLAIN do in MySQL?

EXPLAIN shows the query execution plan — which indexes MySQL uses, how many rows it scans, and how it joins tables. It's the starting point for any query optimization.

What is a covering index?

A covering index includes all columns a query needs, so MySQL can answer the query from the index alone without reading the table rows. This can reduce query time by 10–100×.

When should I add an index?

Add indexes on columns used in WHERE, JOIN ON, and ORDER BY clauses. Don't add indexes on low-cardinality columns (boolean, status with 2–3 values) — they rarely help.

What is the N+1 query problem?

N+1 happens when your code runs 1 query to fetch N records, then N more queries to fetch related data for each record. Fix it with JOINs or eager loading in your ORM.

MySQL EXPLAIN and Query Optimization Guide | Index Tuning

2024년 6월 25일 · 22분 읽기 · 수정 2026년 4월 7일 Intermediate tutorial

이 글의 핵심

Slow MySQL queries are the most common backend performance bottleneck. This guide teaches you to read EXPLAIN output, design indexes correctly, and systematically eliminate slow queries.

Why Query Optimization Matters

A single unindexed query scanning 10 million rows can block your entire database. Most performance issues come from missing indexes, poor JOIN design, or queries that can’t use existing indexes.

Real-world insight: A 45-second dashboard query became 80ms after adding a composite index — no application code changed, just the index definition.

1. EXPLAIN Basics

Prefix any SELECT with EXPLAIN to see the execution plan:

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

Key columns to read:

Column	What to look for
`type`	Access method — see table below
`key`	Which index was used (`NULL` = no index)
`rows`	Estimated rows scanned
`Extra`	Additional info — watch for “Using filesort”, “Using temporary”

`type` values (best to worst)

Type	Meaning
`system` / `const`	Single row match — perfect
`eq_ref`	One row per join — good
`ref`	Non-unique index scan — acceptable
`range`	Index range scan — acceptable
`index`	Full index scan — check if avoidable
`ALL`	Full table scan — fix this

EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Shows actual execution time and rows — not just estimates.

2. 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 | 4        | o.user_id |    1 |                             |
+----+-------+--------+------+---------------+---------+---------+-----------------------------+

Problems here:

type: ALL on orders → full table scan of 1.5M rows
Using filesort → in-memory sort, expensive without index

Fix: add a composite index

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

After the index:

| o | range | idx_status_created | ... | 1823 | Using index condition |

3. 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);

-- Pointless: boolean column, only 2 values
CREATE INDEX idx_users_active ON users(is_active);  -- don't do this

Composite indexes — column order matters

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

-- Query
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Good index: most selective column first, then equality filters
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- For range + sort: equality first, range last, sort column last
-- Query: WHERE status = 'pending' ORDER BY created_at DESC
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Covering indexes

Include all columns the query needs — no table row lookup required:

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

-- Covering index: includes all columns in SELECT + WHERE
CREATE INDEX idx_covering ON orders(user_id, status, id, total, created_at);

EXPLAIN will show Using index in Extra — the query reads only the index, not the table.

4. Slow Query Log

Enable to capture queries that exceed a threshold:

-- Enable (MySQL 8.0+)
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';

Parse the slow query log:

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

# Or use pt-query-digest (more detailed)
pt-query-digest /var/log/mysql/slow.log | head -100

5. Common Anti-Patterns

Functions on indexed columns

-- BAD: function prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2026;

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

Leading wildcard LIKE

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

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

-- For full-text search: use 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 all columns, prevents 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 instead of IN / UNION

-- BAD: OR can prevent index use
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

-- GOOD
SELECT * FROM orders WHERE status IN ('pending', 'processing');

Implicit type conversion

-- BAD: email is VARCHAR, comparing to integer breaks index
SELECT * FROM users WHERE email = 12345;

-- GOOD: match types
SELECT * FROM users WHERE email = '12345@example.com';

6. JOIN Optimization

-- Always join on indexed columns
SELECT o.id, u.email
FROM orders o
JOIN users u ON o.user_id = u.id  -- user_id and users.id should both be indexed
WHERE o.status = 'pending';

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

Fix N+1 queries with 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;

7. Pagination Optimization

-- BAD: OFFSET 100000 scans 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;

Keyset pagination is O(1) regardless of page number. Use it whenever possible.

8. EXPLAIN FORMAT=JSON (detailed)

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

Shows cost estimates, filtered percentage, and nested loop details — more information than the default tabular output.

9. Index Maintenance

-- Check index usage statistics (MySQL 8.0+)
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- Rebuild fragmented index
ALTER TABLE orders ENGINE=InnoDB;  -- rebuilds all indexes

-- Check table size 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;

Optimization Checklist

□ Run EXPLAIN — is type ALL anywhere? Fix with index.
□ Check Extra — "Using filesort"? Add ORDER BY column to index.
□ Check Extra — "Using temporary"? Often GROUP BY without index.
□ Count queries per request — more than 10? Look for N+1.
□ Enable slow query log — fix everything over 100ms.
□ No functions on WHERE columns (YEAR(), LOWER(), etc.)
□ No leading wildcard LIKE — use FULLTEXT for contains search.
□ Paginating? Switch to keyset pagination.
□ SELECT only the columns you need.
□ Check sys.schema_unused_indexes — remove dead weight.

Key Takeaways

Problem	Fix
Full table scan (type: ALL)	Add index on WHERE/JOIN columns
Using filesort	Add ORDER BY column to index
Slow LIKE search	FULLTEXT index or trailing-only wildcard
N+1 queries	JOIN or ORM eager loading
Slow OFFSET pagination	Keyset (cursor) pagination
Index not used	Remove function wrapper from WHERE column
Duplicate indexes	`sys.schema_redundant_indexes` → drop redundant

Query optimization is a feedback loop: EXPLAIN → identify problem → add index or rewrite query → EXPLAIN again. Most production MySQL problems are solved by the right composite index and eliminating SELECT *.

Frequently Asked Questions (FAQ)

Q. When would I use this in practice?

A. Master MySQL query optimization with EXPLAIN.

Q. What should I read before this?

A. Follow the previous article or related articles links at the bottom of each post to learn in sequence.

Q. Where can I study this more deeply?

A. Check cppreference and the relevant library’s official documentation. The reference links at the end of the article are also worth using.

이 글이 도움이 되셨나요?

여러분의 피드백은 더 나은 콘텐츠를 만드는 데 도움이 됩니다

문제가 있거나 개선 제안이 있으시면 연락처로 알려주세요.