What is an indexing?

Indexing is a technique used by SQL databases to exact the data fasters. Means our DB will maintain a sub-table for our records and utilize that table to identify the expected data.

Sounds confusing😕 let’s take look into the real time example!

Consider your company is have 10000 employees, you want extract one employee “8267” — “john” details from that table. so, you written a query. Do your thing your DB will start search from 0 and then goes all the way to 8267. Noooooo!

Your DB will maintain an index table where primary keys were stored on the ordered manner. like 0–1000, 1000–2000, 2000–3000 etc…, so your query goes to index table pick 8000–9000 pointer then goes to actual table and pick the record then return the results.

Awesome 😃 right, now let’s understand types of indexing.

· Ordered Index:

In this type of index records will be stored on sorted manner and pointer have a reference value for the records

· Primary Index:

If the index is created on the basis of the primary key of the table, then it is known as primary indexing. These primary keys are unique to each record and contain 1:1 relation between the records.

· Cluster Index:

A clustered index can be defined as an ordered data file. Sometimes the index is created on non-primary key columns which may not be unique for each record.

· Secondary Index:

In the secondary indexing, as the size of the table grows, the size of mapping also grows. These mappings are usually kept in the primary memory so that address fetch should be faster. Then the secondary memory searches the actual data based on the address got from mapping.

· Composite Index:

In this composite Indexing, let you composite multiple columns into single index.

Experiment time: Let’s dive into the practical examples:

Here the general query format for creating index:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Primary Index:

it’s default index created by SQL while creating the table. this index created on top of primary keys values on table.

CREATE INDEX IX_tblEmployee_ID
ON Employee(Emp_ID)

#To see the existing index create by SQL
SHOW INDEXES FROM Employee

Ordered Index:

Imagine you want to fire the top salaried employees, so let’s create salary based sorted value index on the employee's table.

CREATE INDEX IX_tblEmployee_Salary
ON Employee(Salary DESC)

Cluster Index:

Consider in your company there are few departments and usually you perform search based on the department. here we can utilize the “Cluster Index” where we can create clusters of departments here the actual query:

CREATE CLUSTERED INDEX IX_tblEmployee_Department
ON Employee(Department ASC)

Composite Index:

Something you search employee based on name and also combination of the age of the employee. so, let’s create an index for that.

CREATE INDEX composite_index_emp ON Employee (name, age);

There are few disadvantages also associated with the indexing let look at those:

• Indexing necessitates more storage space to hold the index data structure, which might increase the total size of the database.

• Increased database maintenance overhead: Indexes must be maintained as data is added, destroyed, or modified in the table, which might raise database maintenance overhead.

• Indexing can reduce insert and update performance since the index data structure must be updated each time data is modified.

• Choosing an index can be difficult: It can be challenging to choose the right indexes for a specific query or application and may call for a detailed examination of the data and access patterns.

Conclusion:

In the end, it can extremely usefully concept if understand the requirement of the project and implemented at the right place. A carefully chosen index will surely help you boost up your application’s performance. However, if our records are smaller in size it does make greater impact and also if constantly updating the records in millions of read and write indexing can be increase operations time. so, choose and use wisely.

Happy Reading 😊