Database index
A database index supports suitable lookups. It does not accelerate every query and adds storage and data-change overhead.
A database index is an additional data structure that helps a database find suitable records more directly. Whether it helps a query depends on factors including search conditions, data distribution and the chosen execution plan.
Example and cost
A hypothetical portal frequently searches by order number. A suitable index can support that lookup instead of scanning all orders each time. This does not imply that every view becomes faster.
The index needs storage and must be maintained when relevant data changes. Additional indexes therefore need evaluation against the actual workload. An ordinary index does not guarantee unique values either; that requires an appropriate uniqueness rule.
Sources
Related terms
- Database queryA database query requests data under defined conditions. Selection, result scope and ordering should fit the work being done.
- N+1 queryIn an N+1 pattern, a list query is followed by another fetch for each result. The structure matters, not merely a high call count.
- Object-relational mappingConnecting program objects to data in a relational database.
- PostgreSQLAn open-source object-relational database system for structured storage and querying of data.
- NoSQLAn umbrella term for database systems with different, not primarily relational data models.
- SQLA language for defining, querying and changing data.
Articles on this topic
Your project with ASES
Describe your project to us. We will get back to you with an initial assessment.