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.
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 ;
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.