Optimizing GROUP BY Query I'm running the following query:

SELECT `type`, MAX(`time`) AS `last_updated` FROM `stocks` GROUP BY `type`


On the following table:

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `type` varchar(6) NOT NULL,
);


And I have the following indexes:

PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `time` (`time`)


Yet the query takes about 4 mins to run and there's only about 3.6 million rows.

Here is my explain:

+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+                                                          
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |                                                          
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+                                                          
|  1 | SIMPLE      | stocks | ALL  | NULL          | NULL | NULL    | NULL | 3684211 | Using temporary; Using filesort |                                                          
+----+-------------+--------+------+---------------+------+---------+------+---------+---------------------------------+


Why aren't my indexes getting used? Should i have different indexes?
Your indexes won't be used because they won't narrow down the table scan. 
As a matter of fact, you want the max time for each type, so even if type index were used as a first intention, you'd still have to fully scan the segment at this index to get the max time for a given type. As a result, MySQL would have scanned the whole table, as a sum of type-indexed segment scans.

Using (type, time) index would in theory give a direct access to the last (max) time, for each type sub-tree. But as far as I know, some older MySQL versions have trouble regarding index selection for this kind of queries.