Wednesday 16 January 2013

Want Faster database queries ? Try INDEX'ing !!

What is indexing ?

Consider this - suppose you are browsing through the pages of a book and you want to find a particular topic , what would you do ?

Option 1 ( The Dumber Way) - Browse through each page , manually go through all the contents of it , and find the topic you are looking for .

Option 2 (The Smarter Way) - View the contents page , see the page number for that topic , and go to that page .

Indexing works much like option 2 . In other words , indexing allows us to find the data fast without going through all the items in a table .


When to implement indexing ?

Suppose you have a table like this :-

Serial No |  Continent          |  Country
------------------------------------
1             |  Asia                   | India
2             |  Asia                   | China
3             | North America    | USA 
4             | North America    |Canada
5             | Europe                | UK                       ....... and so on

and suppose most of the time you retreive a row or query the table by the name of the country , then you should create an index on the field "country"  . You can also have multiple indexes for a table . When i used index for the first time , i was astonished to see the results . On an average each query was speeded up by atleast 70-80 times !! 

!! WORD OF CAUTION  
Updating a table with syntaxes takes more time than updating one without any , so use indexes carefully . Use them only on fields which are frequently searched against !!


How to implement ?    

Mysql
-------

CREATE INDEX name_of_index ON table_name (fieldname)

to drop an index :-

DROP INDEX name_of_index ON table



MongoDB
-------

database.collection_name.ensureIndex({fieldname1 : 1, fieldname2 : -1})

In this case indexes will be created on both the fields , however , fieldname 1 will be arranged in an ascending order and fieldname 2 in descending !!




No comments:

Post a Comment