The database is crucial for the operations and to ensure the operations go on seamless without any slowdowns, it is very important to make sure that the database performs well. The high performance of the database could be very well managed by practicing database optimization techniques.

Though we have gone through the database performance tuning in the previous articles, in this article we would focus precisely on indexing. Before we proceed to explore and get into the details of how indexing is performed for database optimization, let us first see what indexes are and how they are helpful in database optimization.

What is Database Index?

Database Index is a data structuring which is based on one or more columns of the database. The main idea behind indexing is to fasten the data retrieval. This helps in locating the data easily, needless to go through each and every row, every time the database is accessed.

Using columns, indexing helps in minimizing the disk accesses for each query which is processed. This makes the database indexing a powerful technique for database optimization improving the overall performance of the database.

Structure of a Database Index

An index consists of two columns i.e. a Search Key and a Data Reference. The search key is mainly a copy of the primary key of the table which is stored in a sorted manner, for quick access to data. There could be a chance that the data in the table might not be sorted and thus, sorting in index search key proves to be very helpful in speeding up the data access.

The second column consists of Data Reference which has the set of pointers, that has the address of the respective disk block where the particular key value can be found.

Indexes could be of two types i.e. Ordered Index and Hash Index.

Ordered index as the name suggests is based on the sorted ordering of the primary key values. On the other hand, the hash index is based on the uniform distribution of values across a range of buckets. These assigning of buckets for a value is determined by hash function. But then the hash index is not sorted in nature and for some of the queries which require sorted data, hash indexes might not be helpful.

These indexes are used in accordance with the database used, to suit the best.

How does Indexing help in Database Optimization?

Database Indexing is fairly helpful in database optimization as it minimizes the number of data access for processing a query. This is especially effective when the data is at scale with a large number of fields.

Let us try to understand this with a real-life example. We all have written, posted and received postcards at some point in our lives. To deliver each postcard you need a Zip Code (Pincode in some countries), which is a number to locate the address.

This Zip Code is like indexing, where the different digits combine to depict a locality and then finding the address based on the name, house number and apartment is easy.

Now imagine, if there were no Zip Code, the letter might have been posted to a city with say 10,000 houses and making it reach the right address could have been lot more time consuming, and tiring process. Where it would be difficult to locate the right address in a city processing approximately 5,000 houses on an average.

On the contrary, locating an address in a small locality having 500 houses within the city could be a lot easier, quick and less tiring process. It would take maximum 500 trips to reach the right address given the locality.

Thereby, minimizing the number of accessing trips to 500 from the otherwise 5,000.

This is how Indexing works, where a set of rows are dedicated to an index (in the above example like locality) and finding one entry using an index would process lesser data instead of processing the whole data present in the database as would cause.

When not to use Indexing

Although, Indexing is very useful for database optimization, yet it also results in extra memory allocation, which might cause memory shortage if indexing is performed for a large number of fields.

Too much indexing could make your database slow due to memory allocation, instead of optimizing it. Thus, it is always advised to use indexing only when required.

In a nutshell

Indexing could be very fruitful in optimizing the database when used tactfully. A well indexed database can be very efficient and high performing with a quick turnaround time of data access. This could play an important role in making the operations seamless with quick database access.

To perform efficient indexing, a diligent database administrator (DBA) could play a pivotal role by optimizing the database for high performance. If you are looking forward to index your database for optimization, feel free to contact us and get the assistance of our expert DBAs at OptimizDBA to boost your database.