title: mysql datetime indexed column - varied query response time
body: <p>EVENT_TIME is an indexed datetime column (Table Definition below). And almost all of my queries is based on EVENT_TIME column. I need a wide variety of options (like between, group by, DATE(EVENT_TIME),HOUR(EVENT_TIME) etc) to base my queries.</p>
  
  <p>Not sure why there is huge difference between = and (like and DATE(EVENT_TIME)) conditions as mentioned below.</p>
  
  <p>Any suggestions to query the most fastest way of say  <strong>per day</strong>-DATE(EVENT_TIME=today()), <strong>last 4 hours</strong> etc</p>
  
  <pre><code>select * from 4gc_1h_atch_enb where EVENT_TIME='2014-04-08 00:00:00'
  </code></pre>
  
  <p>60743 row(s) returned<br>
  <strong>0.062 sec / 3.229 sec</strong></p>
  
  <pre><code>select * from 4gc_1h_atch_enb where EVENT_TIME like '2014-04-08 00:00:0%'
  </code></pre>
  
  <p>60743 row(s) returned<br>
  <strong>449.875 sec / 7.988 sec</strong></p>
  
  <pre><code>select * from 4gc_1h_atch_enb where date(EVENT_TIME)='2014-04-08' and time(event_time)='00:00:00'
  </code></pre>
  
  <p>60743 row(s) returned<br>
  <strong>401.094 sec / 9.063 sec</strong></p>
  
  <p><strong>Table Definiton</strong></p>
  
  <pre><code>mysql&gt; show create table 4gc_1h_atch_enb \G
  *************************** 1. row **********************
         Table: 4gc_1h_atch_enb
  Create Table: CREATE TABLE `4gc_1h_atch_enb` (
    `ELEMENT_TYPE` varchar(32) NOT NULL,
    `NE_VERSION` varchar(32) NOT NULL,
    `NE_ID` int(10) unsigned DEFAULT NULL,
    `NE_NAME` varchar(32) NOT NULL,
    `EVENT_TIME` datetime DEFAULT NULL,
    `ENB` varchar(32) NOT NULL,
    .
    .
    .
  TRUNCATED FOR READABILITY
    .
    .
    .
    `MarketName` varchar(32) NOT NULL,
    KEY `iNE_VERSION` (`NE_VERSION`),
    KEY `iNE_ID` (`NE_ID`),
    KEY `iNE_NAME` (`NE_NAME`),
    KEY `iEVENT_TIME` (`EVENT_TIME`),
    KEY `iENB` (`ENB`),
    KEY `iMarketName` (`MarketName`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)
  
  mysql&gt;
  </code></pre>