Instead of virtual fields/indexes? -MySQL

Databases
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!

Bagtjesen

2

use an index on the entire datetime column

performance is impaired only if you use DATE(RecDate) for searching

so

WHERE DATE(RecDate) = '2022-02-12'

will require a complete table scan, since you are applying a function to the column value

however,

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'
3

Thanks Rudy,

Yes, I have been testing while waiting for some explanations (Thanks!), and I have verified that you suggestion works giving the best performance possible.

Regards,

Bagtjesen