Results 1 to 3 of 3
Mar 3, 2011, 12:34 #1
- Join Date
- Apr 2010
- 0 Post(s)
- 0 Thread(s)
Instead of virtual fields/indexes? -MySQL
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!