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 = ...
And why?
Thanks a lot,
Baihao
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGBaihao Yuan wrote:
> 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 = ...
It will use the index.
> And why?
Because, as far as the query optimizer is concerned, these two are
exactly the same:
WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...|||Thanks for your help, I really appreciate it.
Baihao
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG|||Baihao Yuan wrote:
> 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 = ...
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".|||Alexander Kuznetsov wrote:
> Baihao Yuan wrote:
> > 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 = ...
> 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".
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.|||I created a table called tblNames with nonClustered index defined on
lastname,firstname (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:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > 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 = ...
> > > 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".
> 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.|||> > 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".
> 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.
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".|||Alexander Kuznetsov wrote:
> > > 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".
> > 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.
> 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".
I did try it in practice -- as did the OP who posted his results in
this thread.|||>
> select * from tblnames where firstname = 'john'--Performed a Table Scan
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.|||> I did try it in practice -- as did the OP who posted his results in
> this thread.
the OP tried for
select * from ...
while I was speaking aobut
select lastName, firstname from ...
Big difference.|||correction:
If you only select 2 columns, firstname, lastname
select firstname, lastname 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.|||ZeldorBlat (zeldorblat@.gmail.com) writes:
> 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.
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> ZeldorBlat (zeldorblat@.gmail.com) writes:
> > 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.
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Point taken. Thanks, Erland.|||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!
No comments:
Post a Comment