Database Indexing: How to Explain It in an Interview (2026)

When an interviewer asks you to explain database indexing, they want to see that you understand both the idea and the practical impact on a system. A good answer is short, concrete, and shows you can connect the concept to real‑world decisions.

One‑Sentence Definition

A database index is a auxiliary data structure that lets the engine locate rows matching a query key without scanning every row in the table.

How Indexes Work Under the Hood

Most relational databases use a B‑tree (or variant) for primary and secondary indexes. The tree stores the indexed column values in sorted order, and each leaf node holds a pointer (row ID or physical address) to the actual row. When the engine receives a query like WHERE last_name = 'Smith', it traverses the tree from root to leaf, following the sorted keys until it finds the matching leaf, then follows the pointer to the row.

Key Mechanics

• Sorted keys: Enables binary‑search‑style navigation, O(log N) lookup.
• Pointers: Direct references to the row, avoiding full‑table scans.
• Maintenance: Insert, update, or delete operations must adjust the tree, which adds overhead.

Trade‑offs: When to Use an Index

In most interview scenarios, you’ll emphasize that the decision to add an index balances read‑heavy workloads against write‑heavy workloads and storage constraints.

Concrete Example

Imagine a users table with 10 million rows and a frequent query:

SELECT id, email FROM users WHERE email = 'alice@example.com';

Without an index, the engine scans all rows – O(N) work. Adding a unique index on email creates a B‑tree keyed by the email string. The lookup becomes O(log N) and typically resolves in a few milliseconds. The trade‑off is that each new user insertion now requires an extra B‑tree insertion, adding a few microseconds to the write latency.

Typical Interview Questions

1. Why do we need indexes if the DB already stores data? – Explain that tables are stored unsorted; indexes provide a sorted map to locate rows quickly.
2. What are the differences between a primary key and a secondary index? – Primary keys are unique and often clustered; secondary indexes are separate structures that may be non‑unique.
3. When would you avoid indexing a column? – High‑cardinality columns with frequent writes, columns that are rarely used in WHERE/ORDER BY, or columns with large text values where the index would be huge.
4. How does an index affect query plans? – The optimizer evaluates cost estimates; an index can turn a full scan into an index seek, dramatically lowering the estimated cost.
5. What is a covering index? – An index that includes all columns needed by the query, allowing the engine to satisfy the query from the index alone.

60‑Second Spoken Version

"An index is a sorted data structure, usually a B‑tree, that stores column values alongside pointers to the actual rows. When you query WHERE col = value, the engine walks the tree instead of scanning every row, which drops lookup time from linear to logarithmic. The upside is much faster reads, especially for filters and sorts. The downside is extra storage and slower writes because every INSERT, UPDATE, or DELETE must also update the index. A typical use case is adding a unique index on an email column in a large users table: reads go from minutes to milliseconds, while inserts incur a small overhead. In interviews, you’ll be asked why you’d add or omit an index, how primary and secondary indexes differ, and what a covering index is."

How to Practice This

1. Write the answer on paper – Draft the short definition, mechanism, and trade‑off in 3‑4 sentences.
2. Record yourself – Aim for a 45‑90 second delivery; listen for filler words and adjust.
3. Use Call Assistant – Run a mock interview, let the tool capture your answer, and get real‑time feedback on staying on topic and timing.

---

FAQ

• Q: Do all databases use B‑trees for indexes? A: B‑trees are common in relational systems, but some engines use hash indexes, GiST, or column‑store structures depending on the workload.
• Q: What is a clustered index? A: A clustered index determines the physical order of rows on disk; the table data is stored together with the index leaf nodes.
• Q: Can an index make a query slower? A: Yes, if the optimizer chooses an index that forces many random I/O operations instead of a sequential scan, especially on very small tables.
• Q: How many indexes should a table have? A: There is no hard rule, but most production tables have a primary key plus a handful of secondary indexes that match the most common query patterns.