MySQL Query Tuning: A Step-by-Step Workflow

Tuning is a process, not a bag of tricks. A good process stops you from “optimising” things that were never the problem.

The workflow

1. Define the problem with a number. “The orders page takes 4 seconds, it should take under 300 ms.” Without a target you do not know when to stop.

2. Find the real cause. Do not guess. Use the slow query log or sys.statement_analysis to see which query shapes take the most total time (Slow Query Log, performance_schema). In the application, check how many queries one page runs (see the N+1 problem next).

3. Reproduce it with realistic data. A copy of production (anonymised) is best. Use the same MySQL version.

4. Look at the plan. EXPLAIN for the shape, EXPLAIN ANALYZE for the reality (EXPLAIN, EXPLAIN ANALYZE).

5. Fix the biggest thing first. Usually:

a missing or unusable index,
a query that reads far more rows than it returns,
a sort or temporary table on a big set,
too many queries.

6. Change one thing at a time and measure again.

7. Check the side effects. An index that speeds up a read slows writes. Check the other queries that use the table.

8. Keep the evidence. Record the before-and-after numbers in the ticket or the pull request.

A worked example

A listing query: “the newest 20 paid orders of a customer”.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    total DECIMAL(8,2) NOT NULL,
    notes VARCHAR(200) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL
);
INSERT INTO orders (customer_id, status, total, notes, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 60000)
SELECT 1 + MOD(i * 7, 600), ELT(1 + MOD(i, 3), 'paid', 'shipped', 'cancelled'), 10 + MOD(i * 13, 900), REPEAT('n', 100), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;
ANALYZE TABLE orders;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Output

OUTPUT

+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| learn.orders | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 20 row(s)  (cost=6101 rows=20) (actual time=34.7..34.7 rows=0 loops=1)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk  (cost=6101 rows=59488) (actual time=34.7..34.7 rows=0 loops=1)
        -> Filter: ((orders.`status` = 'paid') and (orders.customer_id = 123))  (cost=6101 rows=59488) (actual time=34.7..34.7 rows=0 loops=1)
            -> Table scan on orders  (cost=6101 rows=59488) (actual time=0.225..27.9 rows=60000 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

The plan is a full table scan with a sort: 60,000 rows read to return 20.

Fix 1: an index for the filter

query.sql

SQL

1
2

CREATE INDEX idx_customer ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Output

OUTPUT

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 20 row(s)  (cost=26 rows=20) (actual time=0.438..0.438 rows=0 loops=1)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk  (cost=26 rows=100) (actual time=0.437..0.437 rows=0 loops=1)
        -> Filter: (orders.`status` = 'paid')  (cost=26 rows=100) (actual time=0.424..0.424 rows=0 loops=1)
            -> Index lookup on orders using idx_customer (customer_id=123)  (cost=26 rows=100) (actual time=0.0657..0.407 rows=100 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Better: only that customer’s rows are read (about 100), and then filtered and sorted.

Fix 2: an index that also provides the order

query.sql

SQL

1
2

CREATE INDEX idx_customer_status_created ON orders (customer_id, status, created_at);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Output

OUTPUT

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 20 row(s)  (cost=0.35 rows=1) (actual time=0.0129..0.0129 rows=0 loops=1)
    -> Index lookup on orders using idx_customer_status_created (customer_id=123, status='paid') (reverse)  (cost=0.35 rows=1) (actual time=0.0122..0.0122 rows=0 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Now MySQL walks the index backwards for this customer and status, and stops after 20 entries. No sort, and very few rows. (Compare the actual time of the three plans.)

Fix 3: select fewer columns

SELECT * also drags the notes column from the table for every row. If the page needs only a few fields, name them. With a covering index it can avoid the table entirely (Covering Indexes).

A checklist of common rewrites

Symptom	Likely fix
`type: ALL` on a big table	Add an index for the `WHERE` or the join column
`Using filesort` on a big result	An index in `ORDER BY` order, after the equality columns
`Using temporary` with `GROUP BY`	An index on the grouping columns, or filter earlier
A function on an indexed column in `WHERE`	Rewrite so the column stands alone, or a functional index
`LIKE '%text%'`	Full-text index, or a search engine
Large `OFFSET` in pagination	Keyset pagination
`SELECT *`	Select only needed columns
`COUNT(*)` on a huge table, often	A counter or summary table, or an estimate
`NOT IN (subquery)`	`NOT EXISTS`
Correlated subquery run per row	A join to a pre-aggregated derived table, or a window function
Many small queries from the application	Batch or join them (the N+1 problem)
Same expensive result, many requests	Cache it, or use a summary table
A wide `OR` over different columns	`UNION` of two indexed queries
Implicit type conversion	Pass the right type
`DISTINCT` to hide duplicate joins	Fix the join
Updating millions of rows at once	Batches

What not to do

Do not add an index for every slow query without thinking. Indexes cost writes and memory.
Do not tune without data. A 100-row dev table tells nothing.
Do not trust one run. Run each variant several times and compare the median, with a warm cache.
Do not change the server configuration first. A bad query is almost never solved by innodb_buffer_pool_size.
Do not forget the application. A fast query run 500 times per request is still slow.

After the fix

Keep a regression test or an alert for the page’s speed.
Re-run sys.statement_analysis a week later to confirm the improvement in production.
Remove temporary hints and debugging indexes.

lightbulb

Stop when you hit the target

Tuning has no natural end. Decide the goal, reach it, stop. Every extra index is a cost forever.