Indexing — How & When

An index is a separate, sorted data structure the database keeps so it can find rows fast — without scanning the whole table.

In simple language — it’s the index at the back of a book. Want the page about “goroutines”? We don’t read all 400 pages; we jump to the “G” section and follow the pointer.

That’s the whole idea. Without it, the database does a full table scan — reads every single row to find the ones we want.

Why it matters — and what it costs

The why is speed. On a million-row table, a WHERE email = ... with no index reads a million rows. With an index it reads maybe 3–4. That’s the difference between 800ms and 0.2ms.

But there’s no free lunch. An index is extra data the database must keep in sync. So:

• Reads get faster.
• Writes get slower — every INSERT, UPDATE, DELETE now also has to update the index.
• Disk usage grows — the index is a real structure taking real space.

Think of it like a book index — great for the reader, but every time we add a paragraph, someone has to update the index too.

How a B-tree index works

Most indexes are B-tree (balanced tree) indexes. Picture a tree of sorted values. We start at the top, and at each level we go left or right based on comparison, narrowing the range until we hit the row’s pointer.

Because the tree is sorted, B-trees are great for equality (=), ranges (>, <, BETWEEN), and ORDER BY. They’re useless for things like WHERE email LIKE '%son' — a leading wildcard can’t use the sorted order.

Composite indexes and column order

A composite index covers multiple columns, e.g. (last_name, first_name). The catch that trips everyone up is the leftmost prefix rule: the index can only be used from the left.

An index on (a, b, c) helps queries filtering on a, or a + b, or a + b + c — but not b alone, and not c alone. It’s like a phone book sorted by last name then first name: great for finding “Sharma, Asha”, useless for finding everyone named “Asha”.

So column order is a design decision. Put the column we filter on most — usually the equality column — first.

Covering index

A covering index is one that contains every column the query needs. When that happens, the database answers straight from the index and never touches the table at all — an “index-only scan”. Fastest thing going.

-- if we index (user_id, status), this query is fully covered:
-- it only needs user_id (filter) and status (returned)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
SELECT status FROM orders WHERE user_id = 42;

When NOT to index

Indexes aren’t free, so don’t sprinkle them everywhere:

• Low-cardinality columns — a gender or is_active boolean has few distinct values; the index barely narrows anything.
• Small tables — a full scan is already instant.
• Write-heavy tables with rarely-queried columns — we’d just pay the write tax for nothing.
• Columns we never filter, join, or sort on.

Reading EXPLAIN

EXPLAIN shows the plan the database will use. This is how we prove an index is actually being hit.

EXPLAIN SELECT * FROM users WHERE email = 'asha@x.com';

-- Bad  → Seq Scan on users   (full scan, no index used)
-- Good → Index Scan using idx_users_email on users

If we see Seq Scan on a big table where we expected an index, something’s off — wrong column order, a function wrapping the column, or a type mismatch.

Interview soundbite

An index is a sorted side-structure (usually a B-tree) that turns O(n) scans into O(log n) lookups — faster reads at the cost of slower writes and more disk. Composite indexes obey the leftmost-prefix rule, so column order matters; a covering index answers the query without touching the table. Don’t index low-cardinality or tiny tables, and always confirm with EXPLAIN that the plan says Index Scan, not Seq Scan.