An index in a database is a data structure that improves the speed of data retrieval operations on a table. By creating an index on one or more columns, the database can quickly locate the rows that match a query, significantly reducing the amount of data it needs to scan.
In depth
Indexes function similarly to an index in a book, allowing the database to find relevant data without scanning every row in a table. By maintaining a separate structure that holds the indexed columns and pointers to the actual data, the database can perform queries more efficiently. However, while indexes speed up read operations, they can slow down write operations because the index must be maintained when records are inserted, updated, or deleted. Thus, it's essential to find a balance between read and write performance when deciding which indexes to create. It's also important to consider the selectivity of the indexed columns; high-selectivity columns often yield better performance improvements.
Example scenario
In a retail application, a company tracks its sales data in a large database. By adding an index on the 'product_id' column, the application can quickly retrieve sales records for specific products without scanning the entire sales table. When a report is generated for sales data over the last month, this index allows the query to return results in seconds, which is critical for timely decision-making and reporting.
Common mistakes
A common mistake developers make is over-indexing tables, which can lead to increased storage requirements and slower write performance. They may create indexes on every column that is frequently queried instead of analyzing the most critical queries to optimize. Another mistake is failing to consider composite indexes, which can be more efficient than multiple single-column indexes when queries involve multiple columns. This can lead to suboptimal query execution plans and longer response times.
Follow-up questions
- What are the trade-offs between read and write performance when using indexes?
- Can you describe a situation where an index might not be beneficial?
- How would you determine which columns to index in a large table?
- What tools or methods can help analyze query performance?
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.