How does database Indexing work with Real-time example

Welcome to Quickpickdeal Technologies, in this session, we will learn about what indexes are, why we use indexes, and the advantages of using them.

 What is  indexes?

Indexes are utilized by queries to quickly retrieve data from tables. Indexes are typically created on tables and views. An index on a table or view functions much like an index found in a book.

If you don’t have an index in a book and I ask you to locate a specific chapter, you would need to search through every page from the beginning to the end.

On the other hand, let’s assume there is an index in the book. In that case, you can refer to the page number listed in the index for the chapter, allowing you to directly navigate to that page.

Similarly, table and view indexes can assist queries in finding data quickly. Indeed, having the appropriate indexes can significantly enhance query performance.

Without an index to aid the query, the query engine must scan every row in the table from start to finish, much like we would without an index in a book. This process is referred to as a table scan in database terminology, and table scans adversely affect performance.

When does a query result in a table scan?

When there is no index to assist the query, the database engine must scan every row from the beginning to the end to locate the data.

Consequently, if the table is sufficiently large, it will significantly impact the query's performance, akin to how it takes us longer to find a chapter number or specific keywords in a book without an index for reference.

Let’s look at an example.

We have a table called TblUsers, which includes columns such as Id, Name, Email, Gender, MyRewards, and MyCash. Notably, the TblUsers table lacks an index on the MyRewards column. The SQL script for creating the TblUsers table can be found at the end of this post.

If you examine the MyRewards column, you'll notice that its values are not sorted in either ascending or descending order in the table.

Consequently, when searching for users whose MyRewards fall between 50 and 100, every row must be scanned since there is no index on this table to facilitate the query.

As a result, the database engine must scan the entire table, starting from the beginning to the end. This process is referred to as a table scan, which is highly inefficient.

How to create an Index?

Now, let’s explore how to address this issue with the assistance of an index. To create an index on a table, we employ the 'CREATE INDEX' statement.

Just as we use 'CREATE TABLE' to create a table and 'CREATE PROC' for stored procedures, similarly, to create an index, we use 'CREATE INDEX', followed by the index name.

Conventionally, while 'sp' is often used for stored procedures and 'tbl' for tables, for indexes, 'IX' followed by an underscore and the name of the table on which the index is being created is commonly utilized.

CREATE Index IX_TblUsers_MyRewards
ON TblUsers (RewardsPoint ASC)

In this scenario, we are establishing an index on TblUsers. Therefore, we append '_' followed by TblUsers, indicating the column within the table for which this index is being created.

Specifically, we are establishing an index on the MyRewards column. Hence, we append underscore followed by MyRewards on the table for which the index, TblUsers, is being created and on the MyRewards column within that table.

How do you want the entries in the index to be arranged?

When creating an index on the MyRewards column, the MyRewards column serves as the key.

Now, how would you like these key values to be organized? You can specify whether they should be arranged in ascending or descending order by using the keywords ASC for ascending and DESC for descending.

Therefore, in this instance, we are establishing an index on the MyRewards column in ascending order.

Upon inspection of the index, you'll observe that all MyRewards values are arranged in ascending order."

Once we have this index in place, when executing the query, we know that all rewards points between 50 and 100 are located towards the end of the index since they are stored in ascending order.

Upon examining each entry in the index, consider the first row with a rewards point of 11; it possesses a real address, much like the page numbers in a book.

Each row in the index has an associated address. While I've represented them as simple row addresses for our understanding, in reality, they would likely be represented as hexadecimal numbers.

With each row having an address, the query can directly navigate to the table and retrieve the corresponding record, similar to how we navigate to a specific page number in a book.

Instead of scanning each individual row in the table, the index allows us to seek the addresses from the index and then retrieve the records directly from the table using those addresses, thus significantly enhancing the performance of the query.

How see created index?

Expand the 'Tables' folder, and you will find 'TblUsers'. Now, if you expand the 'TblUsers' table, you should see an 'Indexes' folder. Upon expanding the 'Indexes' folder, you will encounter the index that we have just created.

Table Script

/****** Object:  Table [dbo].[TblUsers]    Script Date: 09/28/2020 4:25:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUsers](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](100) NULL,
    [Email] [nvarchar](max) NOT NULL,
    [Gender] [int] NOT NULL,
    [RewardsPoint] [decimal](18, 2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2012, 'Aakankshi Gupta', '[email protected]', 0, CAST(127.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', '[email protected]', 3, CAST(109.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', '[email protected]', 1, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', '[email protected]', 1, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', '[email protected]', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (3, 'Pankaj Patel', '[email protected]', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2, 'rajat Saxena', '[email protected]', 1, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', '[email protected]', 0, CAST(5955.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', '[email protected]', 2, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', '[email protected]', 0, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', '[email protected]', 1, CAST(31685.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', '[email protected]', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', '[email protected]', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', '[email protected]', 0, CAST(109.00 AS Decimal(18, 2)))
GO