Indexing is a method to get the requested data very fast. There are mainly two types of indexes in SQL, clustered index and non-clustered index. The differences between these two indexes are very important from an SQL performance perspective. The following comparison chart explains their main differences:
|
Clustered Index |
Non-Clustered Index |
|
A clustered index is a table or view 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) are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes. |
|
Clustered indexes store the data information and the data itself. |
Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data. |
|
There can only be one clustered index per table. |
There can be one or more non-clustered indexes in a table. |
|
A clustered index determines how data is stored physically in the table. Therefore, reading from a clustered index is faster. |
It creates a logical ordering of data rows and uses pointers for accessing the physical data files. Therefore, reading from a clustered index is slower. |
|
A clustered index always contains an index id of 0. |
A non-clustered index always contains an index id>0. |
Answers (0)