mysql between dates Q.

[devsie]

I have been using

AND opt_cal.date LIKE '".$year."-".$month."%'

to find dates for a specific month in my database.

Just wondering if (in terms of possible glitchs or optimisation) there is any problem doing this rather than something like...

AND date BETWEEN '2010-01-01' AND '2010-01-31'

Thanks 🙂

[NogDog]

I would think the BETWEEN should be more efficient, as it will be reduced to a simple arithmetic comparison which can take full advantage of column indexing, whereas the LIKE will probably require a full table read, as each date column would have to be converted to a string before the LIKE comparison is done against the value.

PS: possibly somewhere in between would be using the YEAR() and MONTH() functions, e.g.:

...WHERE YEAR(col_name) = 2010 AND MONTH(col_name) = 1

[johanafm]

NogDog;10939856 wrote:

PS: possibly somewhere in between would be using the YEAR() and MONTH() functions, e.g.:

Indexing still will not work in this case as the result of those functions is "unknown" until they are evaluated, which once again means a full table scan.

[Weedpacket]

MySQL doesn't have expression indexes?

[johanafm]

Not as far as I know. There are of course lots of things I don't know, so I may be misstaken, but looking at a an EXPLAIN containing WHERE year(somedate) = someyear, there are no possible keys listed, as opposed to WHERE somedate BETWEEN start AND end. Where somedate obviously is indexed.