Pros and Cons of Indexing in Database
Indexing is used to enhance database performance. Indexes are used to find data quickly with queries. Although indexing gives us this benefit, there is also some cost attached with using indexes. Following are some pros and cons of using indexes in database. 

The main advantage of using indexes is finding data quickly. So if you are going to perform a lot of SELECT operations, then using indexing will enhance the performance of your database and also decrease the operation time of your Select queries. 
Another type of queries that can benefit from indexing are ORDER BY, GROUP BY and even to some extent DELETE and UPDATE. 

First disadvantage of using indexes is additional memory usage. Every non-clustered index requires additional storage space as it is stored separately from the table. So if you have big data table, this can be quite costly. 
Another disadvantage of using indexes is that Data Manipulation queries (INSERT, UPDATE, DELETE) become slow, as whenever these queries are performed on the table, data in all the indexes also needs to be updated. Hence it can harm the performance of data modifications. 

Final Words:
Indexes are used quite extensively now, they enhance the performance of data retrieval significantly. Additional memory usage by indexes is no longer a problem nowadays as there are abundance of high disk space available. But it all depends upon the data and the types of operations one needs to perform regularly on it. If a database needs to be manipulated too often, then using indexes may not be the best option, but if data read operations and find queries are performed regularly, then indexing can definitely increase the performance.

Users browsing this thread:
1 Guest(s)