MySQL Forums
Forum List  »  Optimizer & Parser

Index optimized left join with group by
Posted by: Michal Kurgan
Date: March 11, 2013 05:41PM

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 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Index optimized left join with group by
2596
March 11, 2013 05:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.