Indexing
DBMS · 10 interview questions
An index is a separate data structure that makes finding rows faster than scanning the table. Almost all general-purpose database indexes are B+ trees, which keep data sorted and balanced so lookups take a few disk reads regardless of table size, and which support range scans because the leaves are linked.
A clustered index determines the physical order of the rows themselves, so there can only be one per table and the table effectively is the index. A non-clustered index is a separate structure storing key values and a pointer back to the row, which means a second lookup unless the index already contains every column the query needs.
The cost is on writes. Every insert, update and delete must maintain every index on the table, so indexes are a read-speed-for-write-speed trade, not free.
Indexing interview questions
- Why do databases use B+ trees rather than binary trees for indexes?
- B+ trees have a high branching factor, so the tree stays shallow and a lookup costs only a few page reads. Nodes are sized to a disk page, and the linked leaf level makes range scans sequential. A binary tree would be far deeper, meaning far more random I/O.
- What is a clustered index?
- An index that determines the physical order of rows in the table — the leaf level is the data. There can be only one per table, and lookups by it need no second read.
- What is a non-clustered index?
- A separate structure holding the indexed columns plus a reference to the row. Finding the row usually requires a second lookup into the table, sometimes called a bookmark lookup.
- What is a covering index?
- One that contains every column a query needs, so the query is answered from the index alone with no lookup back to the table. Often the difference between a fast query and a slow one.
- Why does column order matter in a composite index?
- Because the index is sorted by the leftmost column first. An index on (a, b) helps queries filtering on a, or on a and b, but generally not one filtering on b alone — this is the leftmost prefix rule.
- Why they ask: Very common as a practical 'why isn't my index being used' question.
- What does adding an index cost?
- Storage, and write speed — every insert, update and delete must update every affected index. It also gives the optimizer more plans to consider. Indexes nobody queries are pure overhead.
- Why does wrapping a column in a function stop an index being used?
- The index stores the column's values, not the function's results, so WHERE YEAR(created_at) = 2026 can't be looked up. Rewrite as a range on the raw column, or create an index on the expression if the database supports it.
- What is selectivity, and why does it decide index usefulness?
- The fraction of rows a condition matches. High selectivity — few matching rows — makes an index worthwhile. On a column with two distinct values, an index scan plus lookups is often slower than just scanning the table, so the optimizer ignores it.
- When is a hash index preferable to a B-tree?
- Only for exact-equality lookups, where it can be O(1). It cannot support range queries, sorting or prefix matching, which is why B+ trees remain the default.
- Does a primary key automatically create an index?
- Yes — uniqueness has to be enforced, and an index is how. In InnoDB the primary key is also the clustered index, so the choice of primary key determines physical row order.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days