Improve the performance by using Indexing in SQL

Index in SQL

have you ever faced slowness while fetching the records in the SQL. If yes, then you are at the right place 😉. First let’s understand the reason behind it.

So at the beginning of any project each table has small amounts of data and when we retrieve the data we don’t feel slowness. But when the project is very big and most of the tables store thousands of records then query execution will be slow if the table is not indexed. Because of indexing is not used in the table SQL will visit all the rows and and will retrieve the matching records.

Index:-

Index in SQL is same like index of any book where the topic and the page number is given so that we don’t have to search all the pages to read about that particular topic.

Types Of Index :-

There are two types of index in SQL i.e. Clustered Index and Non-Clustered Index.

Clustered index defines the order in which data will be stored in table. Only one clustered index allowed per table as it defines the order of the table storage. Primary Key constraint automatically creates one clustered in for the table on that particular column.

Example:-

CREATE CLUSTERED INDEX studentIDX ON STUDENTS(StudentID DESC);

2) Non-Clustered Index:-

Non-Clustered Index does not define the order of the data. It can have multiple columns in it. SQL stores the Non-clustered indexes in the table format in the pages where last column points to the page number of the clustered index if it is present else it points directly to the table page number.

Now let’s check how performance is getting improved by using Indexing on table.

If we don’t use any index in the table then SQL will visit all the records row by row hence execution time will get increased if records are in the thousands or more than that.

As in given Screenshot it is visible that in “Number of Rows Read” parameter value is same as the number of records present in the table.

If we use clustered index then execution time will get decreased.

As in the given Screenshot it is visible that in “Number of Rows Read” parameter value is 1 and execution time has also decrease.

Bhagavad Gita Verse:-

Knowing this truth, even seekers of liberation in ancient times performed actions. Therefore, following the footsteps of those ancient sages, you too should perform your duty.

--

--

--

love ❤ coding, solving some industry problems technologies: JavaScript, C#, Angular, PLSQL, Docker Want to learn: Python, Go language, AI, ML and Cloud

Love podcasts or audiobooks? Learn on the go with our new app.

Crypto incentives for code reviews

How do we automate back up of our WordPress VM to the AWS S3? (Part 1)

How WatchMyDC helps to export VMware virtual machines in AWS and manage incidents.

Alert and Monitoring with Grafana

Task 7.1-(C)✍️: Automating the LVM partition using python script.

Understanding the AMAZON ELASTIC COMPUTE CLOUD

Integrate the Scene detection feature using Huawei HiAI Engine in Android (Kotlin)

Scope Creep IRL

Spring boot Scaffolding with Docker.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vishal Pathak

Vishal Pathak

love ❤ coding, solving some industry problems technologies: JavaScript, C#, Angular, PLSQL, Docker Want to learn: Python, Go language, AI, ML and Cloud

More from Medium

Triggers in SQL and What is DML Trigger

Open-source SPL that can execute SQL without RDB

Intelligent Oracle SQL Commands