Common Indexing Issues

As a new programmer you may have a lot of questions.

A more common initial question is should you index all the columns?

A database index is important for efficiently retrieving data.  To speed up data retrieval optimally, the correct indexes need to be defined for the tables.  For example, an index can speed up the query when putting in a filter in the WHERE clause.  Missing indexes in large databases can make queries take longer.

While indexes are widely used to implement query optimization, the reason there is not typically an index on every column in a table is because they do take up RAM and drive (in most cases the space constraint will be minimal).  Also, as each index is updated for every piece of data that is updated, this can slow down inserts and updates.  It can be inefficient to index columns that will never be utilized, especially if the system is at the point of surrendering drive space and risking the performance.  The risk of additional locking is something to seriously consider.

In conclusion, the most important piece to pay attention to are the queries that are frequently used by the indexes.  However, with the pace of technology advancements, it would not be a surprise if some of these constraints were eliminated in the future.  In the meantime, a query of the missing indexes can be performed and evaluated to consider optimization.







Leave a Reply

Your email address will not be published. Required fields are marked *