February 26, 2009 at 7:58 am
I have always been told that an index is read from left to right and that as such you needed your WHERE clauses first, then your SELECT statements.
As an example:
Create nonclustered index Index1 on Table1 (Column1, Column2, colum3, Column4, column5, column6)
Select Column2, column3
From table1
Where column1 = something
I think we will all agree that this would give you an index seek all day long with the above select statement.
While doing some optimizations the other day, I came across a query like:
select column4, column5
from Table1
where Column1 = something
and column6 = something
Now, from everything I have been told this should NOT give do an index seek on INDEX1 due to the fact that column6 is not the second column listed in the above index.
However, this is not the case. I do get an index seek with INDEX1. I can actually change the query around numerous ways an so long as Column1 is in the WHERE clause and I don't add any columns that are not in the index, I continually get an index seek (assuming I don't do a between or sum() or something similar that requires a scan)
Is this an abnormality, or have I been misunderstanding indexes this entire time?
Thanks,
Fraggle.
February 26, 2009 at 8:00 am
That's normal.
Generally, if the first column in the index has an equality test in the query, you'll get a seek. The rest of it doesn't matter as much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 8:35 am
So if I searched by column, 1, 2, 3, & 4, I could essentially create 4 indexes as follows and I should get seeks on all of them?
Column1, column2, column3, column4, column5, column6
column2, column1, column3, column4, column5, column6
column3, column1, column2, column4, column5, column6
column4, column1, column2, column3, column5, column6
Do i understand you correctly?
February 26, 2009 at 8:40 am
That's probably overkill, but it should work. Why would you do that, though?
The whole point of the key columns in indexes is that you will be using them in Where and Join clauses. Why include ones that you won't use that way?
If, for example, you have a query that only uses Col4 in the Where clause, and none of the others, why have an index with Cols 1 through 3 and 5 and 6 in they key columns?
If, on the other hand, you will generally have 1, 2, 3, 5 and 6 in your Where/Join clauses, then you don't need all the duplicative indexes, because one will probably handle it.
You want the minimum number of idexes with the minimum number of columns that will get you the best results on the majority of your queries. That way, inserts, updates, and deletes aren't slowed down by having a ton of index activity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 8:54 am
You are correct. I wouldn't do it. I was using it more as a simplified example that if I have queries that use all 6 column, but each of the queries search on a different column (ie column 1-4), then the order of the rest of it doesn't matter.
From what I am actually seeing in the execution plans, I can actually do a column1 and column3 in the where clause, have the remaining columns in the select statement, and it will still do a seek using the indexes that has column1 list first.
Fraggle
February 26, 2009 at 9:03 am
Fraggle (2/26/2009)
So if I searched by column, 1, 2, 3, & 4, I could essentially create 4 indexes as follows and I should get seeks on all of them?
Yes, for that query
All four of your examples will be fine for queries that filter on all four columns. The order that you specify conditions in the where clause is not important. SQL does the filtering based on the indexes.
Column1, column2, column3, column4, column5, column6
Good for queries of the form
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3 and Column4 = @var4
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3
WHERE Column1 = @var1 and Column2 = @var2
WHERE Column1 = @var1
Not seekable by any query that doesn't filter on column1
column2, column1, column3, column4, column5, column6
Good for queries of the form
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3 and Column4 = @var4
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3
WHERE Column1 = @var1 and Column2 = @var2
WHERE Column2 = @var2
Not seekable by any query that doesn't filter on Column2
column3, column1, column2, column4, column5, column6
Good for queries of the form
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3 and Column4 = @var4
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3
WHERE Column1 = @var1 and Column3 = @var3
WHERE Column3 = @var3
Not seekable by any query that doesn't filter on Column3
column4, column1, column2, column3, column5, column6
Good for queries of the form
WHERE Column1 = @var1 and Column2 = @var2 and Column3 = @var3 and Column4 = @var4
WHERE Column1 = @var1 and Column2 = @var2 and Column4 = @var4
WHERE Column1 = @var1 and Column4 = @var4
WHERE Column4 = @var4
Not seekable by any query that doesn't filter on Column4
If this query is the only one you have, then the order isn't much of an issue. If you have other queries that filter on subsets of the 4 columns, it is. If column5 and column6 are never in the where, just in the select, make them include columns, rather than putting them into the index key
Have a look at this post that I wrote about index key order - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2009 at 9:05 am
Fraggle (2/26/2009)
However, this is not the case. I do get an index seek with INDEX1.
What you're getting is a seek on column1 followed by a filter on column6. The only way you'll get a seek on column1, column6 together is if they are the 1st and 2nd columns in the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2009 at 9:14 am
Gila,
If that is the case and I am getting a filter on column 6, then why does the exection plan say
Index Seek -> Select
with no filter or any other steps showing up? I seem to remember that the execution plan should show something like this.
The query actually reads
select Column4, Column5
from Table1
where Column1 = X
and Column6 = Y
and it says that it is doing an index seek using Index1 (column1,2,3,4,5,6)
Thanks,
Nathan
February 26, 2009 at 9:29 am
Fraggle (2/26/2009)
Gila,If that is the case and I am getting a filter on column 6, then why does the exection plan say
Index Seek -> Select
with no filter or any other steps showing up?
Look at the properties of the index seek.
You'll see a seek predicate on column1 and a predicate on column6. That's the filter. It doesn't appear in a separate step, because it's done as part of the index seek
It's the difference between finding all the rows that match on column1 and, while reading them, eliminate the ones that don't match on column 6 or finding the rows that match on column1 and column6 without having to look at rows that don't match.
See - http://sqlinthewild.co.za/index.php/2008/04/23/execution-plans-important-properties/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2009 at 10:07 am
And the light bulb has gone off. Thanks for the great explanations guys and gals.
Fraggle
February 26, 2009 at 11:01 pm
If column one contains, say, whole numbers greater than zero and you want the index seek without contraining column 1 to a single value, the following will usually all it all...
select Column4, Column5
from Table1
where Column1 [font="Arial Black"]> 0[/font]
and Column6 = Y
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2009 at 1:51 am
That'll say index seek but, behind the scenes it's almost equivalent to an index scan.
Seek to find the start of the range for column 1 - the beginning of the table. Read all pages, filtering out values for column 6 that don't match. Keep reading until the end of the range on Column1 is reached. Since there is not upper bound, that's the last page in the table. Return matching rows.
I have a half-written blog post on this. Must see if I can finish it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply