MERITS AND DEMERTS OF INDEXNG

MERITS

1)Speed up SELECT query

2)Helps to make a row unique or without duplicates(primary,unique) 

3)If index is set to fill-text index, then we can search against large string values. for example to find a word from a sentence etc.

4)They result  in better queries performance.

5)They make it possible to quickly retrieve (fetch) data.

6)They can be used for sorting..

7)Unique indexes guarantee uniquely identifiable records in the database.

DEMERTS

1)Indexes take additional disk space.

2)indexes slow down INSERT,UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.  INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.

3)Some databases will monocase(It means that filenames are case-insensitive) values in fields that are indexed

‎


courtesy..bayt.com