Database Indexing Interview Questions
Last updated 2026-11-19
Indexing questions test whether you understand why a query is fast or slow. Expect to explain a B+ tree, compute its height, choose the column order of a composite index and say why an index was ignored. This page answers those with numbers and short SQL examples instead of slogans.
It builds on DBMS interview questions and pairs with keys, since primary keys usually create the main index. For a plan covering the whole DBMS round, see how to crack a DBMS interview.
| Structures | B+ tree, hash, bitmap, full-text |
|---|---|
| Key skills | Composite order, covering index, reading EXPLAIN |
| Level | Medium to hard |
Reading a query plan
Use EXPLAIN (MySQL, PostgreSQL) to see whether the engine chooses an index seek, an index scan or a full table scan. A full scan is not always wrong: if a query returns a large fraction of the table, sequentially reading it beats thousands of random index lookups. The optimiser decides using statistics about distinct values, so stale statistics cause surprising plans.
Typical reasons an index is not used
- A function wraps the column, as in WHERE YEAR(created) = 2026.
- A LIKE pattern starts with a wildcard, as in LIKE '%son'.
- The column is compared with a value of a different type, causing implicit conversion.
- A composite index is queried without its leftmost column.
- The predicate is not selective enough, so the optimiser prefers a scan.
Database Indexing Interview Questions: questions with worked answers
1 What is an index and what is the trade-off?
easyAn index is an auxiliary data structure, usually a B+ tree, that maps column values to row locations so the engine can find rows without scanning the whole table. A lookup becomes logarithmic instead of linear. The trade-off is that every INSERT, UPDATE and DELETE on indexed columns must also update the index, and each index consumes disk and memory. Index columns used in WHERE, JOIN and ORDER BY clauses, and avoid indexing everything.
2 Why do databases use B+ trees rather than binary trees?
easyDisk reads happen in pages, so the aim is to minimise page reads. A B+ tree node holds hundreds of keys and fits one page, so the tree is very shallow, typically three or four levels. A binary tree has one key per node and would be around twenty levels deep for a million rows, each a separate page read. B+ trees also keep all data pointers in linked leaf nodes, so range queries and ORDER BY walk the leaves sequentially.
3 Compute the height of a B+ tree: 4 KB pages, 8-byte keys, 8-byte pointers, 10 million rows.
mediumAn internal node with n pointers has n-1 keys, so 8n + 8(n-1) is at most 4096, giving 16n at most 4104 and n = 256. The fan-out is about 256. One level addresses 256 children, two levels 65,536, three levels about 16.7 million leaves-worth. So 10 million entries need roughly three levels of pointers: a lookup costs about three to four page reads, and the root and upper levels usually stay cached, making it one or two disk reads in practice. Real fan-out is lower because nodes are kept at least half full.
4 Clustered versus non-clustered index: what is stored in the leaves?
mediumIn a clustered index the leaves are the table rows themselves, sorted by the clustering key, so there can be only one per table; InnoDB clusters on the primary key. In a non-clustered (secondary) index the leaves hold the indexed columns plus a locator: in InnoDB the primary key value, in PostgreSQL a physical tuple id. A secondary lookup in InnoDB therefore walks two trees: the secondary index to find the primary key, then the clustered index to fetch the row. This is why long primary keys bloat every secondary index.
5 How do you choose the column order in a composite index?
mediumA B+ tree on (a, b, c) is sorted by a, then b within a, then c within b, so it supports predicates on a, on a and b, or on a, b and c, but not on b alone. Put equality columns first, then the range column, because columns after a range predicate cannot narrow the scan. Among equality columns, prefer the one that is most commonly queried; selectivity matters less than query shape. For WHERE country = ? AND created > ? ORDER BY created, the index (country, created) serves both filter and sort.
sqlCREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date); -- uses the index: WHERE customer_id = 7 AND order_date >= '2026-01-01' -- uses the index: WHERE customer_id = 7 -- cannot seek: WHERE order_date >= '2026-01-01'6 What is a covering index?
mediumA covering index includes all columns a query reads, so the engine answers entirely from the index and never touches the table, which EXPLAIN shows as "Using index" in MySQL or an index-only scan in PostgreSQL. For SELECT email FROM users WHERE city = ?, an index on (city, email) covers it. PostgreSQL and SQL Server also support INCLUDE columns that are stored in the leaf but not used for ordering, keeping the key small.
7 Why might the optimiser ignore an index? Fix these cases.
mediumWrapping the column in a function, as in WHERE DATE(created_at) = '2026-01-05', stops index use; rewrite it as a range, created_at >= '2026-01-05' AND created_at < '2026-01-06'. A leading wildcard in LIKE '%abc' cannot use a B+ tree; use a full-text or trigram index. Comparing a VARCHAR column to a number forces conversion of every row. Low selectivity, such as a boolean column, often makes a scan cheaper. Stale statistics can also mislead the planner, so run ANALYZE.
8 Dense versus sparse index?
mediumA dense index has an entry for every search-key value or record; a sparse index has entries only for some records, typically one per data block, and relies on the file being sorted on that key. Sparse indexes are smaller, but need the data ordered, as with a clustered index. A dense index can be used on an unsorted file and can answer "does this key exist?" without reading data.
9 When would you use a hash index instead of a B+ tree?
mediumA hash index gives constant-time lookups for equality predicates only. It cannot do range queries, ordering or prefix matching, because hashing destroys ordering. It suits point lookups, such as key-value access. PostgreSQL supports hash indexes; MySQL InnoDB offers an adaptive hash index internally but does not let you create one directly, while the MEMORY engine does.
10 How does an index affect write performance?
mediumEach insert adds an entry to every index on the table, each delete removes entries, and an update to an indexed column moves the entry. B+ tree inserts can cause page splits that cost extra I/O and fragmentation. A table with ten indexes may write eleven structures per row. Bulk loads are therefore faster if you drop non-essential indexes first and recreate them afterwards. This is why write-heavy tables should have only the indexes their queries justify.
11 Why are UUID primary keys a problem for clustered indexes?
hardRandom UUIDs, such as version 4, land at random positions in the B+ tree, so inserts cause page splits across the whole tree, poor cache locality and a fragmented, larger index. Sequential keys append to the last page. Time-ordered identifiers (UUIDv7, ULID, or a snowflake-style id) keep inserts mostly sequential while remaining unique. Also remember that in InnoDB the 16-byte key is copied into every secondary index.
12 What is a partial (filtered) index and when is it useful?
hardA partial index covers only rows matching a predicate. In PostgreSQL: CREATE INDEX idx_open ON orders (customer_id) WHERE status = 'OPEN'. If only two percent of orders are open and most queries target them, this index is far smaller and cheaper to maintain than a full one. The query must include a predicate that implies the index condition for the planner to use it. SQL Server calls these filtered indexes; MySQL does not support them.
13 Write the SQL to inspect and improve a slow query.
mediumRun EXPLAIN to see the plan, check the type column (MySQL) or node type (PostgreSQL), add the missing index, then re-run EXPLAIN and compare the row estimates. PostgreSQL's EXPLAIN ANALYZE actually executes the query and reports real timings and row counts, so use it on a copy when the statement modifies data.
sqlEXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'SHIPPED'; CREATE INDEX idx_orders_cust_status ON orders (customer_id, status); EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'SHIPPED';
Frequently asked questions
Does a primary key automatically create an index?
Yes in all major engines; the primary key is backed by a unique index, and in InnoDB and SQL Server it is the clustered index by default.
Should I index every foreign key column?
Usually yes, because joins and cascading deletes look up child rows by that column. PostgreSQL does not create it automatically, while MySQL InnoDB does.
Is a B-tree the same as a B+ tree?
Not quite. A B-tree stores records in internal nodes too, while a B+ tree stores them only in linked leaves. Most databases that say B-tree actually implement a B+ tree.
How many indexes are too many?
There is no fixed number. Add an index when a query needs it and drop ones that statistics show are unused, since each one slows every write.
Related guides
More in CS Fundamentals Interview Questions
- DBMS Concurrency Control Interview Questions
- DBMS Interview Questions and Answers
- DBMS Keys Interview Questions
- Deadlock Interview Questions with Banker's Algorithm Worked Example
- Design Patterns Interview Questions with Code
- Distributed Systems Interview Questions
- DNS Interview Questions: Resolution, Record Types, Caching and Troubleshooting
- ER Model Interview Questions
- File System Interview Questions: Inodes, Allocation and Disk Scheduling
- Git Interview Questions and Answers with Commands
- HTTP and HTTPS Interview Questions: Methods, Status Codes, TLS and Caching
- Inheritance and Polymorphism Interview Questions