| Author |
Topic |
|
sixsigma1978
Starting Member
25 Posts |
 Posted - 2011-08-11 : 13:29:10
|
All - I'm new to SQL and have a (hopefully) straightfoward indexes question I'm hoping to get clarified.Lets say I have a table T with columns C1, C2 , C3, C4.I have two indexes I1 on columns (C1, C2)and I2 on columns (C3,C4)and have a query like this : Select C1, C2, C3, C4 from T where C2=something and C3=something Would indexing even work here considering I don't have an Index on (C2, C3)? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-11 : 13:31:56
|
| are both indexes non clustered?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 13:36:23
|
| Yes - They're all non-clustered |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-11 : 13:39:54
|
| i think it still go for a index scan on one of them depending on selectivity of values you're trying to search in it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 13:41:25
|
| Will I get any improvement if I create another Index on C2, C3? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-11 : 13:48:13
|
| nope. as you're selecting all columns it will still get only index scan i guess------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 13:51:29
|
| Oh - I guess I should ask the more basic question then - I thought Index selection happened based on the where clause! Is this incorrect?I assumed my WHERE clause said where C2=x and C3=y and If I create a new composite Index on (C2,C3) - my SELECT * would still be fast considering my WHERE criteria completely encompasses the (new) Index (C2 and C3) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-11 : 14:34:41
|
quote:
Originally posted by sixsigma1978
Will I get any improvement if I create another Index on C2, C3?
Depends what % of the table you're selecting. Let's see if I can find a blog post on this....http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/--Gail ShawSQL Server MVP |
|
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 14:41:59
|
| I see. So to sum up all these casesIf I did:Select C2, C3 from T where C2=x and C3=y and I create an Index on (C2,C3) ->Improvement because no lookup.If did:Select * from T where where C2=x and C3=y and I create an Index on (C2,C3) ->Improvement but not as much as above as DB would have to lookup C1 and C2However if I had ONLY these Indexes - (C1, C2) and (C3, C4) and did:Select * from T where where C2=x and C3=y-> This would perform the WORST of the three use cases as no composite Index exists on (C2,C3)!!This about right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 15:04:35
|
| I thought this was the covering index in my last post-Select C2, C3 from T where C2=x and C3=y and I create an Index on (C2,C3) ->Improvement because no lookup.Both the indexes C2 and C3 are covered in Select clause. Since both C2 and C3 are indexed - and C1 and C2 need not be looked up etc.My real use case is what you suggested in your last post - Select * from T where C2=x and C3=y. Since I already have an index C3,C4 - I guess I am already getting some performance boost!! My intention was to get the best of all the cases which I assumed was obtainable by creating an Index on (c2, c3) - to get to this in performance scale : Index (C2,C3) > Index (C3,C4) based on our conversations!! |
|
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 15:10:24
|
| BTW - this is a great community - I don't believe I've EVER seen such a quick response on any forum!!Kudos!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2011-08-11 : 15:33:08
|
| Gotcha!! This was of great help!! Thanks :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Indexes question
Tara Kizer