唐赛儿起义
concat效率mysql_mysql–使GROUP_CONCAT查询更有效率我有以下查询.这个想法是它允许我知道哪些组以及随后的⽤户可以访问每个component_instance.我想知道是否有更好的⽅法来做这个查询是相当缓慢的，但每次处理这个表时，这些额外的列是⾮常有⽤的：
SELECT component_instances.*,
GROUP_CONCAT( 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, urs.id, NULL)) AS view_ur_ids,
GROUP_CONCAT(DISTINCT IF(permissions.edit, urs.id, NULL)) AS edit_ur_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ponent_instance_id = component_instances.id
LEFT OUTER JOIN groups ON groups.id = up_id
LEFT OUTER JOIN groups_urs ON up_id = groups.id
LEFT OUTER JOIN urs ON urs.id = groups_urs.ur_id
GROUP BY component_instances.id
ORDER BY (ca when component_instances.ancestry is null then 0 el 1 end), component_instances.ancestry, position
权限表是这样的(请问Rails！)：
create_table "permissions", :force => true do |t|
t.integer "component_instance_id"
t.integer "group_id"
t.boolean "view", :default => fal
t.boolean "edit", :default => fal
end
权限的类型是编辑和查看.可以分配⼀个组或两个组.权限也是递归的，因为如果⼀个component_instance没有组权限，我们必须检查它的祖先来找到第⼀个设置权限(如果有的话).这使得⼀个查询⾮常重要，因为我可以将此查询与祖先宝⽯提供的选择逻辑(物化路径树)相结合.
更新
我发现这个查询基准更快：
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_urs.id) AS view_ur_ids,
GROUP_CONCAT(DISTINCT edit_urs.id) AS edit_ur_ids
FROM `component_instances`
LEFT OUTER JOIN permissions ponent_instance_id = component_instances.id
LEFT OUTER JOIN groups view_groups ON view_groups.id = up_id AND permissions.view = 1
LEFT OUTER JOIN groups edit_groups ON edit_groups.id = up_id AND permissions.edit = 1
LEFT OUTER JOIN groups_urs view_groups_urs ON view_up_id = view_groups.id
LEFT OUTER JOIN groups_urs edit_groups_urs ON edit_up_id = edit_groups.id
LEFT OUTER JOIN urs view_urs ON view_urs.id = view_groups_urs.ur_id
LEFT OUTER JOIN urs edit_urs ON edit_urs.id = edit_groups_urs.ur_id
GROUP BY component_instances.id
ORDER BY (ca when component_instances.ancestry is null then 0 el 1 end), component_instances.ancestry, position
以下是上述查询和表CREATE语句的EXPLAIN：
+----+-------------+---------------------+--------+-----------------------------------------------+--------------------------------------------+---------+--------------------------------------------+------+------------------------------------------------------+
| id | lect_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 | up_id | 1 | "Using where; Using index" |
| 1 | SIMPLE | edit_groups | eq_ref | PRIMARY | PRIMARY | 4 | up_id | 1 | "Using where; Using index" |
| 1 | SIMPLE | view_groups_urs | ref | index_groups_urs_on_group_id_and_ur_id |
index_groups_urs_on_group_id_and_ur_id | 5 | 05707d890df9347c.view_groups.id | 1 | "Using index" |
| 1 | SIMPLE | edit_groups_urs | ref | index_groups_urs_on_group_id_and_ur_id |
index_groups_urs_on_group_id_and_ur_id | 5 | 05707d890df9347c.edit_groups.id | 1 | "Using index" |
| 1 | SIMPLE | view_urs | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.view_groups_urs.ur_id | 1 | "Using index" |
残保金如何计算| 1 | SIMPLE | edit_urs | eq_ref | PRIMARY | PRIMARY | 4 | 05707d890df9347c.edit_groups_urs.ur_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_urs` (
`group_id` int(11) DEFAULT NULL,
`ur_id` int(11) DEFAULT NULL,
KEY `index_groups_urs_on_group_id_and_ur_id` (`group_id`,`ur_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 `urs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`real_name` varchar(255) DEFAULT NULL,
`urname` 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_urs_on_urname` (`urname`),
KEY `index_urs_on_perishable_token` (`perishable_token`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 ORDER BY来⾃ancestry gem，但如果有更好的⽅法，我很乐意提交给他们的拉动请求.
