MySQL Bugs: #1343: index by date part of datetime field

Bug #1343	index by date part of datetime field
Submitted:	18 Sep 2003 14:14	Modified:	23 Jun 2015 13:56
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It would be really nice if you could index by just the date part of a datetime field. But it's just not possible, right?

How to repeat:
Try to index on just date part of datetime field.

Thank you for a feature request. It can be implemented some day, when expression-based indexes will be supported in MySQL. 

In the meantime you can use character columns for storing DATETIME values as strings, with only first N characters being indexed. With some careful usage of triggers in MySQL 5 you can create a reasonably robust solution based on this idea.

Wouldn't it be easier to implement and to use, if you define the accuracy like on the string-index instead of a function? A function-based index seems to be quite complex to use, whereas the accuracy is already there. I would think of accuracy in years, year-months, year-month-days for a start. And on datetime-columns it could even be hourwise.

It could be defined like 

alter table accesslog add index acdat(accesstime(8));

I'd see one great advantage:

On statistical queries you have a lot of possible restrictions (say columns like browser_id, tcpip_country, is_guest), and mostly querying starts with a time restriction combined with one of them.

So you end up in three indexes, all of them almost the cardinality of the whole table, because datetime is too exact. And the datetime-index is always the end of the chain, because it leads directly to a small number of entries.

Posted by developer:
 
I am closing this bug now, as the functionality has been implemented in WL#411 via generated columns.  If you believe this to be in error, please feel free to re-open this feature request and provide information on what functionality is missing.  Thanks!