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.