Hi folks,
I do a considerable amount of queries based on a date, and or date range. I
have not had much luck with optimizing these queries. In some cases I use a
date field and others a datetime field.
The following query searches through 34,000 + records, while specifiying the
exact date searches through 9 records.
'ROWS: 9 SEARCHED
explain
select
a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z
ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte
dby
from submit as a inner join re_idx as b on a.submitid = b.submitid
where a.submitdate = '2003-07-01';
ROWS: 34,000 + searched
explain
select
a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z
ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte
dby
from submit as a inner join re_idx as b on a.submitid = b.submitid
where year(a.submitdate)=2003 and month(a.submitdate)=7 and
dayofmonth(a.submitdate)=1;
--and year(a.submitdate)=2003 and month(a.submitdate)=7 and
dayofmonth(a.submitdate)<15;
Notice the 2nd where statement, this is how I typically do my date queries
(and it is slow). This is because I might also be searching for a range of
dates (as in the commented out "and" clause above).
Is the to_days function faster than these date functions, or have any effect
whatsoever?
Thanks for the help.
Karl
