bagtjesen — 2011-03-03T13:34:45-05:00 — #1
I use MySQL 5.1 where one of the databases is InnoDB.
I have a datetime field called RecDate which stores '2011-03-03 19:24:00' that is ... always Date through to Minute - no seconds. That is a Primarary Key field. And in most selects it is very fast, but when used in conjunction with DATE(RecDate) it becomes slow. Here is an example:
SELECT DATE(RecDate), AVG(t) FROM Oa GROUP BY DATE(RecDate)
or even subsets of data using WHERE MONTH(RecDate) = ...
From using other databases earlier on I have used tricks like virtual fields or "virtual" indexes. I was hoping I could do something like:
ALTER TABLE Oa ADD INDEX dateRC(DATE(RecDate))
or adding a virtual field and building an index on that, but that is is obviously just in the consideration or in the beginning of development for MySQL...
... What can I do in the meanwhile?
Thanks for any suggestions!
r937 — 2011-03-04T08:11:53-05:00 — #2
use an index on the entire datetime column
performance is impaired only if you use DATE(RecDate) for searching
WHERE DATE(RecDate) = '2022-02-12'
will require a complete table scan, since you are applying a function to the column value
WHERE RecDate >= '2022-02-12'
AND RecDate < '2011-02-13'
will use the index most efficiently
just use the same technique when pulling out a whole month --
WHERE RecDate >= '2022-02-01'
AND RecDate < '2011-03-01'
bagtjesen — 2011-03-04T11:12:15-05:00 — #3
Yes, I have been testing while waiting for some explanations (Thanks!), and I have verified that you suggestion works giving the best performance possible.