Debug a Slow MySQL Query Before You Guess at Indexes

The fastest way to make MySQL worse is to add indexes because a query "feels slow."

Start with evidence. Find the query. Measure it. Read the plan. Then change one thing.

Here is the workflow I use.

1. Confirm MySQL Is Actually the Bottleneck

Do not start inside EXPLAIN. First check if the database is under obvious stress.

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
SHOW GLOBAL STATUS LIKE 'Com_select';

These numbers do not solve the problem, but they tell you where to look.

If Slow_queries is climbing during the incident, turn to the slow query log. If connections are spiking, you may have an app pooling problem. If rows read is exploding, you probably have missing selectivity or a bad access pattern.

2. Turn On the Slow Query Log

Check the current setting:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

Enable it for the running server:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

For persistent config, add this to your MySQL config:

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 0

I usually avoid turning on log_queries_not_using_indexes at first. It can create noise. A query can skip indexes and still be fine on a small table. Start with actual slow queries.

3. Summarize the Worst Queries

Use mysqldumpslow when you need a quick local read:

mysqldumpslow -s t -t 10 /var/lib/mysql/*-slow.log

Useful sort modes:

mysqldumpslow -s t -t 10 /var/lib/mysql/*-slow.log  # total query time
mysqldumpslow -s at -t 10 /var/lib/mysql/*-slow.log # average query time
mysqldumpslow -s c -t 10 /var/lib/mysql/*-slow.log  # count

I care about two classes:

• A query that runs rarely but takes forever.
• A query that is individually mediocre but runs thousands of times.

Both can hurt production.

4. Run EXPLAIN on the Real Query

Say the slow log points to this:

SELECT id, title, published_at
FROM posts
WHERE author_id = 42
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Run:

EXPLAIN
SELECT id, title, published_at
FROM posts
WHERE author_id = 42
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

The columns I check first:

5. Add the Index That Matches the Query

For the query above, this is a practical index:

CREATE INDEX idx_posts_author_status_published
ON posts (author_id, status, published_at DESC);

Why this order?

• author_id is an equality filter.
• status is another equality filter.
• published_at supports the sort after filtering.

Then run EXPLAIN again. If rows drops and the sort gets cheaper, you probably helped.

6. Do Not Keep Every Index

Indexes speed reads but slow writes and consume memory/disk. Every new index should pay rent.

Check existing indexes:

SHOW INDEX FROM posts;

If two indexes overlap, you may not need both.

Example:

INDEX (author_id)
INDEX (author_id, status, published_at)

The longer composite index can often satisfy lookups that start with author_id. Do not delete blindly, but do question duplicates.

7. The Mistakes I See Most

Adding an index on the wrong single column:

CREATE INDEX idx_posts_status ON posts (status);

If almost every row is published, this index is low value.

Using functions on indexed columns:

WHERE DATE(published_at) = '2026-06-16'

Prefer a range:

WHERE published_at >= '2026-06-16'
  AND published_at < '2026-06-17'

Selecting more columns than needed:

SELECT * FROM posts ...

Make the query say what the screen actually needs.

My Checklist

• Find the slow query from logs, not vibes.
• Reproduce the query with realistic parameters.
• Run EXPLAIN.
• Add one index that matches filters and sorting.
• Run EXPLAIN again.
• Measure the endpoint again.
• Remove indexes that do not earn their cost.

Performance work is not magic. It is a loop: observe, change, measure.