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.