How a database index avoids checking every row
A database index stores selected values in a structure designed for efficient lookup. Instead of examining every row to find a matching email address, the database may use the index to narrow the search. This can speed reads, but the extra structure consumes storage and must be maintained when relevant data changes.
Go deeper
Build another structure for finding data
A table stores records, but its storage order may not suit every search. An index creates an additional structure organised around selected columns or expressions.
For a query that fits the index, the database can navigate towards matching entries and then retrieve the needed rows. The exact process depends on the index type and database design.
An index trades maintenance and storage for faster access in suitable cases. It does not make every query automatically faster.
- Inserts may require new index entries.
- Updates can require existing entries to change.
- Queries returning much of a table may gain little from an index.
The database's query planner decides how to access the data. A useful index matches the way records are searched, filtered or ordered, rather than simply being added to every available column.