Working with indexes in databases for beginners

Do we have any relational Database out there that is devoid of using Indexes? Well, there isn’t any. Very much similar to the index of your book, indexes in a database grants a swift route to find the precise data you are looking for.

Think of the company like Wal-Mart that has a database of enormous amount of employees working with them and just to find one amongst them is a very tedious task to ask for. Would you be going to search for each and every record in your database so that you could locate that person? Here is where index role comes into play.

Any database has its records in the form of a table consisting of rows and columns. Any primary key on the table has an index. Hence when you classify any column as the primary key, it implicitly constructs an index. Refer to the below table to have a clear understanding on the indexes.

“Employees” Database Table

Assuming you want to request from a database to provide the details of the employee with the EmployeeID as 3003.

Here database will be making use of the index that it created for the primary key column “EmployeeID” and will fetch everything it has in records for Employee ‘3003’.

Select * from Employees where EmployeeID = 3003;

Since the query we requested for contained the primary column ‘EmployeeID’, it can look at the primary key index and accordingly fetch the location of Customer ‘3003’.

Now when you don’t have the EmployeeID but is provided with the Street Address of the Employee, the primary key index cannot be used by the database. The following query needs to be written to obtain the necessary details

Select * from Employees where Address = "7 Long Streets";

However, if the above query is found to be used on a more often basis, we can go ahead and add an index on the “Address” Column which in turn will allow the database to bring into play that index.

There can be a situation when there is no index defined on the column, in such cases the whole database is scanned and may take a bit of a time. But looking at the pace at which technology has moved nowadays, this shouldn’t be creating any issue now. However to be on the safe side, it is always suggested to put indexes on the columns that re queried more often.

The prime reason why indexing is required in a database is to enhance the performance of the entire system in fetching the records that the user requested for.

WORD OF CAUTION: Using loads of indexes in a database may degrade the performance of the system as well. This is for the reason that assumes you are modifying the records of a table quite often which in turn demands for change in index as well. In some of the cases, the database even stops accepting the indexing values if it sees no reason to add the same.

The biggest disadvantage of using index is that it may take up extra space of a disk drive and slows down the outcome of the query requested by the user.