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
- 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.
- Clustered indexes store the data information and the data itself.
- There can only be one clustered index per 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.
Non-clustered Index
- 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.
- A non-clustered index always contains an index id>0.
- There can be one or more non-clustered indexes in a table.
- Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.
