Prev: SQL Server Output Buffer
Next: cannot open backup device
From: Erland Sommarskog on 12 Dec 2009 10:41
Ron (Ron(a)discussions.microsoft.com) writes:
> If I may, just as for a little clarification on this point, as it is of
> particular interest to me...
>
> If the table were to have say 4 possible columns that were used in a
> search, (lets call them index1 thru index4), and each of them were
> searchable (ANDed) in any order...for example
>
> WHERE INDEX1='foo' AND INDEX2='bar'
> or
> WHERE INDEX2='foo' AND INDEX1='bar'
> or
> WHERE INDEX1='foo' AND INDEX3='bar'
> etc etc
>
> In this situation,I expect creating indexes with included columns for each
> combination and order would be going too far. So in this situation, is the
> only way to cover the necessary columns is to add an index on each ?

That is indeed a trade-off, and it's not possible to give a general rule.
If you need to search col1, col2, col3 and col4 in any combination of two,
it may be that bad that you really need six indexes. But maybe, say, col2
is selective enough, that an index on (col2) is good enough, and you
don't need indexes on (col2, col1), (col2, col3) and (col2, col4).

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 12 Dec 2009 10:49
Jay (spam(a)nospam.org) writes:
> I was of the distinct impression that the Query Optimizer chose zero to
> one index, but never more.

No, the optimizer can use more than one index. Try this:

create table #demo (id int IDENTITY PRIMARY KEY,
num int NOT NULL,
name char(3) NOT NULL,
k char(2000) NOT NULL DEFAULT '')
go
INSERT #demo (num, name)
SELECT a.object_id % 100, substring(a.name, 1, 3)
FROM sys.objects a
CROSS JOIN sys.objects b
go
create index ix1 on #demo(num)
create index ix2 on #demo(name)
go
-- This query uses two indexes-
SELECT COUNT(*)
FROM #demo
WHERE num = 13
OR name = 'obj'
go
drop table #demo

However, if I change OR to AND in the last query, the optimizer uses
only one of the NC indexes.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Jay on 12 Dec 2009 13:59
OK, that's a fairly narrow case and yes, I should have thought of it. Still,
in the more normal case, when people want to extract maximum performance,
which is most of the time you're considering adding indexes, the Query
Optimizer is going to use a single index and adding more indexes thinking
you're going to improve things will just use drive space.

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CDFAB59F6167Yazorman(a)127.0.0.1...
> Jay (spam(a)nospam.org) writes:
>> I was of the distinct impression that the Query Optimizer chose zero to
>> one index, but never more.
>
> No, the optimizer can use more than one index. Try this:
>
> create table #demo (id int IDENTITY PRIMARY KEY,
> num int NOT NULL,
> name char(3) NOT NULL,
> k char(2000) NOT NULL DEFAULT '')
> go
> INSERT #demo (num, name)
> SELECT a.object_id % 100, substring(a.name, 1, 3)
> FROM sys.objects a
> CROSS JOIN sys.objects b
> go
> create index ix1 on #demo(num)
> create index ix2 on #demo(name)
> go
> -- This query uses two indexes-
> SELECT COUNT(*)
> FROM #demo
> WHERE num = 13
> OR name = 'obj'
> go
> drop table #demo
>
> However, if I change OR to AND in the last query, the optimizer uses
> only one of the NC indexes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Ronnie R on 13 Dec 2009 07:23
Thanks Jay, that is clear. Thanks

So in this final example....

WHERE INDEX4='foo' AND INDEX2='bar'

a index on INDEX1 with included columns (INDEX2,INDEX3,INDEX4) would likely
be used, as INDEX1 part of the index is ignored, INDEX2 used, INDEX3 ignored,
INDEX4 used. Would you agree?





"Ron" wrote:

