MySQL – Clustered vs Non-Clustered Index

Here are some of the differences between clustered and non-clustered indexes:

Clustered Index

Definition: In clustered indexes, the data for the rows is stored in a table. MySQL automatically creates a clustered index named PRIMARY when a table column contains a primary key.

Use for: As a result, the record can be sorted and the index can be stored in physical memory.

Size: Its size is large.

Data Accessing: The data is accessed very quickly.

Storing Method: Records are stored in the leaf node of an index.

Additional Disk Space: There is no need for additional reports.

Type of Key: A clustered index is created using the primary key.

Contains in Table: There can only be one clustered index per table.

Index Id: Clustered indexes always contain an index id of 0.

Non-Clustered Index

Definition: Indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. Non-clustered indexes are also known as secondary indexes.

Use for: A logical ordering of data rows is created, and pointers are used to access the physical data files.

Size: Compared to a clustered index, its size is small.

Data Accessing: Compared to the clustered index, it has a slower access speed.

Storing Method: As it does not store records in the leaf node of an index, it requires additional storage space.

Additional Disk Space: The index must be stored separately, which requires additional space.

Type of Key: As a composite key, it is capable of working with unique constraints.

Contains in Table: There can be one or more non-clustered indexes in a table.

Index Id: There is always an index id greater than 0 in a non-clustered index.