Indexing Databases

Does your database look like this….

If so, I think I might be able to help you fix it.

Databases help us store large amounts of data.  But when storing large amounts of data, it can become difficult to easily access the specific data you might be looking for.  The more unorganized your database is, the longer it will take to retrieve data.  Lucikly there is an easily solution….

Indexing!

Indexing is a way to organize your database and allows fast retrieval of information. Indexing also allows you see/show relationship between different data sets.  Without indexing your database, especially if you have an extremely large database, it could take a very, very long time to retrieve data.  For most cases, it is a good idea to implement indexing. (We’ll go over the pro’s and cons a little later).  If you’re thinking about indexing a databas, especially if you haven’t made the database yet, it’s best to lay out a plan to see how you want to index and relate different sets of data.  What tables will be related or point to each other?  The hardest part of indexing is coming up with a plan.  Now that you’ve figured out how you want to organized your indexing, lets take a look at some code.

Adding an index is very easy!

If we have a database named Cats, and you wanted to create an index between the cat’s name and breed you can do it as such:

CREATE INDEX index_cats ON Cats (name, breed, …);

If you need an index with unique properties you can do this:

CREATE UNIQUE INDEX index_cats ON Cats (catID);

And that’s it!  We now have some indexes!  The sky’s the limit….BUT…you don’t want to get too crazy with your indexes.  More isn’t always better….

Pros and cons to indexing:

The main pro of indexing is why we usually use indexing in the first place.  With indexing you will have a fast read time.  You can quickly query and retrive data.  As an example, if you had huge database, without indexing it could take daaaays to query but with good indexing you could cut that down to just a few hours of querying.

A con of indexing is having a slow write time.  Indexes are sorted.  If you’ve indexed your database but then have to insert data, not only do you have to write more code to reference both the column but also the index but it may take a while to find the proper place in the sorted data when the new data needs to go.

But what do I do if I created too many indexes?!?! TOO MANY INDEXES!!!

Well, luckily for you, I can also help you with that as well!  It is very easy to delete an index!.   All you need to do is:

DROP INDEX index_catID on Cats

It’s that easy to fix your database if you got too excited about indexing and regretted it afterwards (we’ve all had those mornings).

In conclusion:

When creating a database you should think about what type of data you will be storing, whether you will be adding to the database alot and how much data you will have.  There are some cases where you may not want to index your database but in general I think it is usually a good idea to index your databases.