🚀 go-pugleaf

Wow, thanks guys.  These are some really great ideas.  Martijn, that's a 
great point.  You're quite right about the index on month - only 12 values 
isn't a great candidate for an index.  Combining the fields into one (a 
Month_Day field) is a much better idea.  I'll certainly do that.

Thomas, using a trigger to keep this Month_Day field updated is a great 
idea.   That way I don't have to modify my application.   :)

Thanks again!
- Jason

"Martijn Tonies" <m.tonies@upscene.removethis.com> wrote in message 
news:472777c9$1@newsgroups.borland.com...
>
>> > I think I may have found a solution.  I have stumbled across a 
>> > function,
>> > EXTRACT.
>> >
>> > select * from CONTACTS where EXTRACT (DAY from CBIRTHDAY) > 1
>> > select * from CONTACTS where EXTRACT (MONTH from CBIRTHDAY) > 1
>> > select * from CONTACTS where EXTRACT (YEAR from CBIRTHDAY) > 1950
>> >
>> > What worries me though is execution time.  If I had say 500,000 
>> > contacts
>> > in this table, would it have to brute-force (for-loop) its way through
>> > all these records extracting the month and day from each Contact's
>> > birthdate?
>> >
>> > If I created seperate fields BirthMonth and BirthDay (Integers) I could
>> > index on them, and make sure execution time is fast.  If I have the the
>> > Contact's Birthday field (CBIRTHDAY) indexed, will execution time be
>> > fast? (I'm under the impression that Date fields are stored as
>> > Double/Floating point numbers, and indexing them puts them in order by
>> > linear time.   I don't see how these queries (listed above) could
>> > extract the Month or Day values from the date field without looking at
>> > each individual Birthdate, each and every time this query runs... I
>> > don't think we want to do that.)
>>
>> The optimizer won't be able to use an index on CBIRTHDAY when using
> EXTRACT.
>>
>> You have to use seperate indexed fields as you have pointed out, where
>> the content of the field could be maintained via triggers at the
>> server-side.
>
> Mind you, an index on "month" wouldn't be much good on this number of
> rows, cause it's but 12 different values. "Day" would be slightly better,
> but
> nothing much really.
>
> Perhaps a string value "month-day" and a query of
> WHERE MONTH_DAY BETWEEN '01-01' AND '04-01' would be better?
>
> Oh heck, I don't know, just saying it's not an ideal candidate for an 
> index
> :-)
>
> -- 
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle 
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>