title: Why isn't mySQL using my index in my test query?
body: <p>I have a simple mysql table I want to use for daily stat tracking.  After putting some information into it and testing out a samply query I'd use on it, it's not using my index!  I can't figure out why.</p>
  
  <p>I tried putting in more test data - 200 rows.  I also tried changing select * to select created,value1.  No dice.</p>
  
  <pre><code>mysql&gt; show create table stat_general\G
  *************************** 1. row ***************************
         Table: stat_general
  Create Table: CREATE TABLE `stat_general` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `nameid` smallint(6) NOT NULL,
    `value1` int(11) NOT NULL,
    `value2` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `created` (`created`,`nameid`,`value2`)
  ) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)
  
  mysql&gt; select * from stat_general;
  +----+---------------------+--------+--------+--------+
  | id | created             | nameid | value1 | value2 |
  +----+---------------------+--------+--------+--------+
  |  1 | 2011-06-09 09:43:41 |      1 | 511803 |   NULL |
  |  2 | 2011-06-09 09:43:41 |      2 |     44 |   NULL |
  |  3 | 2011-06-09 09:43:41 |      3 |   9128 |   NULL |
  |  4 | 2011-06-09 09:43:41 |      4 |    219 |   NULL |
  |  5 | 2011-06-09 09:43:41 |      5 |      0 |   NULL |
  |  6 | 2011-06-09 09:43:41 |      6 |      0 |   NULL |
  |  7 | 2011-06-09 09:43:41 |      7 |      0 |   NULL |
  |  8 | 2011-06-09 09:43:41 |      8 |      0 |   NULL |
  |  9 | 2011-06-09 09:43:41 |      9 |      0 |   NULL |
  | 10 | 2011-06-09 09:43:41 |     10 |    140 |   NULL |
  | 11 | 2011-06-09 09:43:41 |     11 |      0 |   NULL |
  | 12 | 2011-06-09 09:43:41 |     12 |    146 |   NULL |
  | 13 | 2011-06-09 09:43:41 |     13 |      0 |   NULL |
  | 14 | 2011-06-09 09:43:41 |     14 |      1 |   NULL |
  | 15 | 2011-06-09 09:43:41 |     15 |   8981 |   NULL |
  | 16 | 2011-06-09 09:43:41 |     16 |      1 |   -127 |
  | 17 | 2011-06-09 09:43:41 |     16 |      2 |     -2 |
  | 18 | 2011-06-09 09:43:41 |     16 |    939 |     -1 |
  | 19 | 2011-06-09 09:43:41 |     16 |    146 |      1 |
  | 20 | 2011-06-09 09:43:41 |     16 |   8011 |      3 |
  | 21 | 2011-06-09 09:43:41 |     16 |     28 |    127 |
  | 22 | 2011-06-09 09:43:41 |     16 |      1 |    128 |
  | 23 | 2011-06-09 09:43:41 |     17 |    146 |      1 |
  | 24 | 2011-06-09 09:43:41 |     18 |    146 |      1 |
  | 25 | 2011-06-09 09:44:08 |      1 | 511803 |   NULL |
  | 26 | 2011-06-09 09:44:08 |      2 |     44 |   NULL |
  | 27 | 2011-06-09 09:44:08 |      3 |   9128 |   NULL |
  | 28 | 2011-06-09 09:44:08 |      4 |    219 |   NULL |
  | 29 | 2011-06-09 09:44:08 |      5 |      0 |   NULL |
  | 30 | 2011-06-09 09:44:08 |      6 |      0 |   NULL |
  | 31 | 2011-06-09 09:44:08 |      7 |      0 |   NULL |
  | 32 | 2011-06-09 09:44:08 |      8 |      0 |   NULL |
  | 33 | 2011-06-09 09:44:08 |      9 |      0 |   NULL |
  | 34 | 2011-06-09 09:44:08 |     10 |    140 |   NULL |
  | 35 | 2011-06-09 09:44:08 |     11 |      0 |   NULL |
  | 36 | 2011-06-09 09:44:08 |     12 |    146 |   NULL |
  | 37 | 2011-06-09 09:44:08 |     13 |      0 |   NULL |
  | 38 | 2011-06-09 09:44:08 |     14 |      1 |   NULL |
  | 39 | 2011-06-09 09:44:08 |     15 |   8981 |   NULL |
  | 40 | 2011-06-09 09:44:08 |     16 |      1 |   -127 |
  | 41 | 2011-06-09 09:44:08 |     16 |      2 |     -2 |
  | 42 | 2011-06-09 09:44:08 |     16 |    939 |     -1 |
  | 43 | 2011-06-09 09:44:08 |     16 |    146 |      1 |
  | 44 | 2011-06-09 09:44:08 |     16 |   8011 |      3 |
  | 45 | 2011-06-09 09:44:08 |     16 |     28 |    127 |
  | 46 | 2011-06-09 09:44:08 |     16 |      1 |    128 |
  | 47 | 2011-06-09 09:44:08 |     17 |    146 |      1 |
  | 48 | 2011-06-09 09:44:08 |     18 |    146 |      1 |
  +----+---------------------+--------+--------+--------+
  48 rows in set (0.00 sec)
  
  mysql&gt; select * from stat_general where created&gt;'2011-06-09 9:44' AND nameid=1;
  +----+---------------------+--------+--------+--------+
  | id | created             | nameid | value1 | value2 |
  +----+---------------------+--------+--------+--------+
  | 25 | 2011-06-09 09:44:08 |      1 | 511803 |   NULL |
  +----+---------------------+--------+--------+--------+
  1 row in set (0.00 sec)
  
  mysql&gt; explain select * from stat_general where created&gt;'2011-06-09 9:44' AND nameid=1;
  +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
  |  1 | SIMPLE      | stat_general | ALL  | created       | NULL | NULL    | NULL |   48 | Using where |
  +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
  1 row in set (0.00 sec)
  </code></pre>