Announcement

Announcement Module
Collapse
No announcement yet.

comparing datetime with string is not using index

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • comparing datetime with string is not using index

    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
    http://bugs.mysql.com/bug.php?id=52849
    ..in my tables I am using utf8_unicode_ci as collation
Working...
X