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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
