MySQL Datetime index

General Discussion
,
1

Not really Ignition related, but I was trying to optimize a table in MySQL and added an index on a datetime column. It didn’t seem to make much difference. I then noticed this:

So, this query:

WHERE DATE(x) = '2016-09-01'   /* slow! */

This one,

WHERE x >= '2016-09-01'
   AND x <  '2016-09-01' + INTERVAL 1 DAY  /* fast! */

Do other databases act in this way?

2

I would say yes, for the same reason given in the article. If you use a function in the where clause it will perform that function on every row in the table during the query before returning results.

2 Likes