CODEWITHSUNDEEP
sqlsql-serverdatabase
Malte
Malte

Reputation: 174

Redundant indexes

I have a table containing the following three indexes:

Index #1:

CREATE NONCLUSTERED INDEX [IDX_1] 
     ON [dbo].[TABLE] ([A] ASC, [B] ASC, [C] ASC)

Index #2:

CREATE NONCLUSTERED INDEX [IDX_2] 
    ON [dbo].[TABLE] ([A] ASC, [B] ASC)

Index #3:

CREATE NONCLUSTERED INDEX [IDX_3] 
    ON [dbo].[TABLE] ([C] ASC, [B] ASC, [A] ASC)

If my humble understanding is correct, then the following holds:

  1. I could remove IDX_2 as it its fully contained in IDX_1 (same columns in same order).

  2. IDX_1 and IDX_3 are not the same. IDX_1 can be used for queries against column A or for queries against column A and column B. On the other hand IDX_3 can be used for queries against column C or columns C and B. Both indexes are equivalent useful for queries against all three columns A, B, C.

  3. IDX_2 and IDX_3 cover IDX_1. For queries against column A or columns A and B IDX_2 will be used and for queries against all three columns IDX_3 can be used. So it is safe to remove IDX_1.

Thanks in advance, Malte

Upvotes: 2

Views: 148

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28860

Out of all your questions, i would remove idx_2 since,that is redundant due to Indx_1

For Questions 2 and 3,which covers below indexes,they are not redundant,if your queries follow some specific pattern like below

  indx-2    CREATE NONCLUSTERED INDEX [IDX_1] ON [dbo].[TABLE] (
              [A] ASC,
              [B] ASC,
              [C] ASC)



  indx-3       CREATE NONCLUSTERED INDEX [IDX_3] ON [dbo].[TABLE] (
              [C] ASC,
              [B] ASC,
              [A] ASC)

below query can use indx2 efficiently,but not index3

select a,b,c from table where a=20 and b=40 and c>40

Below query can use index3 efficiently,but index2 won't be of much help

select a,b,c from table where c=20 and b=40 and a>90

If your queries wont follow this pattern and if all they do is searching for equality ,then one of them is redundant

Upvotes: 3

Related Questions