🚀 go-pugleaf

Hi,

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



-- 
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com