title: Optimizing Datetime fields where indexes aren't being used as expected
body: <p>I have a large, fast-growing log table in an application running with MySQL 5.0.77. I'm trying to find the best way to optimize queries that count instances within the last X days according to message type:</p>
  
  <pre><code>CREATE TABLE `counters` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `kind` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_counters_on_kind` (`kind`),
    KEY `index_counters_on_created_at` (`created_at`)
  ) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>For this test set, there are 668521 rows in the table. The query I'm trying to optimize is:</p>
  
  <pre><code>SELECT kind, COUNT(id) FROM counters WHERE created_at &gt;= ? GROUP BY kind;
  </code></pre>
  
  <p>Right now, that query takes between 3-5 seconds, and is being estimated as follows:</p>
  
  <pre><code>+----+-------------+----------+-------+----------------------------------+------------------------+---------+------+---------+-------------+
  | id | select_type | table    | type  | possible_keys                    | key                    | key_len | ref  | rows    | Extra       |
  +----+-------------+----------+-------+----------------------------------+------------------------+---------+------+---------+-------------+
  |  1 | SIMPLE      | counters | index | index_counters_on_created_at_idx | index_counters_on_kind | 258     | NULL | 1185531 | Using where | 
  +----+-------------+----------+-------+----------------------------------+------------------------+---------+------+---------+-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>With the created_at index removed, it looks like this:</p>
  
  <pre><code>+----+-------------+----------+-------+---------------+------------------------+---------+------+---------+-------------+
  | id | select_type | table    | type  | possible_keys | key                    | key_len | ref  | rows    | Extra       |
  +----+-------------+----------+-------+---------------+------------------------+---------+------+---------+-------------+
  |  1 | SIMPLE      | counters | index | NULL          | index_counters_on_kind | 258     | NULL | 1185531 | Using where | 
  +----+-------------+----------+-------+---------------+------------------------+---------+------+---------+-------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>(Yes, for some reason the row estimate is larger than the number of rows in the table.)</p>
  
  <p>So, apparently, there's no point to that index.</p>
  
  <p>Is there really no better way to do this? I tried the column as a timestamp, and it just ended up slower.</p>
  
  <p>Edit: I discovered that changing the query to use an interval instead of a specific date ends up using the index, cutting down the row estimate to about 20% of the query above:</p>
  
  <pre><code>SELECT kind, COUNT(id) FROM counters WHERE created_at &gt;= 
      (NOW() - INTERVAL 7 DAY) GROUP BY kind;
  </code></pre>
  
  <p>I'm not entirely sure why that happens, but I'm fairly confident that if I understood it then the problem in general would make a lot more sense.</p>