Indexing is one of the techniques used to optimize performance of a database by reducing the number of disk accesses that are required when a query is processed.
A database index is a data structure that is helpful to quickly locate and access the data in a database table.
Indexes are created using database columns.
The first column is the Search key which contains a copy of the primary key or candidate key of the table.
The second column is the data reference that contains a set of pointers which hold the address of the disk block where the key value can be found.
The structure of an index in the database management system (DBMS) is given below −
|Search key||Data reference|
The different types of index are as follows −
These types of indexes are listed below in the form of a chart −
Index Entry will be created only for distinct values in a database.
This is both a dense and sparse type example.
Index (Unique value) is created for each record in a data file which is a candidate key.
Secondary index is a type of dense index and also called a non clustering index.
Secondary mapping size will be small as the two level DB indexing is used.
Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.