INDEXING IN DATABASE | INDEXING IN DBMS | PRIMARY INDEX | SECONDARY INDEX | CLUSTERED INDEX

In this video, you will learn about Indexes in Database | Indexing in Database


What is Indexing in Database?

A database index is a DATA STRUCTURE that improves the SPEED OF DATA RETRIEVAL operations on a database table.

It is used to LOCATE AND ACCESS the data in a database table QUICKLY, this process is called as INDEXING.

Indexing is used to OPTIMIZE the performance of a database by MINIMIZING THE NUMBER OF DISK ACCESSES REQUIRED when a QUERY IS PROCESSED.

Indexes are created using a FEW DATABASE COLUMNS.
An INDEX is a small table having ONLY TWO COLUMNS.
The FIRST COLUMN comprises a copy of the PRIMARY OR CANDIDATE KEY of a table. Its SECOND COLUMN contains a SET OF POINTERS for holding the ADDRESS of the DISK BLOCK where that SPECIFIC KEY value stored.
----------------------------------------------------------------------------------------------------------------
PRIMARY INDEX: Defined on an ordered data file, which is based on a PRIMARY KEY field of the relation.

DENSE INDEX: Stores ALL SEARCH-KEY values, needs MORE SPACE, makes SEARCHING FASTER.

SPARSE INDEX:Stores only some SEARCH-KEY values, needs LESS SPACE, LESS MAINTENANCE overhead

-------------------------------------------------------------------------------------------------------------------

CLUSTERED INDEX: Defined on an ordered data file, which is based on a NON-KEY field.
Mostly clustered Index will have COMBINATION OF NON KEY ATTRIBUTE ALONG WITH CANDIDATE KEY of the tables.
Clustered Index is a SPARSE INDEX TYPE, where only ONE SEARCH KEY VALUE per block.

-----------------------------------------------------------------------------------------------------------------

SECONDARY INDEX: Defined on an un-ordered data file, generated from a CANDIDATE KEY of the relation
Data searching requires MORE TIME as compared to the clustered index
Secondary Index is of DENSE INDEX type, where ALL SEARCH KEY VALUE pointing to specific block