Index, Indexing, advantages of indexing, disadvantages of indexing, Dense primary index, Sparse index, Clustered index, the secondary index in database systems

Index, Indexing, advantages of indexing, disadvantages of indexing, Dense primary index, Sparse index, Clustered index, the secondary index in database systems

In this tutorial, we will learn about the followings;

  • Index
  • Indexing
  • Advantages of indexing
  • Disadvantages of indexing
  • Dense primary index
  • Sparse index
  • Clustered index
  • 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.

  • Better performance of queries.
  • Fast searching from the database.
  • Fast retrieval of data.
  • 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 index when it is really needed.

Types of Index

  • Primary index
  • Clustering index
  • 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.

Primary indexing database
Figure: Dense Primary index

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.

sparse indexing database
Figure: Sparse primary index

What is clustered index?

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

database indexing
Figure: Clustering index

What is 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.

database tutorials
Figure: Secondary index

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;

Welcome to all friends. The reason for our success is only your love for T4Tutorials. Our team is always available to answer your queries regarding any kind of confusions or discussion regarding your study and career matters. For discussion with us please join our facebook group "T4Tutorials.com". The link of the group is mentioned below.Thanks and love to all for connecting with us. We are nothing without you. Love you all.....

Leave a Reply