Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Working with indexes in databases for beginners

In this article you will see how constructing or creating an index may help retrieving the data at a much faster pace but it should be used very carefully and only at places where it is really required for.

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.

Relational Database

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

EmployeeID Name Address Email
1001 John 5 The Back Street Johnh@walmart.com
2002 Steve 111 Christ Church stevew@walmart.com
3003 Mark 7 Long Streets markb@walmart.com

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.



I am a software developer from India with hands on experience on java, html for over 5 years.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login