Database Indexing Interview Questions and Answers (2026)

When an interviewer asks about indexing, they’re looking for three things: you understand the concept, you can reason about trade‑offs, and you have a story of actually using indexes to improve performance. Below is a curated list of questions that span entry‑level to senior‑level discussions, plus a short spoken answer you can deliver in 45‑90 seconds. After each answer, note the typical follow‑up the interviewer may ask. Practice delivering the answer aloud – tools like Call Assistant can record you and keep the conversation on track while you reference your resume.

1. What is a database index and why do we use it?

A database index is a data structure that lets the engine locate rows without scanning the whole table. Think of it as the index at the back of a textbook: you look up a term and jump straight to the pages that contain it. In practice, an index stores the indexed column(s) together with a pointer to the row location. This reduces I/O and CPU work, especially for large tables.

Typical follow‑up: "Can an index ever hurt performance?"

2. How does a B‑tree index work?

Most relational databases implement the primary index as a balanced tree (B‑tree). The tree keeps keys sorted and guarantees logarithmic search time. Each node holds a range of keys and pointers to child nodes; leaf nodes contain the actual key and a row identifier (RID). Because the tree stays balanced, inserts, deletes, and lookups all take roughly the same number of page reads.

Typical follow‑up: "What happens when the tree becomes fragmented?"

3. When would you choose a hash index over a B‑tree?

Hash indexes store a hash of the key and a pointer to the row. They excel at equality predicates (e.g., WHERE id = 42) because the hash computation gives direct access. However, they cannot support range scans or ordering, and they may suffer from collisions. Use a hash index when you have a high‑cardinality column that is always queried with equality and the workload is read‑heavy.

Typical follow‑up: "Are hash indexes common in modern RDBMSs?"

4. What is a covering index and why is it useful?

A covering index includes all columns needed by a query, so the engine can satisfy the request using only the index, without touching the base table. For example, an index on (status, created_at) that also stores order_id can cover a query that selects those three columns. The benefit is fewer page reads and less locking contention.

Typical follow‑up: "How do you decide which columns to add to a covering index?"

5. Explain multi‑column (composite) indexes and the importance of column order.

A composite index stores a tuple of columns in the order you define. The index can be used for predicates that reference a left‑most prefix of the column list. For instance, an index on (country, city, zip) can efficiently serve queries filtering by country alone, country + city, or all three. Changing the order changes which queries can use the index; the most selective column should generally come first.

Typical follow‑up: "What if the query filters on city but not country?"

6. How do you monitor index usage and decide when to drop an index?

Most databases expose DMVs or system tables that report index scans, seeks, and usage counts. Look for indexes with very low seek counts but high maintenance cost (e.g., frequent page splits). If an index hasn’t been used in a reasonable period and its write overhead outweighs its benefit, consider dropping it.

Typical follow‑up: "What risks are there when you drop an index?"

7. Describe index maintenance operations (rebuild, reorganize, statistics update).

Indexes degrade over time due to page splits and fragmentation. A reorganize defragments the leaf level without locking the table; it’s lightweight and can run frequently. A rebuild creates a fresh copy of the index, removing all fragmentation, but may lock the table or require extra space. Updating statistics ensures the optimizer has accurate cardinality estimates, which is essential for the index to be chosen.

Typical follow‑up: "When would you prefer a rebuild over a reorganize?"

8. What are partial (filtered) indexes and when are they beneficial?

A partial index includes only rows that satisfy a predicate, reducing size and write cost. For example, indexing only active users (WHERE status = 'active') can make the index much smaller while still supporting the most common queries. Use them when a subset of rows dominates the workload.

Typical follow‑up: "Do partial indexes affect query plans for the excluded rows?"

9. How do column‑store indexes differ from row‑store indexes?

Column‑store indexes store data column‑wise, which compresses well and speeds up analytical queries that aggregate many rows but only a few columns. They are less suited for point lookups or transactional workloads. In a hybrid system, you might keep a row‑store primary key and add a column‑store secondary index for reporting.

Typical follow‑up: "Can you have both types on the same table?"

