How does database indexing work with real time example

Welcome to Appsloveworld Technologies, In this session, we learn about what indexes are, why do we use indexes, and the advantages of using them.

 What is  indexes?

Indexes are used by queries to find data from tables quickly. Indexes are usually created on tables and views. Index on a table or a view is very much similar to an index that we find in a book.

If you don’t have an index in a book and I ask you to look at a specific chapter in that book, you will have to look at every page starting from the first page till the end.

On the other hand, let’s assume there is an index in the book. In that case, you look up to page number of the chapter with the index and then directly go to that page number to look at that chapter.
So the book index is helping to reduce the time it takes to find the chapter.

In a similar fashion table and view index can help the query to find data quickly. In fact, the existence of the right indexes can drastically improve the performance of the query.

If there is no index to help the query, then the query engine just like we do. it has to scan every row in the table from the beginning till the end. And this is called a table scan in database terminology and table scans are bad for performance.

When does a query result in a table scan?

whenever there is no index to help that query, in that case, the database engine has to scan every row from the beginning till the end to find the data.

So  if the table is large enough, then it’s going to affect the performance of the query very badly, just like how it takes a longer time for us to find, the chapter number, or some keywords if there is no index based on that chapter or keywords.

Let’s look at an example.

We’ve got TblUsers which has Id, named, Email, gender, MyRewards, and MyCash columns.And this TblUsers table does not have an index on the MyRewards column.You can find TblUsers Sql Table Script at end of the post.

if you look at the MyRewards, so these values are not arranged in ascending or descending order in the table.

Obviously, if I have to find,Users who’s MyRewards between 50 and 100, I will have to scan each and every row as there is no index on this table to help this query.

So obviously, the database engine has to scan every row from the beginning till the end. It has to scan the entire table. And this is called a table scan and table scans are very bad.

How to create an Index?

So let’s see how to solve this problem with the help of an index. So to create an index on a table, we use the “create index” statement

Just ike to create a table with  “create table” table name ,to create a stored procedure, we’ll use “create proc” procedure name ,similarly to create an index we use, create index, and the index name.

Since we use “sp” for stored procedures, “tbl” for tables, for indexes usually we use IX, underscore the name of the table on which you are creating the index.

CREATE Index IX_TblUsers_MyRewards
ON TblUsers (RewardsPoint ASC)

In this case, we are creating an index on TblUsers. So “_” TblUsers on the column within the table on which we are creating this index.

In this case, we are creating an index on the MyRewards column. So underscore MyRewards on the table on which we are creating the index TblUsers and on MyRewards column within that table.

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

we are creating an index on the MyRewards column.So in the index, the MyRewards column is the key.

So how do you want these key values to be arranged?

Do you want them to be arranged in an ascending or descending order? You can control that using ASC for ascending and DESC for descending keywords.

so we are creating  an index on MyRewards column in the ascending order.

And if you look at the index, all the MyRewards  values are arranged in ascending order.

So once we have this index and if we execute that query, now we know that all RewardsPoint between 50 and 100 are somewhere at the bottom of the index because they are stored in ascending order.

if you look at every entry in the index, for example, the first row with RewardsPoint 11 has got a real address, just like how we have page numbers in the book.

The rows have addresses, the actual index may look slightly different .This is for our understanding ,I have entered a row address here but in fact, it will be some hexadecimal number.

So each row has a row address So it will directly go to the table and then pick up that record, just like how if we have a page number, we go to that page number directly.

So instead of scanning each individual row in the table, I am now able to seek the index and then pick up the addresses from the index, go to the table, and using those addresses, retrieve those records directly rather than scanning each room in the table.

obviously, this is going to, improve the performance of this query.

How see created index?

Expand the tables folder and we have TblUsers. Now if we expand the TblUsers table, you should see an index folder and if you expand the indexes folder you should see an 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', 'aakankshi@gmail.com', 0, CAST(127.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', 'aakankshi6@gmail.com', 3, CAST(109.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', 'alokpatel@gmail.com', 1, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', 'contact@adequateinfosoft.com', 1, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', 'neirajsingh101@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (3, 'Pankaj Patel', 'Pankaj@gmail.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2, 'rajat Saxena', 'rajat.nirmal@gmail.com', 1, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', 'test02@gmail.com', 0, CAST(5955.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', 'pankajabc@gmail.com', 2, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', 'pankajabcd@gmail.com', 0, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', 'test@test.com', 1, CAST(31685.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', 'test1@test.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', 'pankajws@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', 'test3@test.com', 0, CAST(109.00 AS Decimal(18, 2)))
GO

The post How does database indexing work with real time example appeared first on Software Development | Programming Tutorials.