Database Optimization Techniques #1: Indexing index

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

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. Database Indexing 1

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. Database Indexing 2

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
and get the assistance of our expert DBAs at OptimizDBA
to boost your database.