ntent/-uKlwjrxc" alt="Index Structure">

The search key is the first column in the database and it contains a duplicate or replica of the table's candidate key or primary key. The primary key values are saved in sorted order so that the relevant data is easily accessible.

The data reference is the database’s second column. It comprises a collection of pointers that point to the disk block where the value of a certain key is stored.

Purpose of Indexing

The main purpose of indexing is to improve the speed of data retrieval operations by reducing the number of disk I/O operations. It helps in:

Faster Search Operations: Indexes allow quick location of data without scanning the entire table.
Efficient Query Processing: Enhances the performance of queries involving SELECT, JOIN, and WHERE clauses.
Improved Sorting: Helps in sorting operations, as indexes maintain the order of the indexed columns.

Types of Indexes

Primary Index

A primary index is created on the primary key of a table. It uniquely identifies each record in the table. The searching operation is very efficient since primary keys are stored in sorted order.

There are two types of primary indexes:

Sparse Index

Contains index records for only some of the values. A sparse index points to each block in the data file.

Example: If a table has 1000 records, a sparse index might have entries for every 100th record, pointing to the block where the record starts.

Dense Index

Contains an index record for every search key value in the data file, pointing to the actual data record. It needs more space to store the index record itself.

Example: For a table with 1000 records, a dense index will have 1000 entries, each pointing to the corresponding data record.

Secondary Index

When sparse indexing is used, the mapping expands in parallel with the table's size. Secondary indexing adds a new level of indexing to reduce the size of the mapping.

This method begins with selecting the huge range for the columns, resulting in a minimal mapping size at the first level. The ranges are then broken into smaller groups.

Clustered Index (Clustering Index)

A clustered index can be defined as an ordered data file. To identify the record faster, we will group two or more columns to get the unique value and create an index out of them.

Example: For a university database, a clustered index can be created to identify groups of records of students in the same semester or branch. Here Branch_ID would be a non-unique key.

Ordered Index

Ordered indices are indices that have been sorted. To make searching easier and faster, the indices are frequently arranged/sorted.

Example: In the case of a university database with thousands of student records, if we need to retrieve the record of the student with ID 378, the DBMS would read the record after it reads 378*2 = 756 bytes using an ordered index, which is significantly less than searching through the entire database.

Considerations in Indexing

The most important columns for indexing are selected based on how frequently they are used and the sorts of queries they are subjected to. Regular index maintenance, such