title: Confused about mysql looking at half a million rows when using an index
body: <p>I have a table (location_data) with 300M rows (and growing).</p>
  
  <p>I want to find the id of the latest entry (MAX(id)) <strong>for a particular value of foo_id</strong>.</p>
  
  <ul>
  <li>Is the query below optimal?  It often takes a few seconds.</li>
  <li>Does the "rows" value of 561826 mean that MySQL is reading and scanning through all the rows for a foo_id, event though it should be able to immediately jump to the last id for a foo_id, using the composite index?</li>
  <li>Why is "keylen" 4, when it is made of two 32 bit ints?</li>
  </ul>
  
  <p>Most importantly, how can I speed up this query?</p>
  
  <pre><code>mysql&gt; explain
      -&gt; SELECT foo_id, MAX(id) id 
      -&gt; FROM location_data l   
      -&gt; WHERE l.foo_id = 253   
      -&gt; GROUP BY foo_id;
  +----+-------------+-------+------+---------------+---------+---------+-------+--------+-------------+
  | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows   | Extra       |
  +----+-------------+-------+------+---------------+---------+---------+-------+--------+-------------+
  |  1 | SIMPLE      | l     | ref  | Index 5       | Index 5 | 4       | const | 561826 | Using index |
  +----+-------------+-------+------+---------------+---------+---------+-------+--------+-------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SHOW CREATE TABLE bar.location_data;
  ...
    CREATE TABLE `location_data` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `foo_id` int(11) NOT NULL,
    `ts_lastpos` datetime DEFAULT NULL,
    `lat` double NOT NULL,
    `lng` double NOT NULL,
    PRIMARY KEY (`id`),
    KEY `Index 5` (`foo_id`,`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=562767448 DEFAULT CHARSET=latin1 
  ...
  1 row in set (0.00 sec)
  </code></pre>