zhenxing zhai wrote:
> ...
> where
> LEFT(r.datestr,6)=DATE_FORMAT(now(),'%Y%m')
> ....
> query is so slow because scan full table for table r,but having index on r.datestr and r.datestr(6)
At present, MySQL cannot use an index on a column when this column is the argument of a function (although with the LEFT() function it should be obvious ;-).
> if I change condition
> 'LEFT(r.datestr,6)=DATE_FORMAT(now(),'%Y%m') '
> to
> 'r.datestr=DATE_FORMAT(now(),'%Y%m%d') '
These are not equivalent. You may want to consider
r.datestr LIKE CONCAT(DATE_FORMAT(NOW(), '%Y%m'), '%')
which should use the index on r.datestr.
--
felix
Please use
BBCode to format your messages in this forum.