Field notes — the blog

Notes from inside the system.

Specific write-ups on decisions that moved the needle: architecture trade-offs, delivery pipelines, and leading engineers who are better than you at their part of it.

              {{ totalPosts }} notes
              {{ totalMinutes }} min total
              Monthly
            

/

                Latest
                {{ featured.date }}
                {{ featured.cat }}
                {{ featured.read }}
              

{{ featured.title }}

{{ featured.excerpt }}

Read the note →

// before → after
{{ row.text }}

{{ listHeading }}

{{ resultCount }}

                {{ post.cat }}
                {{ post.date }}
              

{{ post.title }}

{{ post.excerpt }}

{{ post.read }}

Nothing matches that.

Try a different term, or clear the filters.

The architect's dispatch

One note a month. Nothing else.

A single deep write-up on a system I worked on: the trade-off, the numbers, what I'd do differently.

            {{ article.cat }}
            {{ article.date }}
            {{ article.read }}
          

{{ article.title }}

{{ article.excerpt }}

{{ part.text }}{{ part.text }}{{ part.text }}

{{ b.alt }} — {{ b.caption }}

{{ b.label }}

{{ h.text }}

{{ cell.text }}

{{ b.label }}

{{ link.label }} {{ link.note }} {{ link.host }}

{{ b.caption }}

{{ b.text }}

{{ b.text }}

{{ b.label }}

{{ line.text }}

{{ item.no }}

{{ part.text }}

{{ part.text }}

{{ part.text }}

Takeaways

▖ {{ t }}

Tags

{{ tag }}

← Newer

{{ prevPost.title }}

Older →

{{ nextPost.title }}

Related notes

{{ post.cat }}

{{ post.title }}

{{ post.read }}

Make a slow MySQL query fast: a full diagnosis

Cloud & DevOps · Aug 2026 · 17 min read

One real 4.2-second listing query, taken to 40 milliseconds. Reading EXPLAIN properly, composite index order, covering indexes, and keyset pagination.

We are going to take one query from 4.2 seconds to about 40 milliseconds, and more importantly work out why each step helped. The query is a candidate listing from a hiring product: filter by employer and status, order by newest, paginate. It is the most common shape of slow query I meet, and the fixes generalise.

SELECT c.id, c.name, c.email, c.created_at, c.status
FROM candidates c
WHERE c.employer_id = 4471
  AND c.status = 'active'
ORDER BY c.created_at DESC
LIMIT 25 OFFSET 40000;

-- 11.4M rows in the table, ~180k for this employer
-- 4.2s, every time, and getting worse monthly

Step 0: measure before you touch anything

The most common failure in query tuning is fixing a query that was not the problem. Turn on the slow log with a low threshold for an hour of real traffic, aggregate it, and let the data pick the target. Nine times in ten it is not the query anyone suspected.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;      -- catch the merely mediocre
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- an hour later, rank by TOTAL time, not by worst single run:
$ pt-query-digest /var/log/mysql/slow.log | head -60

-- a 4.2s query run 200x/hour costs 14 min/hour.
-- a 40s report run once at 2am costs 40s. fix the first one.

Step 1: read EXPLAIN properly

Most people read the type column and stop. The two that actually tell you the story are rows, which is how many the optimiser expects to examine, and Extra, which is where the expensive work hides.

id  type  key                rows      Extra
1   ref   idx_employer_id    182934    Using where; Using filesort

-- three separate problems in one line:
--   rows=182934 : reading 183k rows to return 25
--   Using where : status filtered AFTER the index, in the server
--   Using filesort : the whole result set sorted, every single run

-- and for what actually happened rather than what was planned:
EXPLAIN ANALYZE SELECT ...   -- real timings, real row counts

Using filesort does not mean a file, and it does not always mean disk — but it does mean MySQL could not get the rows in the order you asked for and had to sort them itself. On 183k rows that is the bulk of your 4.2 seconds. Making the sort unnecessary is the single biggest win available here.

Step 2: the composite index, in the right order

There is one ordering rule and it is worth memorising, because it explains almost every index that fails to help: equality columns first, then the sort column, then any range. Put the sort column before an equality column and MySQL can seek to the right place but cannot use the index for ordering, so the filesort comes back.

CREATE INDEX idx_candidates_listing
  ON candidates (employer_id, status, created_at DESC);
--                 ^equality   ^equality  ^sort

id  type   key                      rows   Extra
1   range  idx_candidates_listing   40025  Using where
--                                          ^ filesort is GONE

-- 4.2s -> 0.9s. good, not done: rows is still 40k. see step 4.

Descending index support needs MySQL 8.0; on 5.7 the DESC keyword parses and is silently ignored, which is a fun afternoon. On 5.7 the index still removes the filesort for ORDER BY created_at ASC, and for DESC the optimiser can usually scan backwards — check EXPLAIN rather than assuming.

Step 3: when the index is ignored anyway

