A database index is a data structure that improves the speed of data retrieval operations on a database table. It allows the database to find and access records more efficiently, significantly reducing query execution time especially for large datasets.
In depth
Indexes work similarly to an index in a book, which helps you locate information quickly without having to read every page. When a database query is executed, the database engine can use the index to find relevant records without scanning the entire table. This is particularly beneficial for operations like searching, filtering, and sorting data. However, it's important to note that while indexes speed up read operations, they can slow down write operations, as the index also needs to be updated when data is modified. Therefore, careful consideration should be given to which columns should be indexed, balancing read and write performance needs.
Example scenario
In an e-commerce application, suppose querying the 'products' table for items by category is a common operation. Without an index on the category column, the database would have to scan all rows in the table every time a user searches for products in a certain category, leading to slow response times. By creating an index on the category column, the database can quickly locate the rows that match the queried category, significantly improving performance and user experience.
Common mistakes
A common mistake is over-indexing, where developers create too many indexes, which can lead to increased overhead on write operations like INSERTs and UPDATEs due to the need for the indexes to be maintained consistently. Another mistake is not considering the query patterns when designing indexes; for instance, indexing a column that is rarely used in queries does not provide any benefit. This can lead to wasted storage and maintenance resources without improving performance.
Follow-up questions
- What types of indexing strategies are there?
- Can you explain how a composite index works?
- How would you determine which columns to index?
- What impact does indexing have on database storage requirements?
Preparing for a system design interview?
- A 7-step method for answering any system design question, plus estimation numbers
- 5 worked designs: URL shortener, rate limiter, chat, news feed and file storage
- Guides to caching, databases, queues, consistency and reliability, with a 4-week study plan
- 306 practice questions with answers on caching, databases, queues, microservices, APIs and more
5 worked designs, a 4-week plan and 306 practice questions with answers.
The material stays free to read online. This is the organised, offline, print-ready PDF edition. Secure payment by PayPal; your personal download link is emailed after payment is confirmed. Digital product, no shipping.