CODEWITHSUNDEEP
mysqlsqldatabase
TheSalamov
TheSalamov

Reputation: 917

Non clustered index vs clustered

I think i am missing the point of why the clustered indices is faster than the non clustered ones , what is the benefit of having original records stored within the index vs having a pointer referencing those records ?

Upvotes: 0

Views: 90

Answers (1)

Oskari Mantere
Oskari Mantere

Reputation: 302

In a clustered index, the data will be stored within the index, together with the index keys. In a non-clustered index, the data will be stored in a heap file, and there will be a pointer from each index key to the heap file.

A clustered index is faster for reads because there is no need for following a pointer to the heap file, as the data is stored together with the key in the index.

Another benefit of clustered indices is that range queries will be fast, since the data will be physically sorted by the key.

Clustered indices can also be faster for updates, since updating a value in the heap file with a new value that has a larger size in memory requires moving the value into a different location in the heap. Then all indices pointing to the old location will have to be updated, or a forwarding pointer needs to be left behind in the old heap location.

Upvotes: 2

Related Questions