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:
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.
The following example explains how the clustered index created in MySQL:
Following are the essential characteristics of a clustered 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.
Following are the essential characteristics of a non-clustered index:
Clustered VS Non-Clustered Index
Let us see some of the popular differences between clustered and non-clustered indexes through the tabular form: