composite index

Question

composite index

Kayal-717623

Ten Centuries

Points: 1390
More actions
July 30, 2008 at 1:35 pm

#92282

I have an effective date, term date in almost all the tables. So mostly my queries has a where clause
WHERE
@Today BETWEEN EffectiveDate and TermDate
So is it wise to have composite index on these 2 dates or 2 indexes, one for Effective date and other for Term date. By the way do we really use composite index in real life scenario as i have never seen one.
Any suggedtion is greatly appreciated.
Thank You.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Having one index with both might help. Adding them to other indexes for your queries would probably be even better.

- 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

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 2

GSquared,

Thanks for ur reply. But if I add them to other index say PathID, then in the WHERE clause i should use the columns in same order of specified in the composite index right? If my WHERE clause does not use one of the field of the composite index, then that index is not used right. Is it true that the WHERE clause should have fields in the same order of the composite index fields and WHERE clause should have all the composite index fields inorder for the query to use the index.

Thanks. Appreciate it.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Mostly yes.

The order of tests in the Where clause doesn't matter. SQL will ignore that if it wants to.

The order of columns in the index matters, because of the selectivity of the first column pretty much determining the use of the index. That's an over-simplification, but it's basically correct.

What matters the most is that all the columns in the Where clause (and any used in Join clauses) are in the index.

If, for example, you have two queries that both select from the same table, and one has five columns in the Where clause, and the other has three, and two of those overlap (both queries use Col1 and Col2, say), then one index, with Col1 and Col2 first, and then the columns that the first query uses, and then the column that the second query uses, it's quite likely that both queries will use the same index, and will use it quite well. There are exceptions, but it usually works that way.

Does that help?

- 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

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 4

Hi Gsquared,

That was good explanation. Thank You.

I have link table which links to tables.Their columns are basically

LinkID(Primaty key idendity column)

OrgID(Foreign Key)

ProductID(Foreign Key)

StartDate

EndDate

IsActive(bit)

Now i wanna if i have a index like this (OrgID,ProductID,StartDate,EndDate)

The circumstances this index will not be used is below:

----------------------------------------------------

1. If my where clause donot contain OrgID though it has ProductID, StartDate, EndDate

The Circumstances this index will be used:

-----------------------------------------

1. All the columns are used in the index

2. Any Column used in combination with OrgID as it is the first column.

Am I right ?

Thanks for your reply. I appreciate it :).

GSquared SSC Guru Points: 260824 More actions · Answer 5

In that particular case, it might use the index, but if so it will probably be an index scan instead of an index seek.

Would StartDate be selective enough to have that as the first column of the index? Do all/most queries of that table have StartDate and EndDate in the Where clause? If so, then an index on StartDate, EndDate, OrgID, ProductID, might be better and might give you good performance.

- 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

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 6

Thank You very much, Gus. I appreciate all your help.

GSquared SSC Guru Points: 260824 More actions · Answer 7

You're welcome.

- 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