Database Index
Subject: Computer and Technology noun /ˈdeɪ.təˌbeɪs ˈɪn.dɛks/ 1 definition
- database-systems
- data-structures
- query-optimization
- data-retrieval
-
Definition (noun)
Entry #4 · UpdatedA Database Index is an auxiliary data structure maintained alongside a database table or collection to locate records quickly without scanning every row sequentially. By storing sorted keys or lookup mappings paired with pointers to physical storage locations, it substantially reduces read latency for filtered queries, joins, and sort operations, while introducing extra disk usage and maintenance overhead during inserts, updates, and deletes.
Common Types
B-Tree Index: A self-balancing tree structure that keeps keys in sorted order, supporting efficient equality checks, prefix matching, and range queries.
Hash Index: A lookup structure that maps key values through a hash function to bucket locations, delivering near-constant-time exact matches without supporting range scans.
Clustered Index: An index that dictates the physical storage order of table rows on disk, limiting a table to one clustered index while accelerating sequential range reads.
Composite Index: A multi-column index built across two or more fields in a defined order, optimizing queries that filter or sort on the leading column prefix.
Example
In an e-commerce database with 15 million rows in its
orderstable, looking up a shopper's recent purchases bycustomer_idinitially triggers a full table scan that takes several seconds. Adding a B-tree index oncustomer_idallows the query planner to traverse only a few index levels and retrieve the matching records in under five milliseconds.