> > 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.
Just thought of something else. I don't know if MySQL can do this, but here
is something that PostgreSQL lets you do. I haven't tried it on MySQL, but
here it is anyway.
Your query would use an index if the index was created on:
DATE_FORMAT(myDateField,'%Y%m%d')
as stated in your query.
So, you would want to do something like:
CREADE INDEX myDateField_Index
ON tblTabel
(
DATE_FORMAT(myDateField,'%Y%m%d')
);
This may or may not work, but it's worth a shot, if you prefer your query in
the format it was in before.
Regards.
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
