Zeugswetter Andreas SB wrote:
>
> > Your ideas for selecting based on the date are intriguing, however the
> > schema of the db was not done with that in mind. Everyone thinks I'm a
> > nut when I say this, but the date is stored in a char(14) field in
> > gregorian format: 19990101125959
>
> Perfect, that makes it a lot easier:
>
> 1. index on (mail_list, mail_date)
> 2. SELECT mailid, mail_date, mail_is_followup, mail_from, mail_subject
> FROM mail_archive WHERE mail_list=35
> AND mail_date between '20000100' and '20000199'
> ORDER BY mail_list DESC, mail_date DESC LIMIT 26 OFFSET 0;
>
> Note the appended 00 and 99 which is generic for all months.
shouldn't it be between '20000100000000' and '20000199000000'?
I've never indexed that date column, because it is likely that there are
3 million+ different dates in there - remember 4 million emails sent
over the course of 15 years are likely to have a lot of different dates,
when the hour/minute/second is attached.
You still think that will work?
Tim
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723