What Is SQL Index | How to Index SQL Database

SQL index is a distinct structure in the database that is built using the CREATE INDEX statement. Mostly , DBAs love & hate SQL indexes at the same time due to the tricky nature they posses. They can either speed up your databases or slow it down , depending on how correctly you have implemented them. People familiar with programming languages understand the behaviour of pointers very well. An index is the pointer in database.

WHAT IS SQL INDEX

As said , SQL indexes are a distinct data structure such as B+tree or balanced tree where reference to the original data is stored with the keys.  It requires its own disk space.

Creating an index does not change the table data; it just creates a new data structure that refers to the table. You can also put it as SQL Index are special lookup tables that the database search engine can use to speed up data retrieval.

SQL INDEX CREATE Command

Syntax to create SQL Index is as follows :

CREATE INDEX index_name ON table_name;

You define index on columns and you can do it two ways

*Single-Column Indexes

Declare index on one column only :

CREATE INDEX index_name ON table_name (column1) ;

*Composite Indexes ( Multiple Columns)

Declare index on more than one column

 CREATE INDEX index_name ON table_name (column1) ;

Implicit Indexes

Implicit indexes are indexes that server creates automatically  upon creation of the object . Indexes are automatically created for primary key constraints and unique constraints.

SQL INDEX DROP Command

You can Drop index with the below code

DROP INDEX index_name;

4 Scenarios WHEN SQL Indexes Are Not An Option

  1. When tables are too small
  2. Tables have frequent, large batch updates or insert operations.
  3. When columns contain a high number of NULL values.
  4. Manipulation frequency of Column(s) is high.

SQL indexes are very important when it comes to the optimisation of database and enhancing query performance. The selection criteria for index and the columns which should be selected for index , needs a special consideration.

How Does an SQL Index work

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 :

What Is SQL Index
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.

We have seen how an SQL index sort data and how it helps to optimise the performance of the database. How data is organised in data structures and what will happen upon each read /write operation.

At the end we discussed types of indexes with practical examples because then you will be able to understand why DBAs love & hate SQL Indexes .

Join 2,256 other subscribers

Stay Tuned for next articles!

Leave a Reply