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.