Indexing in DBMS

Indexing is a technique for improving database efficiency by reducing the number of disk accesses necessary while a query is completed. It is a data structure strategy for fast locating and accessing data in a database. A few database columns are useful to generate indexes. The first column is the Search key, which contains a duplicate of the table’s main key or candidate key. The second column is the Data Reference or Pointer, which comprises a collection of pointers containing the address of the disk block containing that specific key value.

Indexing Methods in DBMS

Ordered Indices

To make searching easier, we frequently arrange the indices. Ordered indices are generally indices that have been sorted.

Primary Indexing

Primary indexing is when the index is built around the table’s primary key. These primary keys are one-of-a-kind for each record and include a 1:1 relationship between them. The searching operation is very efficient because main keys are there in sorted order. There are two types of main indexes: dense and sparse.
Dense Index- Every search key value in the data file has an index record in the dense index. It speeds up the search process. The number of records in the index table and the main table are the same in this case. It requires extra space to hold the index record. The search key and a link to the actual record on the disk are both present in the index records.
Sparse Index- Only a few items in the data file have index records. Each item designates a block. Instead of pointing to every item in the main database, the index in this case points to the records in the main table that are in a gap.

Clustering Index

An ordered data file is basically a clustered index. Non-primary key columns are sometimes useful to build indexes, also they may not be unique for each record. To make it easier to find the record, we’ll combine two or more columns to acquire the unique value and construct an index.

Secondary Indexing

The number of mappings increases as the table size grows in sparse indexing. These mappings are frequently there in primary memory to speed up address retrieval. Fetching the address becomes slower as the mapping size increases. Secondary indexing adds a new degree of indexing by storing the mapping and actual data in secondary memory (a hard disk).