On Tuesday 04 Dec 2001 13:49, Emil Rasmussen wrote:
> Hey
>
> Does anyone have some optimization tips for this query:
>
> SELECT count(id) FROM tblTabel WHERE fieldName != '' AND
> DATE_FORMAT(myDateField,'%Y%m%d') = 20011120;
>
> It does not use the index i have created, so it scans the entire table,
> which is a bit to slow.
>
> The problem off course is, that MySQL has to do a calculation on the
> myDateField before it can decide if the row matches. Any "workarounds" for
> this?
How about:
SELECT count(id)
FROM tblTabel
WHERE fieldName != '' AND
(
myDateField >= 20011120000000 AND
myDateField < 20011130000000
);
That will use indices, and yield the same results. It will also consume less
CPU time because you don't have to play with string parsing for the date
formatting.
HTH.
Gordan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
