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:
Let us first discuss clustered and non-clustered indexing in brief.
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.
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 ;Following are the essential characteristics of a 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.
//It will create non-clustered index CREATE NonClustered INDEX index_name ON table_name (column_name ASC);
Following are the essential characteristics of a 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. |
