From: "Brent Baisley" <brent@stripped>
> Without breaking the "date" up into it's separate parts, you can't use
> an index, so you will always do a full table scan. Your searches will
> get slower as you add more records.
That was what I feared; I was just hoping that MySQL wouldn't treat the
DATETIME column type as a variation of a string or an integer (with a set of
functions to extract various parts of the datetime), but as a type with a
special kind of indexing, etc.
Searching for month + date or other parts of a datetime is pretty common and
it would be useful to be able to do these kind of operations without storing
the same data in more than one place.
> I don't know how many records you
> are searching on in your example, but if you have a lot, the difference
> is pretty minimal and may be due to slightly different loads on the
> computer. Although the DATE_FORMAT one has the extra overhead of
> formating every single record to do the comparison.
The tests were made on a slow test server with about 10,000 records (if
queries run fast on this server they'll be blazingly fast on the production
machines ;-) )
Regards, Jigal.
