Different Types of Database Indexes
Learn about different types of database indexes.
Primary key and candidate key
Before discussing the classification of database indexes, we need to understand two crucial terms:
Primary key
Secondary key
The primary key is a nonempty key that uniquely identifies a record on the disk. There can only be one primary key for a given table or collection in the database.
The secondary key is a search key that acts as a mechanism to filter records on the disk based on nonprimary keys. While the primary key resolves to a single record on the disk, the secondary key can resolve to multiple records and even no record on the disk.
Dense index versus sparse index
Database indexes are of two types:
Dense index
Sparse index
The dense index is a type of index where every record in the data file on disk has a corresponding search key in the index data structure that points to the data file. The data structure for the dense index can be made of ordered or unordered fields since every search key independently maps to an offset in the database file.
Get hands-on with 1200+ tech skills courses.