Indexing, Dense primary, Sparse, Clustered and secondary index in DBMS

By: Prof. Dr. Fazal Rehman | Last updated: March 3, 2022

Indexing, Dense primary, Sparse, Clustered and secondary index in DBMS

Today, in this fresh new article, we will cover the following topics;

1. Index
2. Indexing
3. Advantages of indexing
4. Disadvantages of indexing
5. Dense primary index
6. Sparse index
7. Clustered index
8. Secondary index

What is an index?

An index is a table and this table have only 2 columns. First Column: Contains a copy of the primary key of the table. Second Column: Contains a set of pointers that hold the address of the disk block where key value can be searched and found.

Advantages of indexing

There are many advantages of indexing. Some of the advantages are mentioned here.

1. Better performance of queries.
2. Fast searching from the database.
3. Fast retrieval of data.
4. Increase performance in SELECT query.

   Disadvantages of indexing

   • Indexing takes more space.
   • Decrease performance in INSERT, DELETE and UPDATE query.

When to create an index and when to not?

Create an index when it is really needed.

Types of Index

1. Primary index
2. Clustering index
3. Secondary index.

Primary Index In the primary index, there are two tables, first is Index table and another is the main database table

Both these two tables have 1 to 1 relationships. It means that one index in the index table is for every one record of the main database table.

Types of primary index:

There are two more famous types of primary index. Dense primary index. Sparse primary index.

What is a Dense primary index

Each record in the main table strictly has one entry in the index table.

What is Sparse Index:

When there are large database tables and if we use the dense index, then its size increases, so the solution to this problem is sparse index. According to sparse index, index points to records in the main tables in the form of group. For example, one sparse index can point to more than one records of the main database tables.

What is the clustered index?

In a clustered index, table records are sorted physically to match the index.

What is the Secondary Index?

The index table is stored in main memory and main memory is small in size, and the sparse index is also big in size, so we need to build the secondary index. Secondary index manages the index in multi-levels. Multi-level indexing is an advancement in the secondary matrix, and we use more and more levels in multi-level indexing.

What is the syntax to create an index in MySQL?

CREATE INDEX IndexName ON YourTableName(YourAttributeName);

What is syntax to show index in mysql?

SHOW INDEX FROM YourTableName;

What is the syntax to delete an index in MySQL?

DROP INDEX  IndexTitle ON TableName; Topic Covered Indexing, Dense primary, Sparse, Clustered and secondary index in DBMS.