| Author |
Topic |
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
 Posted - 2010-04-10 : 05:08:33
|
| What is the difference b/w Two indexes on two different columns and one index on combination on two columns...??Vaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-10 : 05:19:56
|
Assuming:Index1 on ColAIndex2 on ColBIndex3 on ColA, ColBVery broadly speaking!:Index1 will be used on a query which has ColA in the WHERE clause Index2 will be used on a query which has ColB in the WHERE clauseSOMETIMES an intersection of Index1 and Index2 will be used on a query which has both ColA and ColB in the WHERE clauseIndex3 will be used on a query that has both ColA and ColB in the WHERE clause, and also on a query which has ColA in the WHERE clause (same as first case above - so if you have Index3 there is NO point also having Index1).Index3 will also be used to cover the query if it has ColA in WHERE cluase and ColB (and also ColA) in the SELECT (and no other columns from that table) and similarly will be used to "cover" ColA and ColB in the ORDER BY, GROUP BY, HAVING etc. clauses e.g.SELECT ColBFROM MyTableWHERE ColA BETWEEN 'A' AND 'B' is an ideal candidate for Index3, but where there is no Index3 then Index1 would be used for the WHERE clause (broadly speaking!) |
 |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-04-10 : 05:36:50
|
| That means instead of keeping index1 and index2 i should keep index3 as it is being used in all the cases...Right??Vaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-10 : 06:20:06
|
quote:
Originally posted by vaibhavktiwari83
That means instead of keeping index1 and index2 i should keep index3 as it is being used in all the cases...Right??
AFAIK, Index 3 won't be used for queries that only have ColB in the where clause. So if you want have queries with (ColA), (ColB) and (ColA + ColB) in the where clause, then you'll need index 1 and 2.I'm pretty sure this is correct, but hopefully someone else can confirm.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-10 : 08:20:45
|
"AFAIK, Index 3 won't be used for queries that only have ColB in the where clause"SQL can do that, but its rare (so best to assume it won't happen!)"So if you want have queries with (ColA), (ColB) and (ColA + ColB) in the where clause, then you'll need index 1 and 2."Actually I would suggest that if you have "queries with (ColA), (ColB) and (ColA + ColB)" that you have indexes 2 and 3  as Index3 will be used in place od Index1, and also for ColA + ColB scenarios. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-10 : 08:49:48
|
quote:
Originally posted by KristenActually I would suggest that if you have "queries with (ColA), (ColB) and (ColA + ColB)" that you have indexes 2 and 3 as Index3 will be used in place od Index1, and also for ColA + ColB scenarios.
Yeah, that's what I meant to post. (This is what happen when you post while your wife is hassling you over doing the vacuuming. :) )Index 2 will service the WHERE ColB queries, and Index 3 will service the WHERE ColA and WHERE COlA + ColB queries.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-04-10 : 09:03:46
|
| I am not getting why index3 will not service for where colB as it is serving for ColA.If i make an index with the sequence colB then ColA then ColB with be replace colA in each scenario. is is like that...Vaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-10 : 09:16:42
|
quote:
Originally posted by vaibhavktiwari83
I am not getting why index3 will not service for where colB as it is serving for ColA.
Because ColA comes first in the index. Imagine this.ColA ColB1 11 41 72 22 32 53 33 83 9 This is how they're be stored in an index on ColA + ColB. Now, it's pretty easy to list the values on ColA in order here, isn't, because they're already in order. But it's difficult to list the values of ColB, Even though they're in an index, because ColA Is the primary sort field, and ColB is the secondary sort field. ColB by itself it in an almost random order.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-04-10 : 09:27:13
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by vaibhavktiwari83
I am not getting why index3 will not service for where colB as it is serving for ColA.
Because ColA comes first in the index. Imagine this.ColA ColB1 11 41 72 22 32 53 33 83 9 This is how they're be stored in an index on ColA + ColB. Now, it's pretty easy to list the values on ColA in order here, isn't, because they're already in order. But it's difficult to list the values of ColB, Even though they're in an index, because ColA Is the primary sort field, and ColB is the secondary sort field. ColB by itself it in an almost random order.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
Yup i understood. thanks for such good explanationVaibhav TTo walk FAST walk ALONE To walk FAR walk TOGETHER |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-12 : 06:45:11
|
| http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/--Gail ShawSQL Server MVP |
|
|
|
|
Indexing