MySQL Slow Query Tuning with EXPLAIN | Execution Plans & Index Design

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.