DEV Community

Cover image for Debug a Slow MySQL Query Before You Guess at Indexes
Mark Yu
Mark Yu

Posted on • Edited on

Debug a Slow MySQL Query Before You Guess at Indexes

#mysql #database #backend #performance

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';
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

Enable it for the running server:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
Enter fullscreen mode Exit fullscreen mode

For persistent config, add this to your MySQL config:

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 0
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Run:

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

The columns I check first:

Column What I look for
type ALL means full scan. Not always bad, but suspicious on large tables.
possible_keys Indexes MySQL could use.
key The index MySQL actually used.
rows Estimated rows scanned.
Extra Watch for Using filesort and Using temporary.

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

If two indexes overlap, you may not need both.

Example:

INDEX (author_id)
INDEX (author_id, status, published_at)
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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

Using functions on indexed columns:

WHERE DATE(published_at) = '2026-06-16'
Enter fullscreen mode Exit fullscreen mode

Prefer a range:

WHERE published_at >= '2026-06-16'
  AND published_at < '2026-06-17'
Enter fullscreen mode Exit fullscreen mode

Selecting more columns than needed:

SELECT * FROM posts ...
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
bernert profile image
BernerT •

This is a fantastic guide on MySQL performance monitoring and query analysis! Could you elaborate more on the difference between the SHOW PROFILE and the EXPLAIN command for understanding query costs?

Collapse
 
markyu profile image
Mark Yu • • Edited

SHOW PROFILE

Purpose: SHOW PROFILE provides a detailed breakdown of the execution time for a query that has already been executed. It helps diagnose where time is being spent in the various stages of query execution.

Details Provided: SHOW PROFILE returns a step-by-step account of the execution stages of a query, including:

  • Starting: Initial setup before the query execution.
  • Checking permissions: Verifying user permissions for the query.
  • Opening tables: Opening the necessary tables for the query.
  • System lock: Acquiring any necessary locks on the tables.
  • Optimizing: Query optimization phase.
  • Statistics: Gathering statistics about tables and indexes.
  • Preparing: Preparing the execution plan.
  • Executing: Actual execution of the query.
  • Sending data: Retrieving the data and sending it to the client.
  • Cleaning up: Cleaning up and releasing resources post-execution.

Use Cases:

  • Diagnose Performance Issues: Identify which part of the query execution is taking the most time.
  • Fine-tuning: Focus on specific stages that might be optimized further (e.g., optimizing index usage if a lot of time is spent on table scans).

Example Usage:

SET profiling = 1;
SELECT * FROM my_table WHERE id = 123;
SHOW PROFILE FOR QUERY 1;
Enter fullscreen mode Exit fullscreen mode

Output Example:

+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000027 |
| checking query cache for query| 0.000005|
| Opening tables               | 0.000021 |
| System lock                  | 0.000003 |
| Table lock                   | 0.000009 |
| init                         | 0.000017 |
| optimizing                   | 0.000006 |
| statistics                   | 0.000020 |
| preparing                    | 0.000007 |
| executing                    | 0.000003 |
| Sending data                 | 0.000086 |
| end                          | 0.000002 |
| query end                    | 0.000002 |
| closing tables               | 0.000004 |
| freeing items                | 0.000011 |
| cleaning up                  | 0.000003 |
+------------------------------+----------+
Enter fullscreen mode Exit fullscreen mode

EXPLAIN

Purpose: EXPLAIN analyzes and predicts the execution plan for a query before it is executed. It helps understand how MySQL intends to execute the query, which can guide optimizations before running the query.

Details Provided: EXPLAIN returns a detailed plan of how the query will be executed, including:

  • id: The sequence identifier of the query.
  • select_type: The type of SELECT query (e.g., SIMPLE, PRIMARY, SUBQUERY).
  • table: The table name involved in the query.
  • type: The join type, indicating how tables are accessed (e.g., ALL, index, range).
  • possible_keys: Possible indexes that might be used.
  • key: The actual index used.
  • key_len: The length of the index used.
  • ref: The columns or constants compared to the index.
  • rows: The estimated number of rows to be read.
  • filtered: The estimated percentage of rows filtered by the query conditions.
  • Extra: Additional information about the query execution (e.g., Using index, Using temporary).

Use Cases:

  • Optimize Query Performance: Understand the intended execution path and identify inefficiencies (e.g., full table scans that might benefit from indexing).
  • Index Optimization: Ensure the query uses the most efficient indexes available.

Example Usage:

EXPLAIN SELECT * FROM my_table WHERE id = 123;
Enter fullscreen mode Exit fullscreen mode

Output Example:

+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | my_table | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------+-------------+
Enter fullscreen mode Exit fullscreen mode

Key Differences

Execution Timing:

  • SHOW PROFILE: Provides details after a query has been executed.
  • EXPLAIN: Predicts execution details before the query is executed.

Depth of Information:

  • SHOW PROFILE: Focuses on the time spent in each stage of query execution, useful for diagnosing performance bottlenecks post-execution.
  • EXPLAIN: Provides an overview of how MySQL plans to execute the query, including join types, index usage, and row estimates.

Use Case Scenarios:

  • SHOW PROFILE: Ideal for post-execution analysis to identify which parts of the query are slow.
  • EXPLAIN: Best for pre-execution analysis to understand and optimize the query execution plan.