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;
- 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.
Contains a copy of the primary key of the table.
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.
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 clustered index?
In a clustered index, table records are sorted physically to match the 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.
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;