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.
The essential characteristics of a clustered index are as follows:
The main advantages of the clustered index are as follows:
The main disadvantages of the clustered index are as follows:
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.
In the below statement, the PRIMARY KEY is a clustered index.
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`) )