Confused about mysql looking at half a million rows when using an index

Question

I have a table (location_data) with 300M rows (and growing).

I want to find the id of the latest entry (MAX(id)) for a particular value of foo_id.

Is the query below optimal? It often takes a few seconds.
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?
Why is "keylen" 4, when it is made of two 32 bit ints?

Most importantly, how can I speed up this query?

mysql> explain
    -> SELECT foo_id, MAX(id) id 
    -> FROM location_data l   
    -> WHERE l.foo_id = 253   
    -> 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> 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)

Thanks, user4035 - that was the answer - the "group by" was causing the 0.5M records to be read. If you format it as an answer, I'll accept it. — fadedbee, May 16 '13 at 11:21

score 1 · Accepted Answer · edited May 23 '17 at 12:11

I tried this:

SELECT MAX(id) id 
FROM location_data l   
WHERE l.foo_id = 2;

The result is still MAX(id) for appropriate l.foo_id. There is no need to retrieve foo_id as you already know it before the query.

As soon as I removed GROUP BY, EXPLAIN started giving this:

mysql> EXPLAIN SELECT MAX(id) id
    -> FROM location_data l
    -> WHERE l.foo_id = 2\G
*************************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

It means you have done a query that does nothing more than count the number of rows in a table, and that table is a MyISAM table. MyISAM tables are stored with a separate row count, so to do this query MySQL doesn't need to look at any of the table row data at all. Instead it immediately returns the pre-calculated row count. Hence the table access is ‘optimized away’ and the query is lightning-fast.

Meaning of "Select tables optimized away" in MySQL Explain plan

So, I think, getting rid of GROUP BY will speed up your query.

Confused about mysql looking at half a million rows when using an index

1 Answers1