Index optimized left join with group by

[Michal Kurgan]

mysql> explain select app_id from app_devs group by app_id;
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE | app_devs | range | NULL | PRIMARY | 2 | NULL | 32135 | Using index for group-by |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+

mysql> explain select app_id, name from app_devs left join apps on apps.id = app_id group by app_id;
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+
| 1 | SIMPLE | app_devs | index | NULL | PRIMARY | 6 | NULL | 32905483 | Using index |
| 1 | SIMPLE | apps | eq_ref | PRIMARY | PRIMARY | 2 | fluik_services.app_devs.app_id | 1 | |
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+

Is there any way to perform left join to retrieve group by with left join that will use 'Using index for group-by'?
Personally i found that using subquery can many times give what you want from query optimizer, but maybe there is some cleaner/better way to prevent full table scan?

Possible subquery solution below:

mysql> explain select id, name from (select app_id as id, dev_id from app_devs group by app_id) as iq left join apps using (id);
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 125 | |
| 1 | PRIMARY | apps | eq_ref | PRIMARY | PRIMARY | 2 | iq.id | 1 | |
| 2 | DERIVED | app_devs | range | NULL | PRIMARY | 2 | NULL | 32135 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+

Simplified table structure below:

mysql> show create table app_devs;
`app_id` smallint(5) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`app_id`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

mysql> show create table apps;
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(63) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |