Difference between MySQL Clustered and Non-Clustered Index

The difference between clustered and non-clustered index is the most famous question in the database related interviews. Both indexes have the same physical structure and are stored as a BTREE structure in the MySQL server database. In this section, we are going to explain the most popular differences between them.

Indexing in MySQL is a process that helps us to return the requested data from the table very fast. If the table does not have an index, it scans the whole table for the requested data. MySQL allows two different types of Indexing:

1. Clustered Index
2. Non-Clustered Index

Let us first discuss clustered and non-clustered indexing in brief.

What is a Clustered Index?

A clustered index is 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 direction. In the database, each table can contains 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.

Example

The following example explains how the clustered index created in MySQL:

CREATE TABLE Student  

( 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

Following are the essential characteristics of a clustered index:

• It enables us to store data and indexes together.
• It stores data in only one way based on the key values.
• Key lookup.
• It support index scan and index seek data operations.
• Clustered index always use one or more column for creating an index.

What is a Non-Clustered Index?

The indexes other than PRIMARY indexes (clustered indexes) called a non-clustered index. The non-clustered indexes are also known as secondary indexes. The non-clustered index and table data are both stored in different places. It is not able to sort (ordering) the table data. The non-clustered indexing is the same as a book where the content is written in one place, and the index is at a different place. MySQL allows a table to store one or more than one non-clustered index. The non-clustered indexing improves the performance of the queries which uses keys without assigning primary key.

Example

//It will create non-clustered index  

CREATE NonClustered INDEX index_name ON table_name (column_name ASC);

Characteristics

Following are the essential characteristics of a non-clustered index:

• It stores only key values.
• It allows accessing secondary data that has pointers to the physical rows.
• It helps in the operation of an index scan and seeks.
• A table can contain one or more than one non-clustered index.
• The non-clustered index row stores the value of a non-clustered key and row locator.

Clustered VS Non-Clustered Index

Let us see some of the popular differences between clustered and non-clustered indexes through the tabular form:

Parameter	Clustered Index	Non-Clustered Index
Definition	A clustered index is a table where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY.	The indexes other than PRIMARY indexes (clustered indexes) called a non-clustered index. The non-clustered indexes are also known as secondary indexes.
Use for	It can be used to sort the record and store the index in physical memory.	It creates a logical ordering of data rows and uses pointers for accessing the physical data files.
Size	Its size is large.	Its size is small in comparison to a clustered index.
Data Accessing	It accesses the data very fast.	It has slower accessing power in comparison to the clustered index.
Storing Method	It stores records in the leaf node of an index.	It does not store records in the leaf node of an index that means it takes extra space for data.
Additional Disk Space	It does not require additional reports.	It requires an additional space to store the index separately.
Type of Key	It uses the primary key as a clustered index.	It can work with unique constraints that act as a composite key.
Contains in Table	A table can only one clustered index.	A table can contain one or more than a non-clustered index.
Index Id	A clustered index always contains an index id of 0.	A non-clustered index always contains an index id>0.