Actually i had this same problem before too when i worked with date based alerts (scheduling)for mobile and the best advice i got was using timestamp date field since its supposed to be closer to int type datafield which was optimized with how mysql implemented indexing (search it out, i think it is still hailed the best) but i figured i rather take the pain of managing date values using it and it worked fine because i can use mathematical equations to filter out the records with int type dates rather than logical/conditional processing (yeah, its a really extreme solution).
Of course if it really boils down to performance try MonetDB ( http://www.monetdb.nl), IMHO, the nearest FOSS alternative to Oracle's TimesTen, however you just got to have a lot of RAM though since its in-memory DB (MMDB). On Jan 17, 2008 6:25 PM, Robert Locke <[EMAIL PROTECTED]> wrote: > > I think Tiger's dissertation on the subject said it all. =) I'm not sure > what > using an int datatype would buy you above and beyond just indexing > datedue. I > suppose using int *might* be slightly faster, but you would lose date > functionality. And, from a purist's perspective, call a spade a spade - > if > something is a date, use a date type. But maybe that's why you said it > was an > "extreme" solution. =) > > Rob > > > On 01 17, 08, at 6:12 PM, Roger Filomeno wrote: > > > Extreme solution: > > > > 1. change date field from datetime or timestamp to int type equivalent. > > Warning do not use alter table, data will be lost; make a script to re > create > > the date data as int using unix_timestamp instead. > > > > 2. index the field. > > > > 3. rewrite sql as select id,datedue from table1 where datedue > > > unix_timestamp("2006-01-01 01:01:01") . > > > > avoid use of select *, if possible get only the data you need > > > > On Jan 17, 2008 3:56 PM, joebert jacaba <[EMAIL PROTECTED]> wrote: > > > > > > I have indexed due_date and account and got much better performance > already. > > > > > > I don't really want to split the date into 1 or 2 ints. I use > > > Hibernate and I want to maximize GregorianCalendar. I need to perform > > > complex date operations I don't want to rewrite this functions. I rely > > > heavily on Criteria and Projection. > > > > > > Joebert > > > _________________________________________________ > > > Philippine Linux Users' Group (PLUG) Mailing List > > > [email protected] (#PLUG @ irc.free.net.ph ) > > > Read the Guidelines: http://linux.org.ph/lists > > > Searchable Archives: http://archives.free.net.ph > > > > > > -- > > -- > > Roger P. Filomeno > > International Project Manager > > TechBiz Asia Group Pte Ltd > > > > http://corruptedpartition.blogspot.com/ > > send MSG GODIE <YOUR MESSAGE> to 2948 > > > > $> who | grep -i blond | date; cd ~; unzip; touch; strip; finger; mount; > gasp; > > yes; uptime; umount; > sleep_________________________________________________ > > Philippine Linux Users' Group (PLUG) Mailing List > > [email protected] (#PLUG @ irc.free.net.ph) > > Read the Guidelines: http://linux.org.ph/lists > > Searchable Archives: http://archives.free.net.ph > > _________________________________________________ > Philippine Linux Users' Group (PLUG) Mailing List > [email protected] (#PLUG @ irc.free.net.ph) > Read the Guidelines: http://linux.org.ph/lists > Searchable Archives: http://archives.free.net.ph > -- -- Roger P. Filomeno International Project Manager TechBiz Asia Group Pte Ltd http://corruptedpartition.blogspot.com/ send MSG GODIE <YOUR MESSAGE> to 2948 $> who | grep -i blond | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
_________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List [email protected] (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
