MySQL Clustered Index

Indexing is finding an unordered list into an ordered list, which makes fetching records faster and more convenient. This is possible by creating an entry for each value in the index columns.

Without indexing, it can be very tough to search the whole table to find the relevant information. Similar to the book index, MySQL indexing goes one together with each other.

• The clustered index is a table where the rows' data are stored.
• The clustered index defines the order of the table data based on key values that can be sorted one way.
• If a table contains a primary and unique key, then it is clustered index.
• MySQL allows us to create a clustered index named primary based on the column.

Pros:

• It helps minimize page transfer and maximizes cache hits.
• The best option for range or group with max, min, or count queries.
• It uses the location mechanism for finding index entries at the beginning of the range.

Cons:

• Has many insert records in non-sequential order, which can be a pain.
• It takes more time to update records.
• It needs extra queries to work to do it.

Clustered Index Outlook Example:

CREATE TABLE `chuninExam` (  
  `shinobis` int NOT NULL AUTO_INCREMENT,  
  `name` varchar(45) DEFAULT NULL,  
  `age` varchar(3) DEFAULT NULL,  
  `email` varchar(25) DEFAULT NULL,  
  PRIMARY KEY (`shinobis`), 
  UNIQUE KEY `email_UNIQUE` (`email`)  
)  

PRIMARY KEY here is a clustered index.