The Query Was Fine at 10,000 Rows

You wrote the query in an afternoon. It ran against your development database in four milliseconds, the test suite went green, and you shipped it. Six weeks later, someone in support forwards a screenshot of a loading spinner and asks whether the site is down.

It isn't down. The same query is now scanning 1.4 million rows instead of 10,000, and the difference between those two numbers is the difference between "instant" and "the browser gave up."

Almost every developer meets this wall eventually, and almost everyone's first move is the same: search for the slow query, add an index to the column in the WHERE clause, and hope. Sometimes that works. Often it doesn't, and the reason it doesn't is that an index is not a performance setting you switch on — it's a data structure with specific rules about when it can and cannot be used. Once those rules make sense, slow queries stop feeling like bad luck.

The database is doing exactly what you told it to

Start with what happens when there is no index at all. You run this:

SELECT id, email, created_at
FROM users
WHERE email = 'sample@example.com';

The database has no shortcut for finding that email, so it does the only thing left: it reads every row in the table, one at a time, and checks whether the email matches. That's a full table scan. With 10,000 rows it's over before you notice. With 1.4 million rows on a table wide enough that only a fraction fits in memory, it becomes disk work, and disk work is where milliseconds turn into seconds.

The important part is that nothing went wrong. The query planner considered its options, found no better path, and picked the honest one. Slow queries are usually not bugs — they're the database telling you, accurately, that you never gave it a faster route.

A full table scan isn't a failure. It's the database saying it has no better option and asking you to provide one.

An index is a sorted copy, not a magic switch

The mental model that clears most of this up: an index is a second, smaller structure that keeps a copy of one or more columns kept in sorted order, with a pointer back to the full row.

Think of a 900-page reference book. Finding every mention of "latency" by reading cover to cover is a full table scan. Flipping to the index at the back, landing on "latency — pp. 88, 341, 502," and turning to three pages is an index lookup. The index at the back of the book is not the book. It's a small, ordered, redundant copy of part of the book, and it exists purely so you can skip the rest.

Real databases use a B-tree for this, which is a shallow tree that stays balanced as you insert. The practical consequence of "shallow and balanced" is that finding a value takes a handful of steps regardless of table size — going from one million to ten million rows might add a single level to the tree, not ten times the work. That's why an index doesn't just make a query faster; it changes how the query's cost grows.

Two consequences follow immediately, and they're the ones people skip:

• Because the index is sorted, it also gives you ORDER BY on those columns for free, and range conditions like BETWEEN or > cheaply.
• Because the index is a copy, every INSERT, UPDATE, and DELETE has to update it too. Indexes are not free. They're a trade: you buy read speed with write speed and disk space.

Reading EXPLAIN without the jargon

Stop guessing and ask the database what it plans to do. In MySQL, put EXPLAIN in front of the query:

EXPLAIN SELECT id, email, created_at
FROM users
WHERE email = 'sample@example.com';

You'll get a row of output with a dozen columns. You only need four of them at first.

The single most useful habit is comparing rows to the size of the result. If a query returns 12 rows but rows says 1,400,000, the database is throwing away 99.999% of the work it did. That gap is the problem, stated numerically. In PostgreSQL the equivalent is EXPLAIN ANALYZE, which runs the query and reports real timings alongside the estimates — when the estimate and the reality diverge wildly, your table statistics are stale.

The leftmost prefix rule that breaks half of your indexes

Composite indexes — indexes on more than one column — are where most confusion lives. Suppose you create this:

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

That index is sorted by customer_id first, then by status within each customer, then by created_at within each status. It behaves like a phone book sorted by last name, then first name, then middle name.

You can use a phone book to find "Kim, Jisoo." You can use it to find everyone named "Kim." You cannot use it to find everyone named "Jisoo," because the first names are scattered across the whole book. This is the leftmost prefix rule, and it decides which queries your composite index can serve:

-- Uses the index
WHERE customer_id = 42
WHERE customer_id = 42 AND status = 'shipped'
WHERE customer_id = 42 AND status = 'shipped' AND created_at > '2026-01-01'

-- Cannot use the index (no customer_id)
WHERE status = 'shipped'
WHERE created_at > '2026-01-01'

There's a second rule stacked on top: once the index hits a range condition, columns after it can no longer be used for lookup. In customer_id = 42 AND created_at > '2026-01-01' AND status = 'shipped', the range on created_at stops the index there, and status gets filtered the slow way. The fix is usually column order — put equality columns first, range columns last.

This is also why the answer to "should I add an index?" is often "no, reorder the one you have." Three separate single-column indexes on customer_id, status, and created_at are meaningfully worse than one well-ordered composite index for this query pattern, and they cost you three times the write overhead.

Four reliable ways to make an index useless

An index exists, the planner ignores it, and the query is still slow. Nearly always it's one of these.

1. You wrapped the column in a function. The index stores created_at, not DATE(created_at). The moment you transform the column, the sorted order no longer applies.

-- Index unusable
WHERE DATE(created_at) = '2026-09-01'

-- Index usable — same result, rewritten as a range
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

The same trap catches WHERE YEAR(created_at) = 2026, WHERE UPPER(email) = ..., and arithmetic like WHERE price * 1.1 > 100. Keep the indexed column bare on the left side of the comparison.

2. A leading wildcard. LIKE 'seoul%' can use an index, because the sorted order lets the database jump to entries starting with "seoul." LIKE '%seoul%' cannot, for the same reason a phone book can't find last names containing "kim." If you need that, you need full-text search, not a B-tree.

3. Type mismatch. If user_id is a VARCHAR and you compare it to a number, or you compare columns with different collations across a join, the database may quietly convert values and lose the index. This one hides well because the query still returns correct results.

4. The index isn't selective enough. An index on a status column with three possible values, where 70% of rows are 'active', is barely worth using — the planner may correctly decide a full scan is cheaper than reading the index and then jumping back to the table 900,000 times. Low-cardinality columns make poor leading index columns. They're fine as the second column in a composite index, where the first column has already narrowed things down.

When adding an index is the wrong answer

Sometimes the query plan is fine and the query is still slow. Two cases come up constantly.

The first is N+1 queries. Your page loads 50 orders with one query, then loops through them and fetches each customer individually. That's 51 round trips, each one fast, adding up to something that isn't. No index fixes this; a JOIN or a single WHERE customer_id IN (...) does.

The second is selecting more than you need. SELECT * on a table with a large TEXT column drags that column across the network for every row, even when you never render it. Naming your columns explicitly also opens the door to a covering index — one that contains every column the query touches, so the database answers entirely from the index and never reads the table at all. That's the Using index note in EXPLAIN, and it's often a bigger win than the initial index was.

And occasionally the honest answer is that the query is doing legitimate work — aggregating two years of history, say — and the fix is caching the result or precomputing a summary table, not tuning the query further.

A checklist for the next slow query

When something is slow, work in this order rather than adding indexes hopefully:

1. Run EXPLAIN (or EXPLAIN ANALYZE) and read type, key, rows, Extra.
2. Compare rows examined to rows returned. A large gap names the problem.
3. Check whether the WHERE columns are bare — no functions, no arithmetic, no leading %.
4. If a composite index exists, verify your conditions include its leftmost column, with equality before range.
5. Ask whether one query is being run 50 times in a loop.
6. Only then add an index — and afterward, re-run EXPLAIN to confirm the planner actually took it.

None of this requires deep database internals. It requires accepting that the planner is not being difficult; it's following rules, and those rules are learnable in an afternoon.

The query that was fine at 10,000 rows was never really fine. It was small enough that being wrong didn't cost anything. Growth just sent the bill.