MySQL Clustered Index

An index is a separate data structure that allows us to add indexes in the existing table. It enables you to improve the faster retrieval of records on a database table. It creates an entry for each value of the indexed columns.

A clustered index is actually a table where the data for the rows are stored. It defines the order of the table data based on the key values that can be sorted in only one way. In the database, each table can have only one clustered index. In a relational database, if the table column contains a primary key or unique key, MySQL allows you to create a clustered index named PRIMARY based on that specific column.

Characteristics

The essential characteristics of a clustered index are as follows:

It helps us to store data and indexes at the same time.
It stores data in only one way based on the key values.
Key lookup.
They are scan and index seek.
Clustered index always use one or more column for creating an index.

Advantages

The main advantages of the clustered index are as follows:

It helps us to maximize the cache hits and minimizes the page transfer.
It is an ideal option for range or group with max, min, and count queries.
At the start of the range, it uses a location mechanism for finding an index entry.

Disadvantages

The main disadvantages of the clustered index are as follows:

It contains many insert records in a non-sequential order.
It creates many constant page splits like data pages or index pages.
It always takes a long time to update the records.
It needs extra work for SQL queries, such as insert, updates, and deletes.

Clustered Index on InnoDB Tables

MySQL InnoDB table must have a clustered index. The InnoDB table uses a clustered index for optimizing the speed of most common lookups and DML (Data Manipulation Language) operations like INSERT, UPDATE, and DELETE command.

When the primary key is defined in an InnoDB table, MySQL always uses it as a clustered index named PRIMARY. If the table does not contain a primary key column, MySQL searches for the unique key. In the unique key, all columns are NOT NULL and use it as a clustered index. Sometimes, the table does not have a primary key nor unique key, then MySQL internally creates hidden clustered index GEN_CLUST_INDEX that contains the values of row id. Thus, there is only one clustered index in the InnoDB table.

The indexes other than the PRIMARY Indexes (clustered indexes) are known as a secondary index or non-clustered indexes. In the MySQL InnoDB tables, every record of the non-clustered index has primary key columns for both row and columns. MySQL uses this primary key value for searching a row in the clustered index or secondary index.

Example

In the below statement, the PRIMARY KEY is a clustered index.

snippet

CREATE TABLE `student_info` (
  `studentid` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `age` varchar(3) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`studentid`), //clustered index
  UNIQUE KEY `email_UNIQUE` (`email`)
)