472,965 Members | 2,266 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,965 software developers and data experts.

composite index and column order

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 = ...

And why?

Thanks a lot,

Baihao
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Jun 8 '06 #1
13 2877

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 = ...

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 = ...

Jun 9 '06 #2
Thanks for your help, I really appreciate it.

Baihao
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Jun 9 '06 #3

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".

Jun 9 '06 #4

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.

Jun 9 '06 #5
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.


Jun 9 '06 #6
> > 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".

Jun 9 '06 #7

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.

Jun 9 '06 #8
>
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.

Jun 9 '06 #9
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.

Jun 9 '06 #10
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.


Jun 9 '06 #11
ZeldorBlat (ze********@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, es****@sommarskog.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
Jun 9 '06 #12

Erland Sommarskog wrote:
ZeldorBlat (ze********@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, es****@sommarskog.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


Point taken. Thanks, Erland.

Jun 10 '06 #13
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!

Jun 11 '06 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Dathan Vance Pattishall | last post by:
------=_NextPart_000_00A7_01C34644.9C21B100 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Say I have column A with 10000 distinct values column B and C with 2...
2
by: Miss Livvy | last post by:
Would it be OK to use varchar(5) instead of char(5) as the first field of a composite clustered index? My gut tells me that varchar would be a bad idea, but I am not finding much information on...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
10
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and selecting Primary Key. But I've heard that's not a...
2
by: Peter Lindquist | last post by:
I had a fun issue this morning, and now I'm wondering if I violated a rule I didn't know about. Any insight would be much appreciated. All operations involving this particular table may be assumed...
2
by: bobby_b_ | last post by:
I have a table where fields 1 and 2 make up the primary key. Because of this, I have a unique composite index on fields 1 and 2 (as required by DB2). Now my question is: Fields 1 and 2 are also...
14
by: dave.dolan | last post by:
Basically I'd like to implement the composite design pattern with leaves that are either of reference or value types, but even using generics I can't seem to avoid boxing (using ArrayList or...
2
by: lfhenry | last post by:
Hi All, I am about to make some changes to a process we have. The new process will have a new table who's job it is to store temporarily data such as customer, clerkno,productno, storeno , amount,...
3
by: meter | last post by:
I am having the following query and I am wondering how should I construct an index for each of the clients and products tables. SELECT clients.id AS id, clients.name AS name, ...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.