Indexes in SQL

What are Indexes in SQL? 

Indexes in SQL are like the "Index" page in a book. They hold the information about the various chapters present in the book along with their page numbers. Whenever we need to go to a particular chapter, we look for that chapter in Index, take its page number and directly jump to that page. 

Similarly, in databases also, we can think of the tables as huge books, with each row as a new chapter. The tables in databases can be enormous with lakhs and lakhs of rows and finding a particular row as per our need not only becomes hard but also impossible after an extend. Thus, to overcome this issue a simple concept of Indexing is used. These indexes help us in finding the exact row within a matter of seconds no matter how huge our table is. 

Thus, using a simple concept taken from our books to our database, we can significantly reduce the time a query takes to get the desired result set. 

Why Indexing is important?

I know a lot of us might still be saying  "Indexes in my book..!! I hardly look at them."  "What indexes...  ??? No one opens that page... we use bookmarks -- they are cool and everyone loves them." 

I would like to say... Yes, guys, you are actually right... But, the book that you or I am reading might not be more than 1000 pages, so even if we turn the pages, it will hardly take us few seconds to reach our desired page. In the case of databases, having a mere 1 lakh records will take more than a minute to find the record.

Still not convinced how a minute to get a record can impact us..!!! Let's take a simple example... We all have a social media account, and that same social media has millions of other users also. Now like we said suppose for 1 lakh records it took 1 minute. So, just imagine for 1 million users, it will take 10 minutes. Way too much, that much time a user has to wait for its login. 

Thus, indexing is very very important. No one wants to wait for a single minute in life, everybody wants it with a click. It is possible only when we can improve our queries and databases.  A small step in this is 'Indexing'. 

Types of Indexes

There are two types of indexes in databases:- 

1. Clustered

2. Non - Clustered

*NOTE:- The concept/names and code may vary from the version of SQL we are using. Thus, we need to pay special attention on the syntax and code while using it.
We will be using MySQL implementation of SQL.  MySQL does not have any special privileges for Clustered or Non-Clustered index, rather Primary Key is treated as Clustered Index and all the other indexes are called as Secondary Index. 

Apart from this. one more thing that we need to pay attention is the 'storage engine'. The concept of Clustered & Non-Clustered Index is limited to 'InnoDB' storage engine only.  

1. Clustered:- 

It defines how the data is physically stored in the table. It is usually created by default on defining the Primary Key for the table, thus we can have only one(1) clustered index per table. 

To explain the concept more clearly, let's link it to our book-index example and understand it better. 

Just like each book has only an index page attached to it similarly, in the case of the clustered index we have only a single index per table. No doubt as we can create our own index for the book(a funky and as per our use) similarly, we can also create our own index for the table(depending on table structure). 

I Hope, we are clear with the concept of Clustered Index. Now let's try to see a practical example for the same. 

a. Create Table

CREATE TABLE `index_demo` (

  `id` INT NULL,

  `col1` VARCHAR(45) NULL,

  `col2` VARCHAR(45) NULL,

  `col3` VARCHAR(45) NULL,

  `col4` VARCHAR(45) NULL,

  `col5` VARCHAR(45) NULL

  );

Creating Dummy Table

b. Creating PRIMARY KEY / Clustered Index 

ALTER TABLE index_demo

ADD PRIMARY KEY (id,col4);  #Clustered Index

SHOW INDEX  FROM index_demo;

Creating Clustered Index

2. Non- Clustered:- 

A non-Clustered index can be thought of as a chart displaying which categories of books are kept in which section, i.e. it is the index that is not stored together with our table and is stored somewhere else in the database. The index contains column values on which the index is created and the address of the record that the column value belongs to. 

This process indeed makes the querying a bit slow as compared to clustered indexing. Also, at the same time, it gives us the advantage of having more than 1 index at a time. This type of indexing method helps us to improve the performance of queries that use keys that are not assigned as primary keys. 

Time to have a look at the practical end also.

Creating Non-Clustered Index

We can see, we have created both the Clustered as well as Non-CLustered index in our Dummy table. But if we notice here, by default Primary Key/Clustered Index are given the higher priority than the Non-Clustered/Secondary Index. 

Also, another thing we notice here is that Index_Type is shown as 'BTREE', that is a huge topic and beyond the scope of this article. We will be bringing up a separate article for same soon. By the time, we can understand it as a data structure and all the indexes are stored in the form of BTREE only. 

Summary

We studied about an important concept of indexing and various type of indexes present in SQL. We not only saw the theoretical part of it but also the practical aspects involved with it. 

So don't just read, practice along with us and let us know the issues and challenges if you face any.