MySQL EXPLAIN and Query Optimization Guide | Index Tuning, 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 = '[email protected]';

---

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 *.