🚀 go-pugleaf

> > 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