Making a GROUP_CONCAT query more efficient

Question

I have the following query. The idea is that it allows me to know what groups, and subsequently users, have access to each component_instance. I'm wondering if there is a better way to do this as the query is quite slow, but it's really handy to have these extra columns every time I deal with this table:

SELECT component_instances.*, 
GROUP_CONCAT(DISTINCT IF(permissions.view, groups.id, NULL)) AS view_group_ids,
GROUP_CONCAT(DISTINCT IF(permissions.edit, groups.id, NULL)) AS edit_group_ids,
GROUP_CONCAT(DISTINCT IF(permissions.view, users.id, NULL)) AS view_user_ids,
GROUP_CONCAT(DISTINCT IF(permissions.edit, users.id, NULL)) AS edit_user_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups ON groups.id = permissions.group_id
LEFT OUTER JOIN groups_users ON groups_users.group_id = groups.id
LEFT OUTER JOIN users ON users.id = groups_users.user_id
GROUP BY component_instances.id
ORDER BY (case when component_instances.ancestry is null then 0 else 1 end), component_instances.ancestry, position

The permissions table is like so (excuse the Rails!):

create_table "permissions", :force => true do |t|
  t.integer "component_instance_id"
  t.integer "group_id"
  t.boolean "view",                  :default => false
  t.boolean "edit",                  :default => false
end

The types of permissions are edit, and view. A group can be assigned either or both. Permissions are also recursive in that if there are no group permissions on a component_instance, we'd have to check its ancestors to find the first where permissions are set (if any). This makes having the one query quite important because I can then combine this query with the selection logic that the ancestry gem provides (materialised path tree).

Update

I've since found this query benchmarks faster:

SELECT component_instances.*,
GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
GROUP_CONCAT(DISTINCT view_users.id) AS view_user_ids,
GROUP_CONCAT(DISTINCT edit_users.id) AS edit_user_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ON permissions.component_instance_id = component_instances.id
LEFT OUTER JOIN groups view_groups ON view_groups.id = permissions.group_id AND permissions.view = 1
LEFT OUTER JOIN groups edit_groups ON edit_groups.id = permissions.group_id AND permissions.edit = 1
LEFT OUTER JOIN groups_users view_groups_users ON view_groups_users.group_id = view_groups.id
LEFT OUTER JOIN groups_users edit_groups_users ON edit_groups_users.group_id = edit_groups.id
LEFT OUTER JOIN users view_users ON view_users.id = view_groups_users.user_id
LEFT OUTER JOIN users edit_users ON edit_users.id = edit_groups_users.user_id
GROUP BY component_instances.id
ORDER BY (case when component_instances.ancestry is null then 0 else 1 end), component_instances.ancestry, position

Here is an EXPLAIN for the query above and the table CREATE statements:

+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
| id | select_type | table               | type   | possible_keys                                 | key                                        | key_len | ref                                        | rows | Extra                                                |
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
| 1  | SIMPLE      | component_instances | ALL    | PRIMARY,index_component_instances_on_ancestry | NULL                                       | NULL    | NULL                                       | 119  | "Using temporary; Using filesort"                    |
| 1  | SIMPLE      | permissions         | ALL    | NULL                                          | NULL                                       | NULL    | NULL                                       | 6    | "Using where; Using join buffer (Block Nested Loop)" |
| 1  | SIMPLE      | view_groups         | eq_ref | PRIMARY                                       | PRIMARY                                    | 4       | 05707d890df9347c.permissions.group_id      | 1    | "Using where; Using index"                           |
| 1  | SIMPLE      | edit_groups         | eq_ref | PRIMARY                                       | PRIMARY                                    | 4       | 05707d890df9347c.permissions.group_id      | 1    | "Using where; Using index"                           |
| 1  | SIMPLE      | view_groups_users   | ref    | index_groups_users_on_group_id_and_user_id    | index_groups_users_on_group_id_and_user_id | 5       | 05707d890df9347c.view_groups.id            | 1    | "Using index"                                        |
| 1  | SIMPLE      | edit_groups_users   | ref    | index_groups_users_on_group_id_and_user_id    | index_groups_users_on_group_id_and_user_id | 5       | 05707d890df9347c.edit_groups.id            | 1    | "Using index"                                        |
| 1  | SIMPLE      | view_users          | eq_ref | PRIMARY                                       | PRIMARY                                    | 4       | 05707d890df9347c.view_groups_users.user_id | 1    | "Using index"                                        |
| 1  | SIMPLE      | edit_users          | eq_ref | PRIMARY                                       | PRIMARY                                    | 4       | 05707d890df9347c.edit_groups_users.user_id | 1    | "Using index"                                        |
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+

