A query that returns instantly on 500 rows can take thirty seconds on five million. Nothing about the SQL changed — what changed is that the database now has to look at every row to answer you. Understanding when that happens, and how to stop it, is the difference between writing SQL and being useful with it.

What an index actually is

An index is a sorted structure (usually a B-tree) mapping column values to row locations. It's the index at the back of a textbook: instead of reading all 800 pages to find "transactions", you check the sorted list and jump straight to page 412.

Without an index, a filter like WHERE email = 'x@y.com' forces a full table scan — every row read and compared. With one, the engine walks the tree in a handful of steps. On a large table that's the difference between seconds and microseconds.

Why indexes aren't free

Writes get slower. Every INSERT, UPDATE and DELETE must also maintain each index on the table. Ten indexes means ten extra structures to update per write.
They take space, sometimes a large fraction of the table itself.
They can be ignored. If a query would match most of the table anyway, scanning is genuinely cheaper than jumping through an index, and the planner will do just that.

Which is why "add an index to every column" is not a strategy. Index the columns you actually filter, join and sort on.

The mistakes that silently disable an index

1. Wrapping the indexed column in a function

-- Index on created_at is unusable here
WHERE YEAR(created_at) = 2026

-- Rewritten so the index can be used
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

The index stores created_at, not YEAR(created_at). The same applies to UPPER(name), CAST(id AS TEXT) and arithmetic on a column: keep the indexed column bare on one side of the comparison.

2. Leading wildcards

LIKE 'raj%' can use an index — the sorted structure knows where "raj" begins. LIKE '%raj%' cannot, because a match could start anywhere. That's what full-text search exists for.

3. Getting composite index order wrong

An index on (department_id, hired_on) helps queries filtering on department_id, or on both. It does not help a query filtering only on hired_on — the left-most column has to be involved. Think of it as sorted by the first column, then the second within it.

4. SELECT * out of habit

Fetching columns you don't need means more I/O and often prevents a covering index — one that contains every column the query needs, letting the engine answer entirely from the index without touching the table at all.

Read the query plan instead of guessing

Every engine will tell you what it intends to do: EXPLAIN (or EXPLAIN ANALYZE, EXPLAIN QUERY PLAN depending on the database). You're looking for three things:

A full/sequential scan on a large table where you expected an index lookup.
Estimated vs actual row counts that differ wildly — usually stale statistics.
The join order and join type: a nested-loop join over a big unindexed table is a common disaster.

Never optimise from intuition. Read the plan, change one thing, read the plan again. Two "obvious" improvements applied at once teach you nothing about which one worked.

The rewrites that fix most slow queries

Filter before you join. Cut the big table down in a CTE or subquery, then join the smaller result — instead of joining everything and filtering at the end.
Aggregate the many-side first. For one-to-many joins, aggregate in a subquery and join the summary. This also avoids the double-counting trap from the joins article.
Prefer EXISTS to IN on large subqueries, and never use NOT IN against a column that can be NULL — it returns nothing at all.
Use UNION ALL unless you genuinely need duplicates removed; UNION pays for a sort.
Paginate with a keyset (WHERE id > :last_id ORDER BY id LIMIT 20) rather than OFFSET 100000, which makes the engine walk and discard 100,000 rows.

Where to start on a query that's slow right now

Run EXPLAIN. Find the biggest scan.
Check whether the columns in WHERE and JOIN ... ON are indexed — and whether any of them are wrapped in a function.
Reduce the number of rows as early in the query as you can.
Only then consider adding an index, and re-check the plan afterwards.

This is also a favourite interview area precisely because it can't be memorised — see the SQL questions freshers get. Build the intuition by writing queries against real tables and watching what changes: you can do that in a browser tab, no database install required.

Why Your SQL Query Is Slow: Indexes and Optimization Basics