Database indexing interview questions and answers

The query was slow, so he added an index on the column in the WHERE clause. It was still slow. He added another. Still slow. By the time the interviewer stopped him he had proposed four indexes on a table, none of which would be used, because the predicate wrapped the column in a function and every index he had suggested was therefore invisible to the planner.

Database indexing interview questions are asked constantly for backend, data and full-stack roles, and they separate candidates cleanly — because knowing that an index makes reads faster is table stakes, while knowing why a specific index will not be used is the actual skill. This guide covers the six areas that recur, with the answers that land.

How an index actually works

The answer that scores: most relational indexes are B+ trees — balanced, sorted structures where lookups, insertions and deletions are logarithmic in the number of rows, and where the leaf nodes are linked so a range scan can walk sideways without returning to the root.

Two consequences worth stating, because they explain almost everything else:

• Because the index is sorted, it helps with more than equality. Range queries, ORDER BY on the indexed column, and MIN/MAX all benefit. A hash index would not help with any of those, which is the standard follow-up.
• Because it is a separate structure, it costs writes. Every insert, update or delete on an indexed column must maintain the tree, and the index consumes disk. That is why "just add an index" is not a free action, and saying so unprompted is a strong signal.

Also know the distinction between a clustered index, which determines the physical order of the rows and of which there can only be one, and a secondary index, which stores a pointer back to the row. In InnoDB the primary key is the clustered index and every secondary index stores the primary key value, which is why a wide primary key makes every other index bigger.

Composite indexes and the leftmost prefix rule

This is the most commonly asked and most commonly fumbled question.

An index on (user_id, created_at) can serve a query filtering on user_id, or on user_id and created_at together. It cannot efficiently serve a query filtering only on created_at, because the index is sorted by user_id first — the created_at values are only ordered within each user_id group.

The rule: a composite index supports any leftmost prefix of its columns.

The follow-up: how do you order the columns? The usual guidance is equality predicates first, then the range or sort column. For WHERE user_id = ? AND created_at > ?, the order (user_id, created_at) is correct because the equality narrows to a contiguous block and the range then scans within it. Reverse them and the database must scan a range and filter, which is much worse.

-- index: (user_id, created_at)
WHERE user_id = 42                                   -- uses it: leftmost prefix
WHERE user_id = 42 AND created_at > '2026-01-01'     -- uses it fully: equality then range
WHERE created_at > '2026-01-01'                      -- cannot use it: skips the first column
ORDER BY created_at                                  -- only sorted within a user_id

Covering indexes

An index that contains every column the query needs, so the database answers entirely from the index without touching the table. In an execution plan this shows as an index-only scan, and it can be dramatically faster because it avoids a random read per row.

Say the tradeoff: a covering index is wider, so it costs more disk and more write overhead. Postgres has INCLUDE and SQL Server has included columns precisely so you can add payload columns without making them part of the sort key.

Why the database ignored your index

The highest-value question in the round. The reasons, roughly in order of how often they actually happen:

• A function or expression on the column. WHERE YEAR(created_at) = 2026 or WHERE LOWER(email) = ? cannot use a plain index on that column. The fixes are rewriting as a range (created_at >= '2026-01-01' AND < '2027-01-01') or creating a functional or expression index.
• A leading wildcard. LIKE '%foo' cannot use a B-tree, because the index is sorted from the left. LIKE 'foo%' can.
• Type mismatch or implicit casting. Comparing a string column to a number, or mismatched collations, silently disables the index.
• Low selectivity. If a predicate matches 40% of the table, a sequential scan is genuinely cheaper than an index scan plus 40% of the rows fetched randomly. The planner is right and you are wrong — this is the answer interviewers most want to hear, because it shows you understand the planner is cost-based rather than rule-based.
• Stale statistics. The planner estimates using stats; if they are out of date after a bulk load it can choose badly. ANALYZE is the fix.
• OR across different columns, which often prevents a single index from being used; a union of two indexed queries can be faster.
• The table is small. Below a few thousand rows, scanning is cheaper than any index.

The write cost, and index hygiene

• Every index slows writes. A table with nine indexes pays nine tree maintenance operations per insert.
• Unused indexes are pure cost. Both Postgres and MySQL expose index usage statistics; finding and dropping unused indexes is a real, easy win and a good thing to mention.
• Redundant indexes. An index on (a) is redundant if an index on (a, b) exists, because the composite already serves the leftmost prefix.
• Index bloat and rebuilds after heavy update or delete churn.
• Write-heavy tables deserve deliberately fewer indexes — the classic answer to "how would you speed up ingestion?"

Reading an execution plan

Expect to be asked to interpret one, or at least to describe how you would diagnose a slow query.

