How do I know if my table has redundant indexes?

Question

I have been taking a look at the queries run against a particular table, along with the indexes on the table, and I have discovered the following:

1) Query #1 uses a composite index that includes a three column index.

2) Query #2 uses a composite index that includes a two column index.

3) The first two columns of both indexes are identical.

Here's my question. Since the two different indexes overlap the first two columns, is the composite index with the two columns redundant? And if I remove it, will Query #2 automatically use the three column index, even though it has previously only used the two column index?

Answer

You are correct in your assumptions. What you have discovered is a redundant index, and this a more common problem than you might expect. Redundant indexes hurt performance because it takes extra resources to maintain them. By removing the index with only two columns, and leaving the index with three columns, you will be removing a redundant index, and when Query #2 runs, it will now automatically use the three column index, and you won't see any performance degradation because of this.

As a part of your performance tuning strategy, you might want to review heavily indexed tables and see if there are any redundant indexes that you can eliminate.