How SQL Index Works | SQL Index Part 2

How SQL Index Work is a question that requires an answer which is creative , imaginative & precise in it’s nature. SQL Index is one of the most important and famous tool in the world of SQL developers. Developing it’s working mechanism and behaviour will help you in deciding whether it’s going to complete your requirements or not.

Read More : SQL Index Tutorial

SQL Index works in SQL engine exactly the way your book index works. Ever see those pages at the end of book with terms written in book with page number . NO? Have a look below then :

How SQL Index Work
index

SQL INDEX WORKING EXPLAINED :

Consider this , using above image you’re looking for advertising word in a book . You have two options:

  • Read word-by-word all the pages and go through all the book and look for the word and then study about it. Boring and Time Taking Right?
  • Use the Index section of book and you will immediately find the term with the page number on which they are written. Advertising word is on 434, 439-449. Sounds Smart!

This is exactly how SQL engine will work when it has to look for data in tables. See below Query for a University Database with more than 90,000 students over the 50 years of data :

SELECT  LASTNAME , CITY ,ADDRESS
FROM    STUDENT
WHERE CITY='LAHORE' AND LASTNAME='ALI'

SQL engine will instantly pick the query , go into student table start looking for the lastname=’ALI’ who lives/lived in Lahore over last 50 years.

Since , computers are fast it will go through the data very quickly but still it will be expensive query because the table will go through whole Locking mechanism and this query will look into whole table and evaluate the data with given conditions and no index in place. In a real-time envoirnment , where we can expect multiple users to use the database for different operations. Such things can do more harm than good.

Let’s make our SQL table for STUDENT smart and create a index on LASTNAME column

CREATE INDEX LastNameIndex ON STUDENT(LastName) ;

When you will create index. A separate table will be created which will store reference values for data and label it with a value.

Now when you execute the query

SELECT  LASTNAME , CITY ,ADDRESS
FROM    STUDENT
WHERE CITY='LAHORE' AND LASTNAME='ALI'

SQL Engine will use the LastNameIndex , out of 90,000 rows it will simply use index pull of the records with ‘ALI’ label and start throwing addresses with City=’LAHORE’. For example ; the resulting data set could be 15,000 student with name ‘ALI’ and out of which only 5000 are living in LAHORE.

LastNameIndex will cut through 90,000 rows straightaway to 15,000 and then match with city. This is much more economical query then the one above.

We are not discussing the selection of Index in this article. That is entirely a huge debate. For the sake of understanding we will keep things simple and go wit it. In general , at conceptual level this is how SQL Index Work.

DIFFERENCE BETWEEN INDEX AND PRIMARY KEY

Read About : Primary Key In DBMS

Do not mix an index with a primary key. Behaviour of an index and primary key at surface level some time appears as same and people tend to avoid indexes because of this.

Primary Key ensures uniqueness in the table . An SQL index doesn’t do that . When you run the query and placing Primary key In WHERE clause will help you to a level that data will be unique but it will be nonetheless a full table scan.
However , an index will use the labelled-address of the given condition and goes through search in much faster way.

Leave a Reply