• Run EXPLAIN ANALYZE (Postgres) or EXPLAIN (MySQL) and read from the innermost node outward.
• Sequential scan on a large table where you expected an index — the question from the section above.
• The estimated versus actual row counts. A large divergence means the statistics are wrong, and every join decision above it is therefore suspect. This is the single most useful thing to look at, and few candidates mention it.
• Nested loop with a large outer input is often the cause of a query that is fine on test data and terrible in production.
• A sort node that could have been eliminated by an index matching the ORDER BY.
• Rows removed by filter, indicating an index that got you close but not close enough.

Other index types worth naming

• Hash — equality only, no ranges, no ordering. Rarely the right default.
• GIN / inverted — for full-text search and for array or JSON containment. The answer to "how would you index a JSON column?"
• Partial index — an index with a WHERE clause, ideal when queries only ever touch a small subset such as status = 'active'. Cheap, small, and underused.
• Unique index — enforces a constraint and provides the index at the same time.
• Bitmap — good for low-cardinality columns in analytical workloads, generally poor under concurrent writes.

Questions you should expect verbatim

• "What is the difference between a clustered and a non-clustered index?"
• "What is a composite index and does column order matter?"
• "Why might a query not use an index you created?"
• "What are the downsides of adding an index?"
• "How would you speed up a slow query?" — the answer is to read the plan first, not to add an index first.
• "How do you index a column you only query with LIKE '%term%'?" — a B-tree cannot help; you want full-text search or a trigram index.

Our DBMS interview questions guide covers transactions and isolation, SQL interview questions covers query writing, and database sharding interview questions covers what happens when one machine is no longer enough.

Where each prep option actually helps

• Use The Index, Luke — a free online book that is the best single resource on this topic, and the leftmost-prefix explanation is worth the visit alone.
• The Postgres documentation on indexes and EXPLAIN — precise, and the source of most interview phrasing.
• A real slow query — take one from your own project, read the plan, add an index, and read the plan again. That single exercise answers most of this round.
• ChatGPT — good at explaining a plan you paste in. It will not notice that your predicate wraps the column in a function, because you will not have shown it the query.
• Greenroom — the spoken layer. Ari, the AI interviewer runs backend rounds out loud and asks why, which is where memorised definitions stop. Honest tradeoff: Ari will not run your query, so profile one yourself.

Frequently asked questions

How does a database index work?

Most relational indexes are B+ trees: balanced sorted structures giving logarithmic lookup, with linked leaf nodes so range scans can walk sideways without returning to the root. Because the structure is sorted it helps with ranges, ORDER BY and MIN or MAX as well as equality, which a hash index would not. Because it is a separate structure it must be maintained on every insert, update and delete, which is why adding an index is never free.

Does column order matter in a composite index?

Yes, and this is the most commonly fumbled indexing question. A composite index supports any leftmost prefix of its columns, so an index on user_id and created_at serves queries filtering on user_id, or on both, but cannot efficiently serve a query filtering only on created_at. The usual ordering guidance is equality predicates first and the range or sort column last, because the equality narrows to a contiguous block that the range then scans within.

Why is my query not using the index I created?

The most common causes are a function or expression wrapping the column such as YEAR(created_at), a leading wildcard in a LIKE pattern, a type mismatch or implicit cast, low selectivity where the predicate matches a large share of the table so a sequential scan is genuinely cheaper, stale statistics after a bulk load, an OR across different columns, or a table small enough that scanning wins. Recognising that the planner is cost-based and sometimes correct to ignore your index is the answer interviewers most want to hear.

What is a covering index?

A covering index contains every column a query needs, so the database can answer entirely from the index without reading the table, which appears as an index-only scan in the execution plan and avoids a random read per row. The tradeoff is a wider index costing more disk and more write overhead, which is why Postgres offers INCLUDE and SQL Server offers included columns — they let you add payload columns without making them part of the sort key.

What are the downsides of adding an index?

Every index must be maintained on insert, update and delete, so a table with many indexes pays that cost on every write, and each index consumes disk space. Unused indexes are pure overhead and both Postgres and MySQL expose usage statistics so they can be found and dropped. An index on a single column is also redundant when a composite index already leads with that column, and heavily updated tables can suffer index bloat requiring periodic rebuilds.

How do you diagnose a slow SQL query?

Read the execution plan before adding anything. Run EXPLAIN ANALYZE and work from the innermost node outward, looking for a sequential scan on a large table where you expected an index, a large divergence between estimated and actual row counts which means the statistics are wrong and every join choice above it is suspect, a nested loop with a large outer input, a sort that an index could have eliminated, and rows removed by filter indicating an index that got close but not close enough.