You add the index, EXPLAIN shows the old plan, and you start doubting yourself. There are five common causes and they account for nearly every case I have debugged.

A function wraps the column: WHERE DATE(created_at) = ... cannot use an index on created_at. Rewrite as a range over the day.
Type mismatch: employer_id is an INT and the parameter arrives as a string, so MySQL casts the column and abandons the index.
Collation mismatch on a join key between two tables — common after a table is restored from an older dump.
Leading wildcard: LIKE '%acme%' cannot seek. LIKE 'acme%' can. If you need the former, you need a full-text index, not a B-tree.
The optimiser thinks a scan is cheaper, because statistics are stale. Run ANALYZE TABLE before you conclude anything about the plan.

Step 4: covering the query

With the composite index, MySQL walks it to find matching rows, then goes to the clustered index for each one to fetch name and email. That second lookup is per row, and it is why 40k rows still costs 900 milliseconds. If every column the query needs lives in the index, that trip never happens.

CREATE INDEX idx_candidates_listing_cov ON candidates
  (employer_id, status, created_at DESC, name, email);
--  ^--- filter/sort ------------^  ^--- along for the ride

id  type   key                          Extra
1   range  idx_candidates_listing_cov   Using where; Using index
--                                                  ^^^^^^^^^^^
--  'Using index' = answered entirely from the index. no table reads.

-- 0.9s -> 0.35s
-- the cost: a wider index to write on every insert and update.

Covering indexes are a trade, not a free win. You are moving cost from reads to writes and to disk. On a read-heavy listing that is obviously correct; on a hot write path with a wide row it may not be. Do not add name and email to a covering index if the next ticket adds four more columns to the SELECT — at that point you want the lookup.

Step 5: the OFFSET 40000 problem

We are at 350 milliseconds and the remaining cost is structural. OFFSET does not skip rows cheaply — the database generates all 40,025 and discards 40,000 of them. Page one is fast, page 1,600 is not, and no index fixes that because the work is inherent to the operator.

-- instead of: LIMIT 25 OFFSET 40000
-- remember where the last page ended, and seek to it:

SELECT id, name, email, created_at, status FROM candidates
WHERE employer_id = 4471 AND status = 'active'
  AND (created_at, id) < ('2026-03-11 09:14:02', 883021)
ORDER BY created_at DESC, id DESC
LIMIT 25;

-- the tuple comparison is the important part: created_at is not
-- unique, so ties would skip or repeat rows across pages without id.

-- 0.35s -> 0.04s, and page 1,600 costs the same as page 1.

The honest trade-off: you lose the ability to jump to an arbitrary page number, because the cursor is the sort key of the last row rather than a count. For infinite scroll and next/previous this is strictly better. For a UI with numbered pages you either keep OFFSET for the shallow pages and cap the depth, or you change the UI. In practice, nobody has ever visited page 1,600 on purpose.

Step 6: verify, on real data

Measure on production-shaped data, with a cold buffer pool, more than once. A query that is fast the second time is telling you about your cache, not your index — and the first user after a restart gets the cold number.

SET profiling = 1;  -- or use performance_schema on 8.0

step                                    p50      rows examined
baseline                                4.20s    182,934
+ composite index (right order)         0.90s     40,025
+ covering columns                      0.35s     40,025
+ keyset pagination                     0.04s         25

-- rows examined is the number that predicts tomorrow.
-- 25 does not grow when the table does. 40,025 does.

Optimise rows examined, not elapsed milliseconds. Time tells you about today's hardware and today's cache; rows examined tells you what happens at triple the volume.

What not to do

Do not add an index per query. Five overlapping indexes on one table slow every write and confuse the optimiser; a well-ordered composite usually serves several queries at once.
Do not reach for FORCE INDEX as a fix. It is a diagnostic. In production it becomes a landmine the day the data distribution shifts and the hint is now wrong.
Do not cache your way out first. A cache in front of a query that examines 183k rows hides the problem until an invalidation storm hands you both problems at once.
Do not tune in isolation. Adding this index changed insert latency on candidates by about 8%, which was fine here and would not have been on a write-heavy table.

The general method is worth more than the specific fixes: measure to pick the target, read EXPLAIN for rows and Extra rather than type, remove the sort with index order, remove the lookup with covering columns, remove the offset with a cursor, then re-measure cold. Four of those five steps are free of application changes, which is why they are the ones to try first.

Rows examined falling from 182,934 at baseline to 40,025 with a composite index, unchanged with covering columns, and 25 with keyset pagination. — The same four steps, measured by rows examined rather than by elapsed time.

Takeaways

Rank by total time across an hour of real traffic; the query everyone suspects is usually not the expensive one.
Composite index order is equality columns, then the sort column, then ranges — the wrong order silently keeps the filesort.
A covering index removes the per-row table lookup, at the cost of slower writes and a wider index.
OFFSET cost grows with depth no matter what you index; keyset pagination with a tuple comparison makes page 1,600 as cheap as page 1.

All notes · Shehzad Aslam