MySQL – Clustered Index

Clustered indexes contain the data for rows in a table. It defines the order of the table data based on key values that can be sorted only in one direction. In the database, each table may contain only one clustered index. Whenever a column in a relational database contains a primary key or unique key, MySQL allows you to create a clustered index named PRIMARY.

Example

Here is an example of how a clustered index is created in MySQL:

CREATE TABLE employee 
( post_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL,  
 CONSTRAINT Post_PK  
    PRIMARY KEY (user_id, post_id),    //clustered index  
 CONSTRAINT post_id_UQ  
    UNIQUE (post_id)               
) ENGINE = InnoDB ;  

Characteristics

The following are the essential characteristics of a clustered index:

• As a result, we are able to store both data and indexes together.
• Data is stored in only one way based on the key values.
• Key lookup.
• In addition, it supports index scans and index seeks.
• In clustered indexes, one or more columns are used to create the index.