In the last blog post we understand how DB Index can improve performance. In this post we will go through Primary Index, Cluster Index.
In a table or a collection a Primary Index in an index on a set of keys that includes the unique primary key. For example, in a user table the id key is unique so database creates a primary index for that key by default. You can create primary index by yourself for key like, username, email etc.
Let’s create a user table.
In this table the id is the primary key so the id is set to primary index by default.
How index table look like,
The index table holds block reference and another one is the actual id of the user.
This is also called Sparse.
Cluster Index is an index that need to be sorted and the index value will be repeated to different rows of the database table. Let’s look at an example of it,
As you can see the Computer Science and Economics department is repeated. Doesn’t matter it is repeatable or not, in index table this will create a single row for Computer Science and rest of them. So the index table will be like this,
Our block and index table,
This is also called Dense.
In Cluster Index the actual value of index attribute can be repeated and thats why there are multiple reference to a single block from index table. This is why it is called Dense.