Not really sure if this is a bug but it looks like one… I have a log table with a datetime field on it that is indexed, running a query that tries to compare that field with a string will not use index:
EXPLAIN SELECT * FROM log_activities WHERE activityDate = ‘2012-06-01 13:47:31’;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE log_activities ALL NULL NULL NULL NULL 162069 Using where
what I just realised is that casting this to datetime will result in index usage
EXPLAIN SELECT * FROM log_activities WHERE activityDate = cast(‘2012-06-01 13:47:31’ as datetime);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE log_activities ref activityDate activityDate 8 const 1 Using where
my q is is this a bug or intended? …should I report it somewhere (and where)?
PS: found something similar in mysql bugs
[URL]MySQL Bugs: #52849: datetime index not work
…in my tables I am using utf8_unicode_ci as collation