Indexing strategy

What it is

An index is a separate, sorted data structure that lets the database find rows without reading the whole table. Almost always a B-tree: sorted, balanced, and cheap to search — O(log n) instead of O(n).

The trade is exact: reads get faster, writes get slower, disk usage goes up. Every INSERT, UPDATE and DELETE must maintain every index on that table.

Why it bites

Two failure modes, opposite directions.

Too few: a query on an unindexed column does a sequential scan. On 10,000 rows nobody notices. On 10 million it takes 8 seconds, and because it is holding a connection, everything else queues behind it.

Too many: someone indexes every column "to be safe". Now each write touches nine B-trees, the write path is three times slower, and the planner has more ways to choose wrong.

The mental model

Think of a phone book sorted by (last name, first name).

Finding "Gupta, Ajink" is instant. Finding everyone named "Gupta" is instant. Finding everyone whose first name is "Ajink" is useless — you read the entire book. That is the leftmost prefix rule, and it is the single most valuable thing to know about composite indexes.

An index on (user_id, created_at) serves:

• WHERE user_id = 5 ✓
• WHERE user_id = 5 AND created_at > '2026-01-01' ✓
• WHERE user_id = 5 ORDER BY created_at DESC ✓ (already sorted — no sort step)
• WHERE created_at > '2026-01-01' ✗ (skips the leftmost column)

So order matters: equality columns first, then the range or sort column.

Which queries can use INDEX (user_id, created_at) — and the one that cannot.

Selectivity

An index is only worth using when it eliminates most rows. On a status column with values active/inactive and a 50/50 split, the database will often ignore the index and scan — reading the index and then fetching half the table's rows is more work than one sequential pass. Low-cardinality columns are poor index candidates on their own, but useful as the second column of a composite.

Covering indexes

If an index contains every column a query needs, the database answers from the index alone and never touches the table. In Postgres that is INCLUDE; in MySQL you add the columns to the key. This can turn a 200 ms query into a 5 ms one, and it is the biggest single win available on a hot read path.

Gotchas

• A function on the indexed column kills the index: WHERE LOWER(email) = ... cannot use an index on email. Index the expression instead, or store it normalised.
• Leading wildcards (LIKE '%foo') cannot use a B-tree. Trailing (LIKE 'foo%') can.
• Type mismatches silently disable indexes — a string parameter against an integer column forces a cast.
• Foreign key columns are not indexed automatically in most databases, and unindexed FKs make deletes on the parent table catastrophically slow.
• CREATE INDEX locks writes. Use CREATE INDEX CONCURRENTLY in Postgres on a live table.
• An index that duplicates the prefix of another ((a) when (a, b) exists) is dead weight. Drop it.

Prove you know it

Take your busiest query, run EXPLAIN ANALYZE, and find its access method. If it says Seq Scan on a large table, design the composite index for it — equality columns first — add it, and re-run. You should be able to state the expected improvement before you measure it. Next: query plans and N+1.