Blog

50 database index–focused interview questions

No Comments

Fundamental Indexing Questions (Basics)

1. What is a database index and why is it useful?

2. How does indexing improve query performance?

3. What is the difference between index scan vs index seek?

4. Explain clustered vs non-clustered indexes.

5. Can a table have multiple clustered indexes? Why/why not?

6. When should you use a clustered index?

7. When should you use a non-clustered index?

8. What is a unique index?

9. What is a primary key index vs a unique constraint index?

10. What are composite/multi-column indexes and when do you use them?

11. What is a covering index?

12. What is a bitmap index and where is it useful?

13. What is a full-text index and how is it different from an ordinary index?

14. What are hash indexes? When are they appropriate?

15. What is a function-based/expressional index?

Index Behavior & Performance Impact

1. How do indexes affect INSERT/UPDATE/DELETE performance?

2. What are the trade-offs of indexing? (storage vs performance)

3. What is index selectivity / cardinality and why does it matter?

4. What is index fragmentation and how do you fix it?

5. How does indexing affect sorting/ORDER BY operations?

6. What is an execution plan and how do indexes show up in it?

7. What is the purpose of statistics in indexing?

8. When might an index not be used even if it exists?

9. How does the WHERE clause influence index usage?

10. What is a covering vs non-covering index?

Practical & SQL-Level Index Questions

1. Write SQL to create an index.

2. How to drop an index?

3. How to check existing indexes on a table?

4. How to see if an index is being used (via EXPLAIN)?

5. How do you optimize a query using indexes?

6. Explain how to index for JOINs effectively.

7. What indexes help with range queries?

8. How would you index for GROUP BY or ORDER BY?

9. What is an index hint and when would you use it?

10. What is the result of duplicate index columns?

Advanced & System-Level Index Concepts

1. How does an index-organized table (IOT) differ from regular tables?

2. How do materialized views relate to indexing?

3. What is partial indexing (filtered indexes)?

4. How do partitioned indexes work and why use them?

5. How does indexing interact with transactions & locking?

6. What is RID Lookup, Key Lookup, or Bookmark Lookup?

7. How do B-trees work internally in indexes?

8. What are inverted indexes in NoSQL/search systems?

9. How do you index a JSON column (PostgreSQL/MySQL)?

10. What are spatial indexes and when are they used?

Scenario & Behavioral / Problem Solving Questions

1. How would you design indexes for a large transaction table?

2. What would you do if queries are slow despite indexes?

3. Describe a time you optimized a query using indexing.

4. How would you choose indexes given multiple heavy queries?

5. Explain indexing strategy for OLTP vs OLAP systems.

Tips for Answering These During Interviews

✔ Always relate your answer to performance impact and real use cases.

✔ Use practical examples like search, sorting, joins, aggregation, etc.

✔ When asked about trade-offs — mention read vs write cost, storage, maintenance.

✔ Use EXPLAIN/EXPLAIN PLAN in examples to show index usage.

system design