Clustered vs Non-Clustered Index
Clustered vs. 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. This article will explain the most common differences between them.
Clustered index
A clustered index is a table where the data for the rows are stored. This index assigns a primary query for unique or primary columns. The cluster table is working on the table using a key. Each table can contain only one clustered index. The primary key helps to display clustered index as per order.
Characteristic of the clustered index
- The clustered index provides sorted data.
- You use either single or multiple columns to assign as an index.
- MySQL system stores index, column, and table data simultaneously.
- It supports fragmentation about the index.
- Cluster index operations support index scan and index seek.
Non-Clustered index
The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes.This index query assigns a secondary query for other columns except for the primary column. The non-clustered index stores data in one memory location and specify another index location.The multiple non-clustered indexes can be stored in a single table.The non-clustered index does not show in order.
Characteristic of the non-clustered index
- The non-clustered index stores key values.
- It supports secondary data columns.
- The non-clustered index operations support index scan and index seek.
- It supports to bridge for the table data.
Comparison between clustered and non clustered index
The following table helps to know the differences between the clustered and non-clustered indexes.
clustered index | Non clustered |
The clustered index is called a primary index. | The non-clustered index is called a secondary index. |
The clustered index displays column data in order. | The non-clustered index does not display data in order. |
The clustered index sorts data or columns row using its key value. | The non-clustered index stores data in one particular location and indicates another location. |
The clustered index uses a leaf node to store the data page. | The non-clustered index does not use a leaf node to store the index. |
This index does not need extra disk space. | This index needs extra disk space. |
The clustered index accesses data faster. | The non-clustered index accesses data slower. |
The clustered index works on the primary key. | The non-clustered index works on unique constraints but not a primary key. |
A clustered index always contains an index id of 0. | A non-clustered index always contains an index id>0. |
The main feature of a clustered index is an improved speed and performance of the data. | The main feature of the non-clustered index is to work on columns that are used in joins. |