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 !!