> If I may, just as for a little clarification on this point, as it is of
> particular interest to me...
>
> If the table were to have say 4 possible columns that were used in a search,
> (lets call them index1 thru index4), and each of them were searchable (ANDed)
> in any order...for example
>
> WHERE INDEX1='foo' AND INDEX2='bar'
> or
> WHERE INDEX2='foo' AND INDEX1='bar'
> or
> WHERE INDEX1='foo' AND INDEX3='bar'
> etc etc
>
> In this situation,I expect creating indexes with included columns for each
> combination and order would be going too far. So in this situation, is the
> only way to cover the necessary columns is to add an index on each ?
>
> "Erland Sommarskog" wrote:
>
> > mark (user(a)msgroups.net/) writes:
> > > So I take it that if both columns are specified in the where, the
> > > composite index is faster(maybe not by much) than the separate indexes?
> >
> > "Maybe not by much", well that depends. For a large table, that could
> > indeed be much. Say that you need to search a table with 100 million
> > rows for people name John Smith in Seattle. With one index each on first
> > name, last name and City, I would expect the optimizer to give up and
> > scan the table. With a compositie index, the optimizer would urely use it.
> >
> > > And if only the first column is specified in the where, the separate
> > > indexes is faster than the composite?
> >
> > Yes, but I don't think difference can be as dramatic as in the case above.
> > --
> > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
> >
> > Links for SQL Server Books Online:
> > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >
> > .
> >
From: Jay on 13 Dec 2009 07:54
The Query Optimizer is basically looking for an index that it can read from
left to right that will get it to the data in the minimum number of disk
reads.

So, you said "a index on INDEX1 with included columns (INDEX2,...)". Are you
asking if an index headed by a column not specified in the WHERE clause
would be used? If so, it's a big resounding NO.

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
>>>>> Index usage is a left-to-right reading order. <<<<<
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The first column of an index must be specified in the where clause before
the QO will even consider it.

When describing indexes, it's best to put ALL the columns in parentheses,
like (index1, index2, index3, index4). It's how we read and it matches the
DDL.


"Ronnie R" <RonnieR(a)discussions.microsoft.com> wrote in message
news:907B60C2-4F7F-458F-9BA5-FC72C3159E8E(a)microsoft.com...
> Thanks Jay, that is clear. Thanks
>
> So in this final example....
>
> WHERE INDEX4='foo' AND INDEX2='bar'
>
> a index on INDEX1 with included columns (INDEX2,INDEX3,INDEX4) would
> likely
> be used, as INDEX1 part of the index is ignored, INDEX2 used, INDEX3
> ignored,
> INDEX4 used. Would you agree?
>
>
>
>
>
> "Ron" wrote:
>
>> If I may, just as for a little clarification on this point, as it is of
>> particular interest to me...
>>
>> If the table were to have say 4 possible columns that were used in a
>> search,
>> (lets call them index1 thru index4), and each of them were searchable
>> (ANDed)
>> in any order...for example
>>
>> WHERE INDEX1='foo' AND INDEX2='bar'
>> or
>> WHERE INDEX2='foo' AND INDEX1='bar'
>> or
>> WHERE INDEX1='foo' AND INDEX3='bar'
>> etc etc
>>
>> In this situation,I expect creating indexes with included columns for
>> each
>> combination and order would be going too far. So in this situation, is
>> the
>> only way to cover the necessary columns is to add an index on each ?
>>
>> "Erland Sommarskog" wrote:
>>
>> > mark (user(a)msgroups.net/) writes:
>> > > So I take it that if both columns are specified in the where, the
>> > > composite index is faster(maybe not by much) than the separate
>> > > indexes?
>> >
>> > "Maybe not by much", well that depends. For a large table, that could
>> > indeed be much. Say that you need to search a table with 100 million
>> > rows for people name John Smith in Seattle. With one index each on
>> > first
>> > name, last name and City, I would expect the optimizer to give up and
>> > scan the table. With a compositie index, the optimizer would urely use
>> > it.
>> >
>> > > And if only the first column is specified in the where, the separate
>> > > indexes is faster than the composite?
>> >
>> > Yes, but I don't think difference can be as dramatic as in the case
>> > above.
>> > --
>> > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>> >
>> > Links for SQL Server Books Online:
>> > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> > SQL 2000:
>> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> >
>> > .
>> >


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: SQL Server Output Buffer
Next: cannot open backup device