AccentureDatabaseMedium
What are database indexes and when can they hurt performance?
DatabaseIndexingSQLQuery Optimization
Question
What is an index in a database? Explain how indexes improve reads and when too many indexes become a problem.
Interview Answer
Indexes speed up reads by avoiding full table scans, but they increase write cost and storage usage. Add indexes only for real query paths and validate impact using execution plans.
Explanation
An index is a search structure over one or more columns. It improves lookup and sort operations but must be maintained on writes. Too many or low-value indexes hurt performance and operational cost.
Key Points
- Indexes improve lookup/select performance
- Composite indexes must match query prefix usage
- Write-heavy systems pay index maintenance cost
- Use EXPLAIN plans to validate index utility
Common Mistakes
- Indexing every column blindly
- Ignoring index selectivity/cardinality
- Keeping duplicate or unused indexes
Likely Follow-Up Questions
- How do composite index leftmost-prefix rules work?
- What is covering index and when is it useful?
- How do you identify unused indexes in production?
Interview Timing
Target speaking time: about 4 minutes.