CREATE TABLE `component_instances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visible` int(11) DEFAULT '1',
  `instance_id` int(11) DEFAULT NULL,
  `deleted_on` date DEFAULT NULL,
  `instance_type` varchar(255) DEFAULT NULL,
  `component_id` int(11) DEFAULT NULL,
  `deleted_root_item` int(11) DEFAULT NULL,
  `locked_until` datetime DEFAULT NULL,
  `theme_id` int(11) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  `ancestry` varchar(255) DEFAULT NULL,
  `ancestry_depth` int(11) DEFAULT '0',
  `cached_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_component_instances_on_ancestry` (`ancestry`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

CREATE TABLE `groups_users` (
  `group_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  KEY `index_groups_users_on_group_id_and_user_id` (`group_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `component_instance_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `view` tinyint(1) DEFAULT '0',
  `edit` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `edit_permissions_index` (`edit`,`group_id`,`component_instance_id`),
  KEY `view_permissions_index` (`view`,`group_id`,`component_instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `real_name` varchar(255) DEFAULT NULL,
  `username` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `crypted_password` varchar(255) DEFAULT NULL,
  `administrator` int(11) NOT NULL DEFAULT '0',
  `password_salt` varchar(255) DEFAULT NULL,
  `remember_token_expires` datetime DEFAULT NULL,
  `persistence_token` varchar(255) DEFAULT NULL,
  `disabled` tinyint(1) DEFAULT NULL,
  `time_zone` varchar(255) DEFAULT NULL,
  `login_count` int(11) DEFAULT NULL,
  `failed_login_count` int(11) DEFAULT NULL,
  `last_request_at` datetime DEFAULT NULL,
  `current_login_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `current_login_ip` varchar(255) DEFAULT NULL,
  `last_login_ip` varchar(255) DEFAULT NULL,
  `perishable_token` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_username` (`username`),
  KEY `index_users_on_perishable_token` (`perishable_token`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

The ORDER BY comes from the ancestry gem but if there's a better way to do this I'd be happy to submit that as a pull request to them.

Answer 1

NULL is placed first (could use COALESCE to replace NULL with something else too instead of using an additional sort column). The second thing is reducing the joins, because the last two were on the id on which we concat.

SELECT
   component_instances.*,
   GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
   GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
   GROUP_CONCAT(DISTINCT view_groups_users.user_id) AS view_user_ids,
   GROUP_CONCAT(DISTINCT edit_groups_users.user_id) AS edit_user_ids
FROM
   `component_instances`
   LEFT OUTER JOIN permissions
      ON permissions.component_instance_id = component_instances.id
   LEFT OUTER JOIN groups view_groups
      ON view_groups.id = permissions.group_id AND permissions.view = 1
   LEFT OUTER JOIN groups edit_groups
      ON edit_groups.id = permissions.group_id AND permissions.edit = 1
   LEFT OUTER JOIN groups_users view_groups_users
      ON view_groups_users.group_id = view_groups.id
   LEFT OUTER JOIN groups_users edit_groups_users
      ON edit_groups_users.group_id = edit_groups.id
GROUP BY
   component_instances.id
ORDER BY
   component_instances.ancestry, -- MySQL was sorting the NULL values already correctly
   position
;