MySQL Slow Query Tuning with EXPLAIN

Introduction

When MySQL latency spikes, causes usually boil down to bad or missing indexes, suboptimal join order, too many full table scans, or stale statistics. MySQL EXPLAIN-based index tuning means reading the execution plan and cutting the most expensive steps, not guessing. This article targets InnoDB and MySQL 8.x: how to read EXPLAIN fields, and a repeatable loop of index changes → query rewrites → statistics refresh. The same workflow applies even if you use an ORM—inspect the final SQL.

After reading this post

• You can interpret type, key, rows, and Extra from EXPLAIN / EXPLAIN ANALYZE
• You can apply composite index column order and covering index ideas
• You can fold stats/histogram refresh and slow query collection into operations

---

Table of contents

1. Concepts
2. Hands-on implementation
3. Advanced usage
4. Performance comparison
5. Real-world cases
6. Troubleshooting
7. Conclusion

---

Concepts

What is an execution plan?

The optimizer uses statistics and a cost model to choose which index to use, join order, and access methods (range/ref/eq_ref, etc.). EXPLAIN unfolds that decision into a human-readable form.

Key EXPLAIN columns (summary)

Column	Meaning
type	Access method—ALL (full table scan) is usually the heaviest; const / eq_ref / range are often cheaper
key	Index actually chosen (NULL means no index)
rows	Estimated rows examined (smaller tends to be better; still an estimate)
Extra	Hints such as Using filesort, Using temporary, Using index
The goal of MySQL EXPLAIN index tuning is to reduce unnecessary full scans, filesorts, and temp tables, and to make the right index usable for filters.

---

Hands-on implementation

1) Sample table

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

2) Run EXPLAIN on slow candidates

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

Checklist: If type is ALL, expect a full scan. Verify key matches the index you expect, and watch Extra for Using filesort.

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

You get real loop counts and timings, which helps catch cases where estimated rows look small but runtime is high.

4) Composite index example

To satisfy WHERE user_id = ? AND status = ? and ORDER BY created_at DESC, try putting equality columns first and the sort column after:

ALTER TABLE orders
  ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

Re-run EXPLAIN ANALYZE and confirm fewer filesorts and fewer rows examined.

5) Refresh statistics

ANALYZE TABLE orders;

Run after bulk loads or when plans look wrong after deploys (pick a low-traffic window in production).

6) Covering indexes (when possible)

If all needed columns live in the index, you can reduce clustered index lookups. Check for Using index in Extra.

-- Example: if only id and user_id are needed, consider indexes like (user_id, id)
EXPLAIN
SELECT id, user_id
FROM orders
WHERE user_id = 42;

---

Advanced usage

• Histograms (8.0+): Can improve selectivity estimates for heavily skewed columns.
• Optimizer hints (INDEX(), …): Use sparingly on measured bottlenecks; default fixes are indexes and query shape.
• Slow query log + pt-query-digest (Percona Toolkit): Group recurring patterns to prioritize work.

---

Performance comparison

Access type	Rough intuition
const / eq_ref	PK/unique matches—usually very cheap
ref / range	Index range—depends on conditions and selectivity
index	Full index scan—can beat table scans but watch width
ALL	Full table scan—first suspect on large tables
Always cross-check with EXPLAIN ANALYZE; versions and data distributions differ.

---

Real-world cases

• List APIs: LIMIT without a WHERE index → full scan + filesort → fixed with a composite index.
• OR conditions: (a = ? OR b = ?) may fall back to full scan instead of index merge → consider UNION splits or schema changes.
• Functions on columns: WHERE DATE(created_at) = ? may skip indexes → rewrite to range predicates.

---

Troubleshooting

Symptom	What to check
key is NULL despite a new index	Stats, hints, type mismatch (string vs number), implicit conversion, functions
rows far from reality	ANALYZE TABLE, histograms, sampling issues
Using temporary	DISTINCT / GROUP BY / ORDER BY combinations—simplify query or align index sort order
Fast locally, slow in prod	Buffer pool warmup, concurrent load, hardware, replication lag

---

Conclusion

MySQL EXPLAIN index tuning is not a one-off plan read—it is a loop of schema change → statistics refresh → EXPLAIN ANALYZE remeasurement. Optimizer details differ from PostgreSQL, but the mindset—“read the plan and remove the expensive steps”—is the same. Pair with the slow query log for the biggest wins.

---

자주 묻는 질문 (FAQ)

Q. 이 내용을 실무에서 언제 쓰나요?

A. Read MySQL EXPLAIN output, tune with type, key, rows, and Extra; use EXPLAIN ANALYZE, composite indexes, ANALYZE TABLE, … 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.

Q. 선행으로 읽으면 좋은 글은?

A. 각 글 하단의 이전 글 또는 관련 글 링크를 따라가면 순서대로 배울 수 있습니다. C++ 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.

Q. 더 깊이 공부하려면?

A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.

---

같이 보면 좋은 글 (내부 링크)

이 주제와 연결되는 다른 글입니다.

• PostgreSQL vs MySQL 차이와 선택 가이드 | 스키마·트랜잭션·운영
• Node.js 데이터베이스 연동 | MongoDB, PostgreSQL, MySQL
• Docker Compose로 Node API·PostgreSQL·Redis 한 번에 띄우기

---

이 글에서 다루는 키워드 (관련 검색어)

MySQL, EXPLAIN, Query Optimization, Index, Performance, InnoDB, Tutorial 등으로 검색하시면 이 글이 도움이 됩니다.