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

It's almost impossible to optimize your query if we don't have your table structure and indices. Using a EXPLAIN statement is the necessary part of query optimizations.

Without the mentioned info, all I can comment on your question is that your ORDER BY part can benefit from some optimization for sure. Using any functions or statements in a condition will always result in a disaster. Also using a nullable field in an ORDER BY will also lead to problems. Perhaps the easiest way would be adding a new field to your table holding 0s and 1s instead of the current CASE statement.

Don't forget that having index on any field within a condition / order by / group by is always necessary if the number of records is considerable.

[UPDATE]

Your query is rather simple. The EXPLAIN's result shows that the only parts suitable as a candidate to be indexed are:

CREATE INDEX inx4 ON permissions (`component_instance_id`, `group_id`, `edit`, `view`);

The EXPLAIN's second line shows that there's no index of table permissions used in your query. That's because MySQL has a couple of rules when it will use indices:

• Only one index of each table can be used in each one (sub-)query.
• Any index could be used only if all its fields are mentioned in the query (as in conditions / order by / group by).

Considering your query, and the fact that all four fields of table permissions are mentioned, you'll need an index on all four of them or it's useless.

Yet the ORDER BY can benefit from the amendment I mentioned before.

Answer 2

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
;