Database Indexing Interview Questions
What Interviewers Are Testing
Database indexing interview questions evaluate your understanding of how indexes improve query performance, the trade-offs they introduce, and how to design indexes for specific query patterns.
Interview Questions & Model Answers
What is a database index and how does it work?
A database index is a sorted data structure (usually a B-tree) that stores column values with pointers to the corresponding full rows. When you query with WHERE col = value, the database searches the much smaller, sorted index to find the matching row pointer, then retrieves just that row — instead of scanning every row in the table.
-- Without index: full table scan (slow for 1M rows) SELECT * FROM users WHERE email = 'alice@example.com'; -- Create index: CREATE INDEX idx_users_email ON users(email); -- Now the same query uses index seek (fast, O(log n)) EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
Potential Follow-up Questions:
- What is the difference between a clustered and non-clustered index?
When should you NOT create an index?
Avoid indexes when: (1) The table is very small — a full scan is faster than index overhead. (2) The column has very low cardinality (few unique values like a boolean/status column) — the index provides little selectivity benefit. (3) The table is write-heavy (INSERT/UPDATE/DELETE) — every index must be maintained on every write. (4) The column is never used in WHERE, JOIN, or ORDER BY clauses.
Potential Follow-up Questions:
- What is index cardinality and why does it matter?
What is a composite index and what is the leftmost prefix rule?
A composite index covers multiple columns: CREATE INDEX idx ON orders(customer_id, status, created_at). The leftmost prefix rule: the index is useful for queries that filter on the leftmost columns in order. This index helps: WHERE customer_id = 5 (uses first column), WHERE customer_id = 5 AND status = 'pending' (uses first two), but NOT WHERE status = 'pending' (skips the leftmost column — cannot use the index efficiently).
CREATE INDEX idx_orders ON orders(customer_id, status, created_at); -- Uses index: SELECT * FROM orders WHERE customer_id = 42; SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid'; -- Does NOT use index efficiently: SELECT * FROM orders WHERE status = 'paid'; -- skips customer_id
Potential Follow-up Questions:
- How do you determine the optimal column order for a composite index?
What is a covering index?
A covering index is an index that contains all the columns needed to satisfy a query — including the columns in SELECT — so the database can answer the query using only the index, without reading the actual table rows. This is the fastest possible read performance because it eliminates a second I/O step.
-- Query: get status and total for customer 42 SELECT status, total FROM orders WHERE customer_id = 42; -- Covering index includes all 3 needed columns: CREATE INDEX idx_covering ON orders(customer_id, status, total); -- PostgreSQL shows: 'Index Only Scan' -- MySQL shows: 'Using index' in EXPLAIN Extra column
Potential Follow-up Questions:
- What is the difference between an index scan and an index-only scan?
Frequently Asked Questions
What index questions are commonly asked in database interviews?
What is the difference between a clustered and non-clustered index?
Related Interview Topics
Practice Answering Live
Use our Interview Arena to practice SQL challenges under real interview conditions. Free.