composite index and column order

**ZeldorBlat** · Jun 9 '06, 12:05 AM

Re: composite index and column order

Baihao Yuan wrote:[color=blue]
> Hi,
>
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
>
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
>
> Also this won't use the index:
> WHERE firstname = ...
>
> But how about: WHERE firstname = .. AND lastname = ...[/color]

It will use the index.
[color=blue]
>
> And why?[/color]

Because, as far as the query optimizer is concerned, these two are
exactly the same:

WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...

**Baihao Yuan** · Jun 9 '06, 12:15 AM

Re: composite index and column order

Thanks for your help, I really appreciate it.

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

**Alexander Kuznetsov** · Jun 9 '06, 03:15 AM

Re: composite index and column order

Baihao Yuan wrote:[color=blue]
> Hi,
>
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
>
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
>
> Also this won't use the index:
> WHERE firstname = ...
>[/color]

Not necessarily. Consider the following query:

select lastname, firstname from some_table where firstname = ...

It will use the index, and, more to the point, it will not touch the
table at all - the index already has all the information the query
needs. It is called "index covering".

**ZeldorBlat** · Jun 9 '06, 04:55 AM

Re: composite index and column order

Alexander Kuznetsov wrote:[color=blue]
> Baihao Yuan wrote:[color=green]
> > Hi,
> >
> > I created a composite index (lastname, firstname). I know the following
> > queries will use this index:
> >
> > WHERE lastname = ...
> > WHERE lastname = ... AND firstname = ...
> >
> > Also this won't use the index:
> > WHERE firstname = ...
> >[/color]
>
> Not necessarily. Consider the following query:
>
> select lastname, firstname from some_table where firstname = ...
>
> It will use the index, and, more to the point, it will not touch the
> table at all - the index already has all the information the query
> needs. It is called "index covering".[/color]

No, it won't. If you had a list of people on a piece of paper, sorted
by last name and then by first name, explain how you would use that
list to find everyone with a first name of "Joe" without looking
through the entire list.

**smithabreddy@gmail.com** · Jun 9 '06, 11:25 AM

Re: composite index and column order

I created a table called tblNames with nonClustered index defined on
lastname,firstn ame (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:[color=blue]
> Alexander Kuznetsov wrote:[color=green]
> > Baihao Yuan wrote:[color=darkred]
> > > Hi,
> > >
> > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > >
> > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > >
> > > Also this won't use the index:
> > > WHERE firstname = ...
> > >[/color]
> >
> > Not necessarily. Consider the following query:
> >
> > select lastname, firstname from some_table where firstname = ...
> >
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".[/color]
>
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.[/color]

**Alexander Kuznetsov** · Jun 9 '06, 01:15 PM

Re: composite index and column order

> > Not necessarily. Consider the following query:[color=blue][color=green]
> >
> > select lastname, firstname from some_table where firstname = ...
> >
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".[/color]
>
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.[/color]

Why don't you try it out in practice? You might be in for some
surprise. If the index is smaller than the table, and contains all the
necessary information, it is likely to be used instead of the table.
Google up "index covering".

**ZeldorBlat** · Jun 9 '06, 08:55 PM

Re: composite index and column order

Alexander Kuznetsov wrote:[color=blue][color=green][color=darkred]
> > > Not necessarily. Consider the following query:
> > >
> > > select lastname, firstname from some_table where firstname = ...
> > >
> > > It will use the index, and, more to the point, it will not touch the
> > > table at all - the index already has all the information the query
> > > needs. It is called "index covering".[/color]
> >
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.[/color]
>
> Why don't you try it out in practice? You might be in for some
> surprise. If the index is smaller than the table, and contains all the
> necessary information, it is likely to be used instead of the table.
> Google up "index covering".[/color]

I did try it in practice -- as did the OP who posted his results in
this thread.

**Alexander Kuznetsov** · Jun 9 '06, 09:15 PM

Re: composite index and column order

>[color=blue]
> select * from tblnames where firstname = 'john'--Performed a Table Scan
>[/color]

If you only select 2 columns, firstname, lastname

select columns, firstname from tblnames where firstname = 'john'

and the table has a lot of other columns, the index is likely to be
used even if firstname is not the first column in the index. The reason
is simple: the index contains all the information necessary to satisfy
the query and it is smaller than the table.

**Alexander Kuznetsov** · Jun 9 '06, 09:15 PM

Re: composite index and column order

[color=blue]
> I did try it in practice -- as did the OP who posted his results in
> this thread.[/color]

the OP tried for

select * from ...

while I was speaking aobut

select lastName, firstname from ...

Big difference.

**Alexander Kuznetsov** · Jun 9 '06, 09:15 PM

Re: composite index and column order

correction:

If you only select 2 columns, firstname, lastname

select firstname, lastname from tblnames where firstname = 'john'
[color=blue]
> and the table has a lot of other columns, the index is likely to be
> used even if firstname is not the first column in the index. The reason
> is simple: the index contains all the information necessary to satisfy
> the query and it is smaller than the table.[/color]

**Erland Sommarskog** · Jun 9 '06, 10:15 PM

Re: composite index and column order

ZeldorBlat (zeldorblat@gma il.com) writes:[color=blue]
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.[/color]

Say further that with each list there is a page number to references
where the persons appear in the book.

If all you want to know is the name of the persons, you can scan
the index, you don't have to read the whole book.

It's important to keep in mind that an index can be used in two
ways: Seek (look up data through the index tree) and Scan (read
the entire index from left to right): While the latter is far more
expensive, it can still be useful at times.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**ZeldorBlat** · Jun 10 '06, 02:55 AM

Re: composite index and column order

Erland Sommarskog wrote:[color=blue]
> ZeldorBlat (zeldorblat@gma il.com) writes:[color=green]
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.[/color]
>
> Say further that with each list there is a page number to references
> where the persons appear in the book.
>
> If all you want to know is the name of the persons, you can scan
> the index, you don't have to read the whole book.
>
> It's important to keep in mind that an index can be used in two
> ways: Seek (look up data through the index tree) and Scan (read
> the entire index from left to right): While the latter is far more
> expensive, it can still be useful at times.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

Point taken. Thanks, Erland.

**Alexander Kuznetsov** · Jun 11 '06, 03:05 AM

Re: composite index and column order

I'd like to repeat the suggestion to do your own experimenting. The
technology evolves quite fast, so anything you might have read in any
book / article / whatever esle may be already obsolete. The optimizer
is way smarter now than it used to be 5 or 10 years ago. In this
particular case you did not need to read anything, you could just take
any table of, say, 100K rows, with, say, 20 columns, create an index on
it

create index i1 on t1(col1, col2)

and see the execution plan for the query

select col1, col2 from t1 where col2 =....

That's all it takes, it's that simple.

Good luck!

composite index and column order

composite index and column order

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment