The Query Was Fine Until the Table Hit a Million Rows

Your app was fast for a year. Nothing changed — same code, same server, same query you wrote in an afternoon and never thought about again. Then one Tuesday the orders page takes eleven seconds to load, support tickets start arriving, and you're staring at a SELECT statement that looks completely reasonable.

Nothing broke. The table just got big.

This is the most common performance story in software, and it has a boring, learnable fix. Most slow queries are slow for one of about five reasons, and you can diagnose which one in under a minute if you know where to look. Here's the whole loop: measure, read the plan, fix the index, verify.

The table didn't get slower — it got bigger

A database with 5,000 rows will forgive almost anything. Scanning every row to find three of them takes a millisecond, so a missing index is invisible. At 5 million rows, that same full scan is reading gigabytes off disk and the query planner has no shortcut to offer.

The math is unforgiving. A full table scan costs time proportional to the number of rows — double the table, double the work. A well-indexed lookup uses a B-tree, which costs time proportional to the logarithm of the row count. Going from 1,000 rows to 1,000,000 rows multiplies scan work by 1,000, but adds only about two extra levels to the tree.

Indexes don't make queries fast. They make queries stop scaling with the size of your table — which, after a year in production, is the same thing.

That's why the failure feels sudden. There's no gradual slowdown you'd notice in a dashboard; there's a threshold where the working set stops fitting in memory and every query starts hitting disk. One week it's 400ms, the next it's 11 seconds.

Step one: get the actual plan, not a guess

Before you change anything, ask the database what it's doing. In MySQL, EXPLAIN shows the plan the optimizer intends to use. Since MySQL 8.0.18, EXPLAIN ANALYZE goes further — it actually runs the query and reports real timings and real row counts next to the estimates.

EXPLAIN ANALYZE
SELECT id, total_amount, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;

Two things in the output matter more than everything else:

The access type. If you see ALL, the engine is reading every row in the table. ref or range means it's using an index. const or eq_ref means it found the row directly. Anything that says ALL on a table with more than a few thousand rows is your problem, full stop.

The gap between estimated and actual rows. EXPLAIN ANALYZE prints both. If the optimizer expected 50 rows and got 300,000, its statistics are stale and it picked a bad plan based on bad information. Run ANALYZE TABLE orders; and check again before you go index-hunting.

The other giveaway is Using filesort or Using temporary in the Extra column. Those mean the engine had to materialize an intermediate result and sort it in memory (or worse, on disk) because no index could deliver rows in the order you asked for.

Step two: build the index in the right order

Here's where most people lose. They see a slow query filtering on two columns, add two separate single-column indexes, and get almost nothing. The engine can usually only use one index per table per query, so you've created two half-solutions.

What you want is a composite index — one index across multiple columns. And composite indexes obey the leftmost prefix rule: an index on (a, b, c) can serve a query filtering on a, or on a and b, or on all three. It cannot serve a query that filters only on b, because the index is sorted by a first. Think of a phone book sorted by last name then first name: useless for finding everyone named "Maria."

A reliable ordering heuristic for the columns:

1. Equality filters first — columns compared with =
2. Sort columns next — whatever's in your ORDER BY
3. Range filters last — >, <, BETWEEN, LIKE 'abc%'

For the query above, that gives:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at);

Now the engine walks straight to the (4821, 'shipped') section of the index, and because created_at is the next column, those rows are already in date order. The LIMIT 20 reads twenty entries and stops. No sort, no scan.

One more trick worth knowing: if the index also contains every column you're selecting, the engine never touches the table at all. That's a covering index, and it's often another 2–5x on top. In the example, adding total_amount to the index would make it covering — at the cost of a slightly bigger index.

Step three: stop disabling your own indexes

You can have a perfect index and still get a full scan, because of how the query is written. These four patterns are the usual suspects.

Wrapping the column in a function. The index stores the raw value, not the transformed one:

-- Index on created_at is ignored
WHERE DATE(created_at) = '2026-08-22'

-- Index is used
WHERE created_at >= '2026-08-22 00:00:00'
  AND created_at <  '2026-08-23 00:00:00'

Leading wildcards. LIKE '%smith' cannot use a B-tree, for the same reason you can't find a name in a phone book by its last four letters. LIKE 'smith%' is fine. If you truly need leading-wildcard search, you need a full-text index or a search engine, not a bigger box.

Type mismatches. If user_code is a VARCHAR and you query WHERE user_code = 12345, the database silently converts the column on every row and skips the index. Quote it.

OR across different columns. WHERE a = 1 OR b = 2 often can't use a composite index at all. Two SELECTs joined by UNION ALL are frequently faster and always more predictable.

If your query runs a function on an indexed column, you didn't write a filter — you wrote a full table scan with extra steps.

Step four: verify, then watch the cost

Re-run EXPLAIN ANALYZE. You're looking for the access type to move off ALL, the Using filesort note to disappear, and actual row counts to drop from table size to result size. A query that went from examining 2 million rows to examining 40 is the win; the wall-clock number will follow.

Then be honest about the trade. Indexes are not free:

• Every INSERT, UPDATE, and DELETE must update every affected index. A table with nine indexes writes roughly nine times the bookkeeping.
• Indexes consume disk and, more importantly, memory. Index pages compete with data pages for buffer pool space.
• Redundant indexes are pure cost. If you have (customer_id, status) and you add (customer_id, status, created_at), the older one is now covered by the prefix of the new one — drop it.

MySQL 8 has a genuinely useful safety valve here. You can mark an index invisible, which keeps it maintained but hides it from the optimizer, so you can test a removal without actually dropping it:

ALTER TABLE orders ALTER INDEX idx_old_customer INVISIBLE;
-- watch production for a day, then either:
ALTER TABLE orders ALTER INDEX idx_old_customer VISIBLE;   -- roll back
DROP INDEX idx_old_customer ON orders;                     -- commit

Do the index work on a copy of production data if you can. Ten thousand seeded test rows will tell you nothing — the optimizer makes different choices at different table sizes, and a plan that looks great on your laptop can invert completely at scale.

Catch it before the support tickets

The best version of this story is the one where you never get the eleven-second page. Turn on the slow query log with a low threshold and read it once a week:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;   -- log anything over 500ms

Half a second is aggressive on purpose. You want to see queries while they're merely sluggish, not after they've become outages. Pair it with a periodic check of sys.schema_unused_indexes to find indexes you're paying to maintain and never reading.

And when you write a new query against a table you expect to grow, spend the thirty seconds to run EXPLAIN on it right then. The cost of thinking about the index at write time is near zero. The cost of thinking about it during an incident is a Tuesday.

The short version

Slow queries usually aren't a mystery, and they're rarely a hardware problem. Run EXPLAIN ANALYZE to see what the engine is actually doing. If the access type is ALL, you're missing an index. Build a composite index ordered equality → sort → range, and respect the leftmost prefix rule. Check that your WHERE clause isn't wrapping columns in functions or leading with wildcards. Verify with the plan, not with vibes. Then prune what you don't use.

None of this requires deep database expertise — just a habit of asking the engine what it's doing before you guess. Your future self, the one who isn't debugging at 9pm, will appreciate it.