Index Question

Question

Index Question

abcim

SSC Eights!

Points: 879
More actions
September 29, 2010 at 4:12 am

#238921

if we have two table A(colA1,colA2,colA3) and B(colB1,colB2,colB3)
table A join with B colA1=colB2
and we want to create composite index on table A for colA2 and colA3
Is this necessary to put colA1 first left most column of the index to satisfy the rule.
eg. index(colA1, colA2, colA3) because we use cross join and where clause will be
"where colA1=colB2 and colA2='abc' and colA3 ='xyz'"

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Without further information, I'd probably put (ColA2, ColA3, ColA1) to ensure that the index will be used for the filters even if it's not feasible for the join (depending on which join is used).

p.s. if you have a join condition, it's not a cross join. You should put the join condition in the from clause rather than the where though.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abcim SSC Eights! Points: 879 More actions · Answer 2

I am not understand clearly.

I am using ,

where colA1=colB2 and colA2='abc' and colA3 ='xyz'

colA1 just used here to join with Table B.

Is the column at left most must be the first Column in Index? Here first column is colA1.

Is Columns used for only joining are no need to indexed?

p.s. if you have a join condition, it's not a cross join. You should put the join condition in the from clause rather than the where though.

Is you mean this,

SELECT ........... FROM A,B Join On A.colA1=B.colB2 WHERE colA2='abc' and colA3 ='xyz'

Then I will use your above Index?

If I use Where instead of from, then my index will work? Correct?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

abcim (9/29/2010)
Is the column at left most must be the first Column in Index? Here first column is colA1.

No. Order of predicates in the where clause is irrelevant. You can specify the three in any order, it makes no difference.

SELECT ........... FROM A,B Join On A.colA1=B.colB2 WHERE colA2='abc' and colA3 ='xyz'
Then I will use your above Index?
If I use Where instead of from, then my index will work? Correct?

The two queries are completely equivalent. There's no difference on meaning. It's just that the join in the from clause is recommended .

Either index will work. I suspect, based on the limited information that you've given that the index I've recommended will be better as the two filtering predicates (column = constant) are first in the index with the join column third. SQL won't always be able to use an index on a join column, depends what type of join it does (loop, hash or merge)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abcim SSC Eights! Points: 879 More actions · Answer 4

Thank you very much Gail,

Is we create an Index for columns used for joining?

two filtering predicates (column = constant) are first in the index with the join column third. SQL won't always be able to use an index on a join column, depends what type of join it does (loop, hash or merge)

Can you clarify this a little?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

abcim (9/29/2010)
Is we create an Index for columns used for joining?

Not sure I understand your question.

two filtering predicates (column = constant) are first in the index with the join column third. SQL won't always be able to use an index on a join column, depends what type of join it does (loop, hash or merge)
Can you clarify this a little?

What did you not understand?

Maybe worth a read... http://www.sqlservercentral.com/articles/Indexing/68439/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abcim SSC Eights! Points: 879 More actions · Answer 6

Not sure I understand your question.

I mean if I am using a column just for Joining to another table, It is worth to create an Index for this column.

What did you not understand?

I have read these articles previously. But these articles not discussed that when two tables are join, whether to create an Index for this column.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

abcim (9/29/2010)
Not sure I understand your question.
I mean if I am using a column just for Joining to another table, It is worth to create an Index for this column.

Maybe. There's no absolutely certain yes/no answer here. It's usually recommended that you do so, so that the index is there if SQL does want to use 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abcim SSC Eights! Points: 879 More actions · Answer 8

abcim

SSC Eights!

Points: 879

September 29, 2010 at 6:06 am

#1228920

Thank you very much