Clustered Index vs Non Clustered Index —
Clustered Index — In clustered index, the rows of a table are maintained in sorted order like a dictionary or a telephone directory. A table can have only one clustered index which is usually a primary key of the table.
In MySQL database, whenever we define a primary key, the InnoDB engine uses it as the clustered index itself. For a clustered index, the column should have unique value in all the rows.
Note — In the absence of primary key, InnoDB takes the first UNIQUE index column with NOT NULL as the clustered index.
In a clustered index, no separate index storage is created because data is already maintained in sorted order in the table itself. Let’s take a look —
create table students
(
id int primary key,
name varchar(50),
class varchar(50)
);insert into students (id, name, class) values (29, 'Sam', '5th');
insert into students (id, name, class) values (23, 'Alex', '12th');
insert into students (id, name, class) values (25, 'Pierre', '8th');
insert into students (id, name, class) values (31, 'Max', '2nd');
insert into students (id, name, class) values (30, 'Lewis', '1st');
Select query shows that the records are stored in sorted order on “id” which is the primary key and is acting as the clustered index of the table.
Non Clustered Index — In non clustered index, rows of the table are not maintained in sorted order and a table can have more than one non clustered index. Here the index is stored in a separate space so for non clustered indexes extra space is required.
A non clustered index holds the columns on which it is applied and the reference address of the actual record in the table. A non clustered index is like the table of contents of a book.
create table students
(
id int,
name varchar(50),
class varchar(50)
);insert into students (id, name, class) values (29, 'Sam', '5th');
insert into students (id, name, class) values (23, 'Alex', '12th');
insert into students (id, name, class) values (25, 'Pierre', '8th');
insert into students (id, name, class) values (31, 'Max', '2nd');
insert into students (id, name, class) values (30, 'Lewis', '1st');
This time the records are not in sorted order as we have not created any primary key, hence no clustered index is present. Now let’s create an index on “name”.
Now even after creating index we can see that records are not stored in sorted order, this shows that in case of non clustered indexes, data is not sorted physically in the table and hence an extra space will be required by the index and this will store the column “name” and the reference to where each record points.
References —
