Databases & Data Storage
Index
Definition
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
Why It Matters
Indexes are crucial for database performance. Without an index, the database would have to scan every single row in a table to find the data you're looking for (a "full table scan"). With an index, it can find the data much more quickly, similar to using the index in the back of a book.
Contextual Example
If you frequently search for users by their email address, you would create an index on the `Email` column of the `Users` table. This makes queries like `SELECT * FROM Users WHERE Email = '...'` dramatically faster.
Common Misunderstandings
- Primary keys are automatically indexed.
- While indexes speed up reads, they slow down writes (inserts, updates, deletes) because the index also needs to be updated. You shouldn't index every column, only those used frequently in queries.