What are indexes in SQL and Why do we need them?

Introduction

Imagine you are holding a book and you need to read about a particular topic inside the book. You can scan each and every page in the book until you reach the desired topic you want to read about. This approach is time consuming and tiresome. A better way would be to open the index of the book and check the page number of the topic you would like to read about. This is much faster and efficient way to access the content without scanning through each and every page!

Indexes in the SQL accomplish the same thing. Data in SQL is stored in terms of records inside the table. Here the table is analogous to the book which we are trying to read and the record are the pages. If we have additional information such as indexes in the table we can directly jump to certain section of the table and start exploring for our required data.

In short, Indexes are created to improve the performance of Queries, for faster execution by reducing the time to read and access.

In this blog you are going to learn about.

  • Types of Indexes
  • How to Create and Delete Indexes
  • Things to keep in mind while creating indexes

If you like the article, please subscribe and share it with others.

Types of Indexes

There are primarily 2 types of indexes

  1. Clustered Index
  2. Non-Clustered Index

Clustered Index : This type of index physically modifies structure of a table. There could be only 1 clustered index per table.

Let’s take an example to better understand the topic.

Inside a table in SQL the rows are stored randomly. Suppose there is a shopping list table which contains 1000 records. Each record contains 3 fields: Name, Type, Count

First few records in the table are

  1. T-shirt, Clothing, 3
  2. Mango, Food, 12
  3. Cap, Clothing, 2
  4. Apple, Food, 4

…1000 more records

To find whether banana is present in the table we will have to scan the entire table. It would be much better if we keep the table organized by grouping items such as clothing, food, electronics, etc together. In this way we could directly jump to particular section such as food in our example of banana and try to search for the value.

Clustered index achieves exactly the same result. It physically modifies the structure of the table by grouping items similar in a single column. Clustered index is automatically created by SQL on the primary key of the table.

Non-Clustered Index: They are pointer to the specific column in the database. There could be multiple non-clustered index on a table (max 999 non-clustered index allowed).

They take additional memory space since the SQL engine has to maintain additional hashmap for every non clustered index created.

You can also create a non-clustered index by combining multiple keys.

Usually try to create an index on columns that are present in the WHERE clause, JOIN clause and ORDERBY clause.

These are implemented using B and B+ trees behind the scenes.

How to create and delete Indexes

General syntax for creating index on the table where duplicate values are allowed.

Example code for creating index on single column and multicolumns

Example code for deleting index

Things to keep in mind while creating indexes

Creating an index speeds up the time to read the data from the database. But there are few things we should consider while creating indexes in SQL.

  1. Index takes extra space and needs to be maintained. You need to make sure that the disk has enough space for index to be created.
  2. Every time a record is inserted to the table it needs to be updated in the index which could be expensive. So if your database is write heavy database then creating indexes could slow down the performance.
  3. If your table is small (contains less data) then it’s not advisable to create index.