缓慢的Group_concat查询 [英] Slow Group_concat query

问题描述

我试图创建一个规范化的数据库.因此，我正在尝试加入21个表.我正在联接中几乎所有表上使用索引，如以下EXPLAIN语句的屏幕截图所示.

I tried to create a normalized database. Because of this, I am attempting to join 21 tables. I am using an index on almost all of the tables in the join as seen in the below screenshot of the EXPLAIN statement.

http://i.imgur.com/V5hQu.png

所有lookup_xxxxx表在2列上都有一个索引(content_id和xxxxxx_id) 所有xxxxxx表在1列(xxxxx_id)上都有一个索引

All lookup_xxxxx tables have one index on 2 columns (content_id and xxxxxx_id) All xxxxxx tables have one index on 1 column (xxxxx_id)

每个表中只有大约10行左右.我看到的问题是两个goup_concat将查询延长了200毫秒.我允许提交字段peripheral和programming language具有多个值.没有它们，查询时间将少于80ms. 我的问题是我是否应该放弃group_concats并对其进行单独的查询或重建我的数据库.

I only have about 10 or so rows in each table. The problem I am seeing is that the two goup_concat prolong the query an extra 200ms. I allow the submission fields, peripheral and programming language to have multiple values. Without them, the query is less than 80ms. My question is whether or not I should do away with the group_concats and do individual query's for them or reconstruct my database.

lookup_xxxxx表存储每个允许的值，然后其他表(例如peripheral)通过content_id将提交链接到允许的值.一切都引用了提交content_id. content表保存诸如会员ID，姓名等基本信息.

The lookup_xxxxx tables store each allowed value and then the other tables such as peripheral link the submission to the allowed value via the content_id. Everything is referenced to the submission content_id. The content table holds essential info such as member id, name, etc.

如果我的帖子不够清楚，我深表歉意.

I apologize if my post is not clear enough.

mysql> describe peripheral;
+------------------+----------+------+-----+---------+-------+
| Field            | Type     | Null | Key | Default | Extra |
+------------------+----------+------+-----+---------+-------+
| peripheral_id    | int(2)   | NO   | PRI | NULL    |       |
| peripheral       | char(30) | NO   |     | NULL    |       |
| peripheral_total | int(5)   | NO   |     | NULL    |       |
+------------------+----------+------+-----+---------+-------+

mysql> select * from peripheral;
+---------------+-----------------+------------------+
| peripheral_id | peripheral      | peripheral_total |
+---------------+-----------------+------------------+
|             1 | periph 1        |                0 |
|             2 | periph 2        |                1 |
|             3 | periph 3        |                3 |
+---------------+-----------------+------------------+

:

mysql> describe lookup_peripheral;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| content_id    | int(10) | NO   | MUL | NULL    |       |
| peripheral_id | int(2)  | NO   |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+  


mysql> mysql> select * from lookup_peripheral;
+------------+---------------+
| content_id | peripheral_id |
+------------+---------------+
|         74 |             2 |
|         74 |             5 |
|         75 |             2 |
|         75 |             5 |
|         76 |             3 |
|         76 |             4 |
+------------+---------------+

SELECT group_concat(DISTINCT peripheral.peripheral_id) as peripheral_id, group_concat(DISTINCT programming_language.programming_language_id) as programming_language_id, c.member_name, c.member_id, c.added_date_time, c.title, c.raw_summary, c.raw_all_content, c.meta_tags, c.main_pic_thumb, application.application_id, architecture.architecture_id, compiler.compiler_id, device_family.device_family_id, difficulty.difficulty_id, ide.ide_id, programmer.programmer_id, table_name.table_name_id, device_name.device_name
FROM (content as c)
INNER JOIN lookup_peripheral ON 76 = lookup_peripheral.content_id
INNER JOIN peripheral ON peripheral.peripheral_id = lookup_peripheral.peripheral_id
INNER JOIN lookup_programming_language ON 76 = lookup_programming_language.content_id
INNER JOIN programming_language ON programming_language.programming_language_id = lookup_programming_language.programming_language_id
.......
LEFT OUTER JOIN device_name ON device_name.content_id = c.content_id
INNER JOIN table_name ON table_name.table_name_id = lookup_table_name.table_name_id
WHERE `c`.`content_id` = '76'  

推荐答案

我认为您还应该对lookup_peripheral.peripheral_id字段进行索引.索引外键使INNER JOIN更快.另外，由于要串联ID字段，您是否真的需要DISTINCT子句? 再次考虑，也许您可​​以忽略GROUP_CONCAT和INNER JOIN(我相信您的一些JOIN条件重叠，如果我弄错了，请纠正我)

I think you should also INDEX the lookup_peripheral.peripheral_id field . Indexing foreign keys makes INNER JOINs faster. Also, do you really need the DISTINCT clause since you are concatenating ID fields? On second thought, maybe you could omit the GROUP_CONCATs and INNER JOINs (some of your JOIN conditions overlap I believe, correct me if I'm mistaken)

... (SELECT GROUP_CONCAT(l.peripheral_id) from lookup_peripheral lp where lp.content_id = 76) as peripheral_id, ...

希望这会有所帮助.

这篇关于缓慢的Group_concat查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！