468,784 Members | 1,573 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,784 developers. It's quick & easy.

Indexing Date/Time fields.

If we have a date/time field and are doing frequent queries
WHERE {date/time field} BETWEEN TimeA AND TimeB

Does it make sense, query speed wise, to create an index on the date/time
field?

The reason I ask is that TimeA and TimeB are significant down to seconds.
My *assumption* is that with a large amount of data scatter very few records
will contain duplicate date/time points. And that under these circumstances,
indexing will be of little value since every date/time point will need to be
examined in detail. Date/time fields are actually floating point numbers
after all.

In other words, the assumption I am making is that if most of the data in a
field is unique, then indexing will yield little or no query performance
benefit.

Is my "assumption" valid?
Would an index on the date/time field speed up this type of query?

Thomas Bartkus
Jul 23 '05 #1
1 4618
"Thomas Bartkus" <to*@dtsam.com> wrote in message
news:5a********************@telcove.net...
If we have a date/time field and are doing frequent queries
WHERE {date/time field} BETWEEN TimeA AND TimeB

Does it make sense, query speed wise, to create an index on the date/time
field?

The reason I ask is that TimeA and TimeB are significant down to seconds.
My *assumption* is that with a large amount of data scatter very few records will contain duplicate date/time points. And that under these circumstances, indexing will be of little value since every date/time point will need to be examined in detail. Date/time fields are actually floating point numbers
after all.

In other words, the assumption I am making is that if most of the data in a field is unique, then indexing will yield little or no query performance
benefit.

Is my "assumption" valid?
Would an index on the date/time field speed up this type of query?


Okay. I'll answer my own question just in case anyone is interested.

I ran a few tests and my assumption was *wrong*.

Indexing on a field that contains mostly or entirely discrete values helps
the query speed substantially. Indexing on date/time fields or floating
point fields with lots of data scatter does speed up queries involving those
fields in a WHERE clause restriction.

This was a surprise to me!
Thomas Bartkus


Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by John Sidney-Woollett | last post: by
6 posts views Thread by Hennie7863 | last post: by
1 post views Thread by brino | last post: by
2 posts views Thread by =?Utf-8?B?SmVycnkgQw==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.