We have discussed on how index improve the database performance but there are also many disadvantages. Lets discuss them
1.Too many indexes can hamper performance. If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated.
2. Indexes need disk space, the more indexes you have, more disk space is used.
Index is a very complex subject and should be exercised carefully. Defining indexes without proper analysis may hamper the performance. Again, indexes are also very important aspect of performance tuning.