Redundant indexes

When exactly is an index considered redundant? I've read some general
guidelines about composite indexes, but I'm trying to find some
definitive answers or documentation. For example (fixed width font
will work best):
Table 1 existing indexes:
Name Type Columns
PK1 clustered, unique, PK A
IX1 nonclustered B
IX2 nonclustered ACDE
IX3 nonclustered ACBF
Proposed new index:
IX4 nonclustered AE
Is the proposed new index IX4 redundant? The two columns are already
included in IX2, but they're not adjacent in IX2. Does that matter?
Are any of the existing indexes unneeded?
Table 2 existing indexes:
Name Type Columns
PK1 nonclustered, unique, PK A
IX1 clustered B
IX2 nonclustered C
IX3 nonclustered D
IX4 nonclustered DBEFGHIJKLMNOPQR
Proposed new indexes:
IX5 nonclustered CD
IX6 nonclustered BD
For table 2, are the proposed new indexes redundant? The columns are
already indexed individually, so would those be used? There's also an
existing covering index IX4 that includes two columns from the proposed
index IX6, but in a different order. Could IX4 be used in place of IX6?stavros wrote:
> When exactly is an index considered redundant? I've read some general
> guidelines about composite indexes, but I'm trying to find some
> definitive answers or documentation.
An index I is definitive redundant if its columns C1..Cn are the leading
columns of another index Y on the same table that has the same storage
characteristics (i.e. both non clustered). If clustering is different
then it depends on the access patterns to the table which is to be
retained etc.
> For example (fixed width font
> will work best):
> Table 1 existing indexes:
> Name Type Columns
> PK1 clustered, unique, PK A
> IX1 nonclustered B
> IX2 nonclustered ACDE
> IX3 nonclustered ACBF
> Proposed new index:
> IX4 nonclustered AE
> Is the proposed new index IX4 redundant?
Probably not.
> The two columns are already
> included in IX2, but they're not adjacent in IX2. Does that matter?
Yes. I believe newer Oracle versions can do a skip scan when scanning a
range of the index but I doubt that SQL Server can do it. You'd have to
check with the docs for the details.
> Are any of the existing indexes unneeded?
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
Theoretically IX3 is redundant - although it might be used because it
needs less space then IX4.
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
> For table 2, are the proposed new indexes redundant? The columns are
> already indexed individually, so would those be used? There's also an
> existing covering index IX4 that includes two columns from the proposed
> index IX6, but in a different order. Could IX4 be used in place of IX6?
Depends. B has higher selectivity IX6 might have an advantage over IX4
- especially since IX4 is so large.
As you see even redundant indexes can make sense. It all depends...
robert|||On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
wrote:
> Table 2 existing indexes:
> Name Type Columns
> PK1 nonclustered, unique, PK A
> IX1 clustered B
> IX2 nonclustered C
> IX3 nonclustered D
> IX4 nonclustered DBEFGHIJKLMNOPQR
> Proposed new indexes:
> IX5 nonclustered CD
> IX6 nonclustered BD
Neither of the new indexes are redundant, but each of them makes an
existing index redundant.
Roy|||Roy Harvey wrote:
> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> wrote:
> > Table 2 existing indexes:
> > Name Type Columns
> > PK1 nonclustered, unique, PK A
> > IX1 clustered B
> > IX2 nonclustered C
> > IX3 nonclustered D
> > IX4 nonclustered DBEFGHIJKLMNOPQR
> >
> > Proposed new indexes:
> > IX5 nonclustered CD
> > IX6 nonclustered BD
> Neither of the new indexes are redundant, but each of them makes an
> existing index redundant.
> Roy
But since the columns referenced in the new indexes are already indexed
individually, doesn't that make the new indexes redundant?
http://www.sql-server-performance.com/composite_indexes.asp says that
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index", and also
implies that single-column indexes are generally preferred.|||Robert Klemme wrote:
> An index I is definitive redundant if its columns C1..Cn are the leading
> columns of another index Y on the same table that has the same storage
> characteristics (i.e. both non clustered). If clustering is different
> then it depends on the access patterns to the table which is to be
> retained etc.
>
Thanks for the clear answer, that helps.|||So who are you going to believe? ;-)
"SQL Server has the ability to join two or more individual indexes and
intersect them, just as if you were using a composite index"
... "just as if" is not true.
SQL Server can join two or more individual indexes together, but there is
more internal work involved to do so, much more than if you already have a
composite index on the columns.
"single-column indexes are generally preferred"
Preferred by whom? Maybe just by the writers of this article. Multi column
indexes have much greater chance to be used as covering indexes, which avoid
table bookmark lookups and are generally preferred.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"stavros" <stavros@.mailinator.com> wrote in message
news:1162249345.592630.63930@.m7g2000cwm.googlegroups.com...
> Roy Harvey wrote:
>> On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
>> wrote:
>> > Table 2 existing indexes:
>> > Name Type Columns
>> > PK1 nonclustered, unique, PK A
>> > IX1 clustered B
>> > IX2 nonclustered C
>> > IX3 nonclustered D
>> > IX4 nonclustered DBEFGHIJKLMNOPQR
>> >
>> > Proposed new indexes:
>> > IX5 nonclustered CD
>> > IX6 nonclustered BD
>> Neither of the new indexes are redundant, but each of them makes an
>> existing index redundant.
>> Roy
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant?
> http://www.sql-server-performance.com/composite_indexes.asp says that
> "SQL Server has the ability to join two or more individual indexes and
> intersect them, just as if you were using a composite index", and also
> implies that single-column indexes are generally preferred.
>|||Another incorrect statement from the same article:
"if you create a composite index, such as "City, State", then a query
such as "WHERE City = 'Springfield'" will use the index, but the query
"WHERE STATE = 'MO'" will not use the index."
;)|||Agreed. Should be:
"but the query "WHERE STATE = 'MO'" will not use the index to search for the rows that qualifies, it
can be used to scan for the rows."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1162259023.159887.170330@.b28g2000cwb.googlegroups.com...
> Another incorrect statement from the same article:
> "if you create a composite index, such as "City, State", then a query
> such as "WHERE City = 'Springfield'" will use the index, but the query
> "WHERE STATE = 'MO'" will not use the index."
> ;)
>|||On 31.10.2006 00:13, stavros wrote:
> Robert Klemme wrote:
>> An index I is definitive redundant if its columns C1..Cn are the leading
>> columns of another index Y on the same table that has the same storage
>> characteristics (i.e. both non clustered). If clustering is different
>> then it depends on the access patterns to the table which is to be
>> retained etc.
> Thanks for the clear answer, that helps.
You're welcome. But keep in mind that redundant != superfluous as I
tried to point out for the case of the huge index.
Regards
robert|||stavros wrote:
> Roy Harvey wrote:
> > On 30 Oct 2006 13:01:49 -0800, "stavros" <stavros@.mailinator.com>
> > wrote:
> >
> > > Table 2 existing indexes:
> > > Name Type Columns
> > > PK1 nonclustered, unique, PK A
> > > IX1 clustered B
> > > IX2 nonclustered C
> > > IX3 nonclustered D
> > > IX4 nonclustered DBEFGHIJKLMNOPQR
> > >
> > > Proposed new indexes:
> > > IX5 nonclustered CD
> > > IX6 nonclustered BD
> >
> > Neither of the new indexes are redundant, but each of them makes an
> > existing index redundant.
> >
> > Roy
> But since the columns referenced in the new indexes are already indexed
> individually, doesn't that make the new indexes redundant? [snip]
That is why "redundant" is not all that useful when you are trying to
maximize query performance. I mean, all but the clustered index are by
definition redundant, because their data is already in the table, and
can be retrieved with a table scan or clustered index scan.
Index IX5 will make IX2 redundant. But if column C is very narrow (let's
say a smallint) and D very wide (let's say char(150)), then finding rows
in index IX5 probably requires more reads then finding them in IX2. It
is up to you to decide which performance is satisfactory, and balance
that against the cost that each additional index adds to
inserts/updates/deletes.
Gert-Jan