10. Walk through a real‑world scenario where you improved performance with an index.

Sample answer (spoken, ~70 seconds):

"At my previous company we had a reporting dashboard that refreshed every five minutes. The underlying query joined the orders table on customer_id and filtered by order_date ≥ last 30 days. The execution plan showed a full table scan on orders, which was 20 million rows. I added a composite index on (customer_id, order_date) covering the needed columns. After the index was built, the scan turned into an index seek, and the query time dropped from 12 seconds to under 2 seconds. We also set a daily rebuild to keep fragmentation low. The dashboard became responsive, and the team could add more filters without breaking performance."

Typical follow‑up: "How did you verify the index was used and not just a plan cache artifact?"

11. What is index selectivity and how does it influence index choice?

Selectivity is the fraction of rows a predicate returns. High selectivity (e.g., 0.1 % of rows) means the index can prune most rows, making it worthwhile. Low selectivity (e.g., 50 % of rows) often leads the optimizer to prefer a full scan. When evaluating an index, consider the column’s cardinality and typical filter values.

Typical follow‑up: "Can you force the optimizer to use an index even if selectivity is low?"

12. How do you handle indexing for write‑heavy workloads?

For write‑heavy tables, each index adds overhead on INSERT/UPDATE/DELETE because the engine must maintain additional structures. Strategies include:

• Keep only essential indexes (primary key, foreign keys, and a few covering indexes).
• Use partial indexes to limit maintenance to active rows.
• Batch writes to reduce per‑row overhead.
• Periodically rebuild or reorganize to control fragmentation.

Typical follow‑up: "What about using a NoSQL store for high‑write scenarios?"

13. Discuss the trade‑offs of using a clustered index versus a non‑clustered index.

A clustered index determines the physical order of rows; there can be only one per table. It provides fast range scans but makes inserts more expensive if the key isn’t monotonic. Non‑clustered indexes are separate structures that reference the clustered key; they are cheaper to maintain but may require extra lookups (bookmark lookups) to fetch other columns. Choose a clustered index on a column that is frequently used for range queries and grows monotonically (e.g., timestamp).

Typical follow‑up: "Can you have a clustered index on a composite key?"

14. How do you test the impact of a new index before deploying to production?

Create the index on a staging copy of the database or on a subset of production data using a temporary index. Run the representative workload and capture execution plans and query timings. Compare against the baseline. If the index shows a clear win without unacceptable write overhead, you can roll it out with a controlled rollout, monitoring metrics like CPU, I/O, and latency.

Typical follow‑up: "What monitoring tools do you rely on for this?"

15. What are the emerging trends in indexing as of 2026?

• Adaptive indexing: databases that automatically create or modify indexes based on query patterns.
• Vector indexes: for similarity search in AI‑augmented workloads.
• Hybrid storage engines: combining row‑store and column‑store at the table level.
• Self‑tuning systems: leveraging machine learning to suggest index changes.

These trends aim to reduce manual index management while handling new data types.

---

How to practice this

1. Record yourself answering each question in 45‑90 seconds. Listen back and trim any filler.
2. Create a sandbox database (e.g., PostgreSQL or MySQL) and experiment: build an index, run EXPLAIN, then drop it and observe the plan change.
3. Map each answer to a bullet point on your resume. When you rehearse, reference the concrete project so the story stays grounded.

---

FAQ

• Q: Can an index ever make a query slower? A: Yes. If the index isn’t selective, the optimizer may choose it and perform many random I/O reads, which can be slower than a sequential table scan.
• Q: Do all databases support partial indexes? A: Most major relational systems (PostgreSQL, SQL Server, Oracle) support filtered or partial indexes, but the syntax and capabilities differ.
• Q: How often should I rebuild indexes? A: It depends on fragmentation and write volume. A common rule is to rebuild when fragmentation exceeds 30 % or after a major data load.
• Q: What’s the difference between a primary key and a unique index? A: A primary key enforces uniqueness and is often the clustered index; a unique index enforces uniqueness but can be non‑clustered.