Database indexing for system design interviews, explained
Almost every system design interview reaches a moment where a query is slow and someone says "add an index" — and that is where the real question starts. Database indexing is not one idea; it is a set of deliberate choices about which columns to index, what kind of index to build, and what you pay on every write for the read you speed up. An interviewer can tell in thirty seconds whether you have a vocabulary for those choices or whether "add an index" is where your thinking stops. An index trades extra storage and slower writes for dramatically faster reads, and the whole skill is knowing exactly which trade you are making.
The reason this comes up in almost every round is that indexing sits on the two axes every design cares about: latency and write cost. A lookup served by an index is orders of magnitude faster than one that scans every row, and every row the index lets the database skip is work it never does. But an index is a second structure that must be kept in sync, and the instant you add one you have made writes more expensive and committed storage to it — so an index on the wrong column, or too many indexes, quietly makes the system slower. This guide walks the choices a strong candidate names out loud: why an index is a B-tree and what that buys you, the index types worth naming (clustered vs secondary, composite, covering, hash), and the trade-offs — write amplification, selectivity, column order, and the planner ignoring your index — that the follow-up questions are really about.
Why indexing comes up in system design interviews
Indexing rarely gets its own whiteboard prompt. It shows up inside another design — a product catalogue, a news feed, an orders table — at the moment you realise a query filters millions of rows and the database is scanning all of them. You say "I'll index that column," and the interviewer leans in: not whether to index, but how. Which columns, what kind of index, what it costs on writes, and why the database might ignore it anyway. Indexing is a favourite probe precisely because "add an index" is the easy half of the sentence and everything true and hard lives in the second half.
The interviewer rarely lets "we'll index it" stand on its own. "You index the
statuscolumn on a table of a billion orders, but the query still scans. There are only three distinct statuses. Walk me through why the index does not help, and what you'd index instead." That question — selectivity and when an index earns its cost — is the one a memorised "add an index" answer can never survive, and the one this topic is really testing.
The rounds on Zynter are modelled on how the loop runs, not scraped from any candidate's recall. What matters for prep is the shape: you name an index, and the next sentence out of the interviewer's mouth attacks its weakest point — write cost, low selectivity, the wrong column order, or a query the index cannot cover.
What the interviewer is really testing
An indexing answer is read on the same four dimensions as any design decision. Problem solving — did you index the column the query actually filters and sorts on, or the first one you saw? Communication — can you say in one sentence why an index on a three-value column is useless but one on user_id is transformative? Technical execution — do you account for the write cost, reach for a composite or covering index when it fits, and know why the planner might skip your index? Time management — did you spend your words on selectivity and the write trade-off, not on reciting that an index "makes reads faster"? Reading this guide teaches the mechanics; it does not teach you to defend an index while someone pushes on the slow write.
What an index is, and why it's a B-tree
Start with the mechanic, because every later decision follows from it. Without an index, finding the rows matching WHERE email = 'x' means a full table scan — read every row and test it, which is O(n) and ruinous at a billion rows. An index is a separate, sorted data structure that lets the database find matching rows without scanning them all.
Almost every general-purpose index is a B-tree (really a B+-tree). The idea to say out loud: it keeps keys in sorted order in a shallow, balanced tree, so a lookup walks from the root down a handful of levels to the leaf — O(log n) instead of O(n). Because the leaves are sorted and linked, a B-tree also serves range queries (WHERE age BETWEEN 20 AND 30), ORDER BY, and prefix matches efficiently — not just exact lookups. That range capability is why B-trees, not hash tables, are the default.
There are two flavours of how the index relates to the row data:
- Clustered index — the table rows are physically stored in the index's order (typically the primary key). There is one per table because the data can only be sorted one way; a lookup by the clustered key reaches the row directly.
- Secondary (non-clustered) index — a separate structure whose leaves hold the indexed value plus a pointer (the primary key) back to the row. A lookup finds the pointer, then does a second read to fetch the row — the extra hop matters for the covering-index idea below.
"Why is an index a tree and not a hash map?" A hash index gives O(1) exact-match lookups but cannot do ranges, ordering, or prefix matches; a B-tree keeps keys sorted so it serves
=,BETWEEN,ORDER BY, and prefix queries in O(log n) — which is what real queries need.
The index types worth naming
Naming the right kind of index for a query is what separates a real answer from "add an index." Four are worth having in your vocabulary.
Composite (multi-column) index. An index on (a, b, c) sorts by a, then b, then c. The rule to state is the leftmost prefix: it can serve queries filtering on a, on a and b, or on all three — but not a query on b alone, because the index is not sorted by b first. Column order is therefore a design decision: put the column used for equality filters first, the one used for ranges or sorting after.
Covering index. If an index contains every column a query needs (both the filtered and the returned columns), the database can answer from the index alone — an index-only scan — and skip the second read back to the row. This is one of the highest-leverage optimisations to name: a covering index on (user_id, created_at) for a query that selects only those columns never touches the table.
Hash index. O(1) exact-match lookups, but no ranges or ordering. Worth naming so you can say why you are not using it for most cases — it fits only pure equality lookups.
And when not to index. Every index must be updated on every INSERT, UPDATE, and DELETE to the indexed columns, so each one makes writes slower and costs storage. An unused index is pure overhead. The senior move is to index for the queries that actually run and to say "I would not index this" when the column is rarely filtered or the table is write-heavy.
| Index type | Best for | The catch |
|---|---|---|
| B-tree (single column) | equality, ranges, ORDER BY, prefix |
the write/storage cost of any index |
Composite (a,b,c) |
multi-column filters sharing a leftmost prefix | useless for queries that skip the leading column(s); column order matters |
| Covering | reads that select only indexed columns (index-only scan) | wider index = more storage and slower writes |
| Hash | pure exact-match lookups | no ranges, no ordering, no prefix |
System Design
HLD · Core concept
Pick and defend an indexing plan out loud
The hard parts interviewers probe
This section is where most of the follow-ups land, because it is where indexing goes wrong in production.
Write amplification. Every index is a second structure kept in sync on every write to its columns, so adding indexes makes INSERT/UPDATE/DELETE slower and uses more storage. A table with ten indexes pays for ten updates on every row change. The framing to say: indexes are a read optimisation you buy with write cost, so on a write-heavy table you index sparingly and deliberately.
Selectivity (cardinality). An index only helps if it narrows the search a lot. A column with few distinct values — status with three options, a boolean — has low selectivity, so an index on it still points at a huge fraction of the table and the planner will often ignore it in favour of a scan. Index high-cardinality columns (user_id, email, order_id), and reach for a composite or partial index when you must filter a low-selectivity column.
Composite column order. Because of the leftmost-prefix rule, (user_id, created_at) and (created_at, user_id) are different indexes that serve different queries. Order the columns by how queries use them — equality filters first, then the range or sort column — or the index will not be used the way you expect.
When the planner ignores your index. The query optimiser is cost-based: if it estimates that a scan is cheaper (low selectivity, a small table, a function wrapping the column like WHERE LOWER(email) = … that defeats the index, or stale statistics), it will not use your index. Knowing that the index is a hint the planner may decline, not a guarantee, is a senior-level point.
The point to state plainly: an index is only worth its write cost when it is selective, ordered for the query, and actually usable by the planner — otherwise you have paid for writes and storage and sped up nothing.
System Design
HLD · Core concept
Defend selectivity and the write trade-off out loud
Where indexing fits in a design
"Add an index" is a local fix; a strong answer places it in the bigger read/write story:
- Read-heavy, OLTP lookups — index the high-cardinality columns the hot queries filter and sort on; reach for covering indexes on the hottest read paths to get index-only scans.
- Write-heavy tables — index sparingly; every index taxes the write path. Consider whether the read even needs the database or belongs in a cache.
- Beyond a single node — indexing speeds a query on one machine, but when the table itself is too big or too hot, the next tools are sharding (partition the data so each node indexes less) and replicas (serve reads off copies). Indexing, caching, sharding and replication are the four levers for a read bottleneck, and naming where indexing stops and the others start is the senior framing.
How to talk about indexing in an interview
The probes from every section rhyme. Master these five and you can defend an indexing decision in any design, not just this checklist.
- "This query is slow — what do you index and why?" → The high-cardinality column(s) it filters and sorts on, as a composite ordered equality-first then range, and a covering index if the query selects only those columns. (problem solving)
-
"You indexed
statuswith three values and it still scans — why?" → Low selectivity: the index still points at most of the table, so the planner picks a scan; index a high-cardinality column instead, or a partial/composite index. (communication) - "What does an index cost you?" → Write amplification and storage — every index is updated on every write to its columns — so index deliberately, especially on write-heavy tables. (technical execution)
-
"Why does column order matter in a composite index?" → The leftmost-prefix rule:
(a,b)serves filters onaora+bbut notbalone, so order columns by how queries use them. (technical execution) - "Why might the database ignore your index?" → The cost-based planner chose a scan — low selectivity, a tiny table, a function wrapping the column, or stale statistics; an index is a hint, not a guarantee. (problem solving)
Each maps to a debrief dimension. Reading them teaches the answers; it does not teach you to deliver them while someone interrupts with the slow write — that only comes from saying them out loud.
How to prepare: a short plan
You do not need a new topic for every design. You need the indexing vocabulary automatic, so when a query is slow you reach for the right index without thinking.
- Day 1: Learn the mechanic — full scan vs B-tree O(log n), range support, clustered vs secondary — until you can draw it and explain why it is a tree, not a hash.
- Day 2: Drill the types and the trade-offs — composite and the leftmost-prefix rule, covering indexes and index-only scans, selectivity, write amplification, and when the planner declines. These are where the follow-ups live.
- Day 3: Place indexing inside a real design (a catalogue, an orders table, a feed): pick the columns, choose composite order, decide what not to index, and say where sharding and caching take over. Then do it out loud, timed, with someone pushing on the slow write and the low-selectivity column.
This explainer feeds the broader Top 25 system design interview questions — indexing is a decision inside almost every one of them, so getting it automatic pays off across the whole list.
Frequently asked questions
What is a database index and how does it speed up queries?
A database index is a separate, sorted data structure that lets the database find matching rows without scanning the whole table. Without an index, a filter like WHERE email = 'x' forces a full table scan — reading and testing every row, which is O(n) and far too slow at scale. An index, almost always a B-tree, keeps the indexed values in sorted order in a shallow balanced tree, so a lookup walks from the root to a leaf in O(log n) and jumps straight to the matching rows. The cost is that the index uses extra storage and must be updated on every write to the indexed columns, so it trades slower writes for much faster reads.
Why are most database indexes B-trees instead of hash tables?
A hash index gives O(1) exact-match lookups but cannot handle ranges, ordering, or prefix matches, because hashing destroys the ordering of the keys. A B-tree (really a B+-tree) keeps keys sorted in a balanced tree, so it serves equality lookups, range queries like BETWEEN, ORDER BY, and prefix matches all in O(log n). Real queries need those range and ordering capabilities far more often than they need the marginal speed of a hash lookup, which is why B-trees are the default general-purpose index and hash indexes are reserved for pure equality use cases.
What is a composite index and why does column order matter?
A composite index covers multiple columns, for example (user_id, created_at), and sorts rows by the first column, then the second within it, and so on. The leftmost-prefix rule means it can serve queries that filter on the leading column or a leading prefix — user_id alone, or user_id and created_at together — but not a query that filters only on a later column like created_at by itself, because the index is not sorted by that column first. So column order is a deliberate design choice: put the column used for equality filters first and the column used for ranges or sorting after, to match how the queries actually run.
What is a covering index?
A covering index is one that contains every column a query needs — both the columns it filters on and the columns it returns — so the database can answer the query entirely from the index without reading the underlying table rows. This is called an index-only scan and it avoids the extra lookup back to the row that a secondary index normally requires, which makes it one of the highest-leverage read optimisations. The trade-off is that adding more columns to the index makes it wider, so it uses more storage and makes writes a little slower; you use it on the hottest read paths where the saved row lookups are worth it.
Why would a database ignore an index I created?
The query planner is cost-based: it estimates the cheapest way to run each query and will skip an index if a scan looks cheaper. The most common reasons are low selectivity (the indexed column has few distinct values, so the index still matches most of the table), a very small table where a scan is trivial, a function wrapping the column such as WHERE LOWER(email) = … which prevents the index from being used, or stale statistics that mislead the planner. The practical lesson is that an index is a hint the planner may decline, not a guarantee — you index high-cardinality columns the queries actually use and keep statistics current.
How do I practise a system design interview out loud?
Reading about indexing is not the same as defending an index choice while an interviewer pushes on the slow write. On Zynter, the HLD track runs live voice rounds modelled on how real system design loops run — you talk through a design on a shared canvas, and a voice interviewer follows up one question at a time ("that column has three values — why index it?", "what does this index cost on writes?"), then gives you a debrief on problem solving, communication, technical execution and time management. The score is a practice signal, not a hiring decision.
Practise it on Zynter
You can recite B-tree and covering index. The round is won on defending selectivity out loud.
Pick an HLD system design round, add your indexes to the data model on the canvas, and defend them out loud to a voice interviewer that keeps asking "that column has three values — why index it?" and "what does this index cost you on every write?" — then read the debrief. Zynter.ai is on-demand mock interviews modelled on real company loops, with 150+ rounds across 20 companies including the HLD system design track (Sep 2026).
Practise an HLD system design round →
Browse all interview tracks




Top comments (0)