Do covering indices safely replace smaller covering indices?

Question

For example:

Given columns A,B,C,D,

IX_A is an index on 'A'

IX_AB is a covering index on 'AB'

IX_A can be safely removed, for it is redundant: IX_AB will be used in its place. I want to know if this generalizes:

If I have:

IX_AB
IX_ABC
IX_ABCD

and so forth,

Can the lesser indices still be safely removed? That is, does IX_ABC make IX_AB redundant, and does IX_ABCD make both IX_AB and IX_ABC redundant?

Answer 1

In general -- and this varies from server to server -- a covering index will cover smaller-selections of the index.

So if you have an index that covers a, b, c, that usually automatically gives you an index that covers a, and a, b.

You are not guaranteed to have, for example, a covering index of b, c.

Answer 2

Yes, for the most part.

However, IX_ABCD isn't terribly helpful as a replacement for, say, IX_BCD.

There is a caveat, however: indexes still may require disk reads, so if C and D explode the size of the index, there will be some inefficiency in looking up A,B in IX_ABCD that wouldn't occur when looking it up in IX_AB.

However, that difference is likely outweighed by the additional performance hit of maintaining IX_AB separately.

Answer 3

The important thing is the leading columns in the index. If you have the index IX_ABCD the following queries will use the index:

select * from table where A = 1

select * from table where A = 1 and B = 1

select * from table where A = 1 and B = 1 and C = 1

However, the following will most likely not uses the index (at least not how you intended):

select * from table where B = 1

select * from table where C = 1

select * from table where B = 1 and C = 1

The important thing is that the leading columns are used. Therefore the order of the columns when the index is created does matter.

Answer 4

Not necessarily. While is true that an index on (A, B, C) can be used for a filtering predicate on A or an ordering request on A or a join condition on A, that does not necessarily mean that the index (A) alone is useless. If the index on (A, B, C) is considerably wider than (A), then a range scan on A alone will save significant I/O because it would have to read fewer pages (narrower index).

ut I admint that this would be the exception rather than the rule. In general is safe to remove an index on A if another one on (A, B) exists. Note that an index on (A,B) does not satisfy any filtering on B so, is safe to remove only if the leftmost column(s) are the same. Some databases have 'skip-scan' operators that can use an index on (A,B) for looking up B, but that is a very narrow border case.

Answer 5

Always best not to assume anything about database engine internals and actually check the actual query plans being used.