mySQL>>使用CONCAT，GROUP_CONCAT和多个JOINS进行非常慢的查询（63条记录为100秒） [英] mySQL >> Very Slow Query with CONCAT, GROUP_CONCAT and multiple JOINS (100 sec for 63 records)
本文介绍了mySQL>>使用CONCAT，GROUP_CONCAT和多个JOINS进行非常慢的查询（63条记录为100秒）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用以下查询：
SELECT *，
GROUP_CONCAT（不同的t_name按t_id分隔符排序' ， '） '主题'，
GROUP_CONCAT（由ch_id分离器不同ch_title顺序'， '） '章节'，
GROUP_CONCAT由cs_id分离器（不同cs_code顺序'，） 'ContentStandards'，
GROUP_CONCAT（不同的c_name顺序由c_id分隔符'，'）'类别'，
GROUP_CONCAT（不同的s_name顺序由s_id分隔符'，'）'SchoolSubjects'
FROM（
SELECT r.res_id ，r.res_status，r.res_type，r.res_category，r.res_title，r.res_filename，r.res_source，r.res_gradelevel_from，r.res_gradelevel_to，r.res_tags，r.res_description，r.res_onserver，r.res_favoriteaccts，r .res_CreatedOn，c_id，s_id，t_id，ch_id，cs_id，
CONCAT（< a class =ReportLinkhref =page.asp？category =，c_name，title = ，c_name，>，c_name，< / a>）为c_name，
CONCAT（< a class =ReportLinkhref =page.asp？主题= ，s_name，title =，s_name，>，s_name，< / a>）为s_name，
CONCAT（< a class =ReportLink href =page.asp？topic =，t_name，title =，t_name，>，t_name，< / a>）为t_name，
CONCAT（< a class =ReportLinkhref =page.asp？cs =，cs_code，title =，cs_code，>，cs_code，< ; / a>）作为cs_code，
CONCAT（< a class =ReportLinkhref =page.asp？chapter =，ch_title，title =，ch_title > 中，ch_code， ：，ch_title， < / A> 中），为ch_title
FROM hre_resources - [R
LEFT JOIN topic_to_resource TTR ON ttr.rt_resourceid = r.res_id
LEFT JOIN主题吨上t.t_id = ttr.rt_topicid
LEFT JOIN上tcs.cst_topicid = t.t_id
LEFT topic_to_cs TCS JOIN类别C ON C_ID = r.res_category
LEFT JOT topic_to_framework ttc on ttc.ft_topicid = t.t_id
LEFT JOIN ca_hss_frameworkchapters ch ON ch.ch_id = ttc.ft_frameworkid
LEFT JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid
LEFT JOIN schoolsubjects_to_resource STR可str.sr_resid = r.res_id
LEFT JOIN schoolsubjects S于s.s_id = str.sr_sid）线路，
，其中RES_Status = '主动'
GROUP BY RES_ID
ORDER BY RES_Title ASC，RES_Source DESC
此查询从mySQL数据库返回64个格式正确的记录，但需要超过100秒这样做。
查询分析器将关键违规者显示为：
+ ------------------------------- + ----------- +
|发送数据| 34.530026 |
+ ------------------------------- + ----------- +
|创建排序索引| 24.205878 |
|将结果存储在查询缓存中56.739204 |
+ ------------------------------- + ----------- +
EXPLAIN结果如下：
+ ---- + ------------- + ------------ + -------- + --------------- + --------------- + --------- + -------- ---------------------------- + ------ + -------------- -------------------------------------------------- ----- +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------------ + -------- + ---- ----------- + --------------- + --------- + ------------ ------------------------ + ------ + ------------------ -------------------------------------------------- - +
| 1 |主要| < Derived2的> | ref | < auto_key0> | < auto_key0> | 77 | const | 10 |使用索引条件;用在哪里;使用临时;使用filesort |
| 2 | DERIVED | r |所有| NULL | NULL | NULL | NULL | 64 | NULL |
| 2 | DERIVED | ttr | ref | RT_ResourceID | RT_ResourceID | 4 | uhri_hre.r.RES_ID | 3 |使用何处|
| 2 | DERIVED | t | eq_ref |主要|主要| 4 | uhri_hre.ttr.RT_TopicID | 1 |使用何处|
| 2 | DERIVED | tcs | ref | CST_TopicID | CST_TopicID | 4 | uhri_hre.t.T_ID | 9 |使用何处|
| 2 | DERIVED | c | eq_ref |主要|主要| 4 | uhri_hre.r.RES_Category | 1 |使用何处|
| 2 | DERIVED | ttc | ref | FT_TopicID | FT_TopicID | 4 | uhri_hre.t.T_ID | 21 |使用何处|
| 2 | DERIVED | ch | eq_ref |主要|主要| 4 | uhri_hre.ttc.FT_FrameworkID | 1 |使用何处|
| 2 | DERIVED | cs | eq_ref |主要|主要| 4 | uhri_hre.tcs.CST_ContentStandardID | 1 |使用何处|
| 2 | DERIVED | str | ref | SR_RESID | SR_RESID | 4 | uhri_hre.r.RES_ID | 1 |使用何处|
| 2 | DERIVED | s | eq_ref |主要|主要| 4 | uhri_hre.str.SR_SID | 1 |使用何处|
+ ---- + ------------- + ------------ + -------- + ---- ----------- + --------------- + --------- + ------------ ------------------------ + ------ + ------------------ -------------------------------------------------- - +
表格包含以下索引。未列出的表在ID字段（主键）上有索引：
HRE_Resources：
+ ----- --------- ------- + -------- + -------- + + -------------- + ------------- + ----------- + ------ + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ -------------- + ------- + -------- + -------- + ---- ---------- + ------------- ----------- + + ------ + ------ --- +
|主要| BTREE |是的|没有| RES_ID | 64 | A | | |
| RES_Type | BTREE |没有|没有| RES_Type | 4 | A | | |
| RES_Category | BTREE |没有|没有| RES_Category | 10 | A | | |
| RES_Status | BTREE |没有|没有| RES_Status | 4 | A | | |
| RES_Language | BTREE |没有|没有| RES_Language | 8 | A | | |
| RES_Source | BTREE |没有|没有| RES_Source | 21 | A | | |
+ -------------- + ------- + -------- + -------- + ---- ---------- + ------------- ----------- + + ------ + ------ --- +
主题：
+ --------- + ------- + -------- + ---- ---- + -------- + ------------- ----------- + + ------ + --- ------ +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ --------- + ------- + -------- + -------- + -------- + ------------- + ----------- + ------ + --------- +
|主要| BTREE |是的|没有| T_ID | 39 | A | | |
| T_Name | BTREE |没有|没有| T_Name | 39 | A | | |
+ --------- + ------- + -------- + -------- + -------- + ------------- + ----------- + ------ + --------- +
Topic_to_CS：
+ ----------------------- + ------- + -------- + - ------- + ----------------------- + ------------- + ---- ------- + ------ + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ ----------------------- + ------- + -------- + ---- ---- + ----------------------- + ------------- + ------- ---- + ------ + --------- +
|主要| BTREE |是的|没有| CST_ID | 685 | A | | |
| CST_ContentStandardID | BTREE |没有|没有| CST_ContentStandardID | 228 | A | | |
| CST_TopicID | BTREE |没有|没有| CST_TopicID | 76 | A | | |
+ ----------------------- + ------- + -------- + ---- ---- + ----------------------- + ------------- + ------- ---- + ------ + --------- +
Topic_to_Framework：
+ ------------- --- + ------- + -------- + -------- + ---------------- + --- ---------- + ----------- + ------ + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ ---------------- + ------- + -------- + -------- + - -------------- + ------------- ----------- + + ------ + - ------- +
|主要| BTREE |是的|没有| FT_ID | 471 | A | | |
| FT_TopicID | BTREE |没有|没有| FT_TopicID | 22 | A | | |
| FT_FrameworkID | BTREE |没有|没有| FT_FrameworkID | 235 | A | | |
+ ---------------- + ------- + -------- + -------- + - -------------- + ------------- ----------- + + ------ + - ------- +
Topic_to_Resource：
+ --------------- + ------- + --- ----- + -------- + -------- + ------------- + ----- ------ + ------ + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ --------------- + ------- + -------- + -------- + --- ------------ + ------------- ----------- + + ------ + ---- ----- +
|主要| BTREE |是的|没有| RT_ID | 387 | A | | |
| RT_ResourceID | BTREE |没有|没有| RT_ResourceID | 129 | A | | |
| RT_TopicID | BTREE |没有|没有| RT_TopicID | 77 | A | | |
+ --------------- + ------- + -------- + -------- + --- ------------ + ------------- ----------- + + ------ + ---- ----- +
Schoolsubject_to_Resource：
+ ---------- + ------- + -------- + - ------- + ---------- + ------------- ----------- + ----- + - + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ ---------- + ------- + -------- + -------- + -------- - + ------------- + ----------- + ------ + --------- +
|主要| BTREE |是的|没有| SR_ID | 228 | A | | |
| SR_SID | BTREE |没有|没有| SR_SID | 25 | A | | |
| SR_RESID | BTREE |没有|没有| SR_RESID | 228 | A | | |
+ ---------- + ------- + -------- + -------- + -------- - + ------------- + ----------- + ------ + --------- +
类别：
+ --------- + ------- + -------- + -------- + --- ----- + ------------- + ----------- + ------ + --------- +
| Keyname |输入|独特|打包|专栏|基数|整理|空|评论|
+ --------- + ------- + -------- + -------- + -------- + ------------- + ----------- + ------ + --------- +
|主要| BTREE |是的|没有| C_ID | 9 | A | | |
| C_Name | BTREE |没有|没有| C_Name | 9 | A | | |
+ --------- + ------- + -------- + -------- + -------- + ------------- + ----------- + ------ + --------- +
其中一些索引是使用在其他查询中使用的更复杂的WHERE创建的。
我在数据库上运行了复杂的查询，用于与上述查询无关的其他目的，并且它在名义上执行。因此，我认为这不是服务器资源问题。
任何人都可以在上面的查询中看到可能导致如此缓慢响应的任何内容吗？
非常感谢提前！
PS。根据以下请求从服务器添加一些信息...
+ ------------ ------------------ + --------- +
| Variable_name |价值|
+ ------------------------------ + --------- +
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | ON |
| query_cache_wlock_invalidate |关闭|
| query_prealloc_size | 8192 |
+ ------------------------------ + --------- +
+ ------------------------- + -------- +
| Variable_name |价值|
+ ------------------------- + -------- +
| Qcache_free_blocks | 9 |
| Qcache_free_memory | 22736 |
| Qcache_hits | 121910 |
| Qcache_inserts | 9296 |
| Qcache_lowmem_prunes | 8213 |
| Qcache_not_cached | 29475 |
| Qcache_queries_in_cache | 103 |
| Qcache_total_blocks | 295 |
+ ------------------------- + -------- +
解决方案
正如我在上面的评论中所提到的，您似乎从多个表关系中获取了大量数据。这导致了几个
以下是各个查询的内容，以及各自的EXPLAIN计划。
您可以看到这消除了所有子查询和临时表，它消除了笛卡尔积，因此您无需在任何地方使用DISTINCT，并且所有连接都可以很好地对抗主要键。它可能看似重复，但查询更好地优化的事实应该使它运行得更快。
EXPLAIN
SELECT r.res_id，r.res_status，r.res_type，r.res_category，r.res_title，r.res_filename，
r.res_source，r.res_gradelevel_from，r.res_gradelevel_to，r.res_tags，r.res_description，
r.res_onserver，r.res_favoriteaccts，r.res_CreatedOn，c.c_name AS'Categories'
FROM hre_resources AS r
INNER JOIN category c ON c_id = r.res_category
WHERE r。 res_status ='有效'
--------------
+ ---- + ------------- + ------- + -------- + ------------------------- + ------ ------ + --------- + --------------------- + ------ + ---- ------------------- +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------- + -------- + --------- ---------------- + ------------ + --------- + ---------- ----------- + ------ + ----------------------- +
| 1 |简单| r | ref | res_status，res_category | res_status | 1 | const | 1 |使用索引条件|
| 1 |简单| c | eq_ref |主要|主要| 4 | test.r.res_category | 1 | NULL |
+ ---- + ------------- + ------- + -------- + --------- ---------------- + ------------ + --------- + ---------- ----------- + ------ + ----------------------- +
EXPLAIN
SELECT r.res_id，GROUP_CONCAT（s.s_name ORDER BY s.s_id SEPARATOR'，'）AS'SchoolSubjects'
FROM hre_resources AS r
INNER JOIN schoolsubjects_to_resource str ON str.sr_resid = r.res_id
INNER JOIN schoolsubjects s ON s.s_id = str.sr_sid
WHERE r.res_status ='Active'
GROUP BY r.res_id
----- ---------
+ ---- + ------------- + ------- + ----- --- + --------------------------------- + ------------ + --------- + ----------------- + ------ + -------------- ------------ +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - --------------- + ------ + -------------------------- +
| 1 |简单| r | ref | PRIMARY，res_status，res_category | res_status | 1 | const | 1 |用在哪里;使用索引|
| 1 |简单| str | ref | PRIMARY，sr_sid |主要| 4 | test.r.res_id | 1 |使用索引|
| 1 |简单| s | eq_ref |主要|主要| 4 | test.str.sr_sid | 1 | NULL |
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - --------------- + ------ + -------------------------- +
3行（0.00秒）
--------------
EXPLAIN
SELECT r.res_id，GROUP_CONCAT（ t.t_name ORDER BY t_id SEPARATOR'，'）AS'主题'
来自hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN主题t。 t_id = ttr.rt_topicid
WHERE r.res_status ='有效'
GROUP BY r.res_id
--------------
+ ---- + ------------- + ------- + -------- + ----------- ---------------------- + ------------ + --------- + ---- ----------------- + ------ + ------------------------- - +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ------------------- + ------ + ------------ --- +
| 1 |简单| r | ref | PRIMARY，res_status，res_category | res_status | 1 | const | 1 |用在哪里;使用索引|
| 1 |简单| ttr | ref | PRIMARY，rt_topicid |主要| 4 | test.r.res_id | 1 |使用索引|
| 1 |简单| t | eq_ref |主要|主要| 4 | test.ttr.rt_topicid | 1 | NULL |
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ------------------- + ------ + ------------ --- +
3行（0.00秒）
--------------
EXPLAIN
SELECT r。 res_id，GROUP_CONCAT（DISTINCT CONCAT（ch.ch_code，'：'，ch.ch_title）ORDER BY ch.ch_id SEPARATOR'，'）AS'Chapters'
FROM hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN主题t on t.t_id = ttr.rt_topicid
INNER JOIN topic_to_framework ttc on ttc.ft_topicid = t.t_id
INNER JOIN ca_hss_frameworkchapters ch ON ch。 ch_id = ttc.ft_frameworkid
WHERE r.res_status ='有效'
GROUP BY r.res_id
--------------
+ ---- + ------------- + ------- + -------- + ----------- ---------------------- + ------------ + --------- + ---- --------------------- + ------ + --------------------- ----- +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ----------------------- + ------ + ------------------- ------- +
| 1 |简单| r | ref | PRIMARY，res_status，res_category | res_status | 1 | const | 1 |用在哪里;使用索引|
| 1 |简单| ttr | ref | PRIMARY，rt_topicid |主要| 4 | test.r.res_id | 1 |使用索引|
| 1 |简单| t | eq_ref |主要|主要| 4 | test.ttr.rt_topicid | 1 |使用索引|
| 1 |简单| ttc | ref |主要的，ft_frameworkid |主要| 4 | test.ttr.rt_topicid | 1 |使用索引|
| 1 |简单| ch | eq_ref |主要|主要| 4 | test.ttc.ft_frameworkid | 1 | NULL |
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ----------------------- + ------ + ------------------- ------- +
5行（0.00秒）
--------------
EXPLAIN
SELECT r.res_id，GROUP_CONCAT（DISTINCT cs.cs_code ORDER BY cs.cs_id SEPARATOR'，'）AS'ContentStandards'
FROM hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN主题t on t.t_id = ttr.rt_topicid
INNER JOIN topic_to_cs tcs on tcs.cst_topicid = t.t_id
INNER JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid
WHERE r.res_status ='有效'
GROUP BY r.res_id
--------------
+ ---- + ------------- + ------- + -------- + ------------------ --------------- + ------------ + --------- + ----------- --------------------- + ------ + --------------------- ----- +
| id | select_type |表|类型| possible_keys |关键| key_len | ref |行|额外的|
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ------------------------------ + ------ + ------------ -------------- +
| 1 |简单| r | ref | PRIMARY，res_status，res_category | res_status | 1 | const | 1 |用在哪里;使用索引|
| 1 |简单| ttr | ref | PRIMARY，rt_topicid |主要| 4 | test.r.res_id | 1 |使用索引|
| 1 |简单| t | eq_ref |主要|主要| 4 | test.ttr.rt_topicid | 1 |使用索引|
| 1 |简单| tcs | ref | PRIMARY，cst_contentstandardid |主要| 4 | test.ttr.rt_topicid | 1 |使用索引|
| 1 |简单| cs | eq_ref |主要|主要| 4 | test.tcs.cst_contentstandardid | 1 | NULL |
+ ---- + ------------- + ------- + -------- + --------- ------------------------ + ------------ + --------- + - ------------------------------ + ------ + ------------ -------------- +
以下是我假设的DDL对于表格。如果您在原始问题中包含此内容，那将会很有帮助。
CREATE TABLE类别（
c_id INT PRIMARY KEY，
c_name VARCHAR（100）NOT NULL DEFAULT' c_name'
）;
CREATE TABLE hre_resources（
res_id INT PRIMARY KEY，
res_category INT NOT NULL，
res_status ENUM（'Active'，'Inactive'）NOT NULL DEFAULT'Active '，
res_type VARCHAR（100）NOT NULL DEFAULT'type'，
res_title VARCHAR（100）NOT NULL DEFAULT'title'，
res_filename VARCHAR（100）NOT NULL DEFAULT''，
res_source VARCHAR（100）NOT NULL DEFAULT''，
res_gradelevel_from VARCHAR（100）NOT NULL DEFAULT''，
res_gradelevel_to VARCHAR（100）NOT NULL DEFAULT''，
res_tags VARCHAR （100）NOT NULL DEFAULT''，
res_description VARCHAR（100）NOT NULL DEFAULT''，
res_onserver VARCHAR（100）NOT NULL DEFAULT''，
res_favoriteaccts VARCHAR（100）NOT NULL DEFAULT''，
res_CreatedOn DATE NOT NULL DEFAULT'2017-01-01'，
KEY（res_status），
FOREIGN KEY（res_category）REFERENCES categories（c_id）
）;
CREATE TABLE主题（
t_id INT PRIMARY KEY，
t_name VARCHAR（100）NOT NULL DEFAULT't_name'
）;
CREATE TABLE ca_hss_frameworkchapters（
ch_id INT PRIMARY KEY，
ch_title VARCHAR（100）NOT NULL DEFAULT'ch_title'，
ch_code VARCHAR（100）NOT NULL DEFAULT' ch_code'
）;
CREATE TABLE ca_hss_contentstandards（
cs_id INT PRIMARY KEY，
cs_code VARCHAR（100）NOT NULL DEFAULT'cs_code'
）;
CREATE TABLE schoolsubjects（
s_id INT PRIMARY KEY，
s_name VARCHAR（100）NOT NULL DEFAULT's_name'
）;
CREATE TABLE topic_to_framework（
ft_topicid INT NOT NULL，
ft_frameworkid INT NOT NULL，
PRIMARY KEY（ft_topicid，ft_frameworkid），
FOREIGN KEY（ft_topicid） ）REFERENCES主题（t_id），
FOREIGN KEY（ft_frameworkid）参考ca_hss_frameworkchapters（ch_id）
）;
CREATE TABLE topic_to_resource（
rt_resourceid INT NOT NULL，
rt_topicid INT NOT NULL，
PRIMARY KEY（rt_resourceid，rt_topicid），
FOREIGN KEY（rt_resourceid） ）REFERENCES hre_resources（res_id），
FOREIGN KEY（rt_topicid）REFERENCES subject（t_id）
）;
CREATE TABLE topic_to_cs（
cst_topicid INT NOT NULL，
cst_contentstandardid INT NOT NULL，
PRIMARY KEY（cst_topicid，cst_contentstandardid），
FOREIGN KEY（cst_topicid） ）REFERENCES主题（t_id），
FOREIGN KEY（cst_contentstandardid）参考ca_hss_contentstandards（cs_id）
）;
CREATE TABLE schoolsubjects_to_resource（
sr_resid INT NOT NULL，
sr_sid INT NOT NULL，
PRIMARY KEY（sr_resid，sr_sid），
FOREIGN KEY（sr_resid） ）REFERENCES hre_resources（res_id），
FOREIGN KEY（sr_sid）REFERENCES schoolsubjects（s_id）
）;
以下是Python中我的意思的一个例子将多个查询的结果合并在一起。
import pprint
import MySQLdb
import MySQLdb.cursors
db = MySQLdb.connect（host =localhost，db =test，cursorclass = MySQLdb.cursors.DictCursor）
cur = db.cursor（）
res_data = {}
sql =SELECT r.res_id，r.res_status，r.res_type，r.res_category，r.res_title，r.res_filename，r.res_source，r.res_gradelevel_from ，r.res_gradelevel_to，r.res_tags，r.res_description，r.res_onserver，r.res_favoriteaccts，r.res_CreatedOn，c.c_name AS' Categories'FROM hre_resources AS r INNER JOIN categories c ON c_id = r.res_category WHERE r.res_status ='活动'
cur.execute（sql）
for cur.fetchall（）中的行：
res_data [row ['res_id']] = row
sql =SELECT r.res_id，GROUP_CONCAT（s.s_name ORDER BY s.s_id SEPARATOR'，'）AS'SchoolSubjects'FRN hre_资源AS r INNER JOIN schoolsubjects_to_resource str ON str.sr_resid = r.res_id INNER JOIN schoolsubjects s ON s.s_id = str.sr_sid WHERE r.res_status ='Active'GROUP BY r.res_id
cur.execute （sql）
for cur.fetchall（）中的行：
res_data [row ['res_id']]。update（row）
sql =SELECT r。 res_id，GROUP_CONCAT（t.t_name ORDER BY t_id SEPARATOR'，'）AS'主题'FROM hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN主题t on t.t_id = ttr.rt_topicid WHERE r。 res_status ='Active'GROUP BY r.res_id
cur.execute（sql）
for cur.fetchall（）中的行：
res_data [row ['res_id']]。更新（行）
sql =SELECT r.res_id，GROUP_CONCAT（DISTINCT CONCAT（ch.ch_code，'：'，ch.ch_title）ORDER BY ch.ch_id SEPARATOR'，'）AS '章节'来自hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN主题t on t.t_id = ttr.rt_topicid INNER JOIN topi c_to_framework ttc on ttc.ft_topicid = t.t_id INNER JOIN ca_hss_frameworkchapters ch ON ch.ch_id = ttc.ft_frameworkid WHERE r.res_status ='Active'GROUP BY r.res_id
cur.execute（sql）$ b cur.fetchall（）中行的$ b：
res_data [row ['res_id']]。update（row）
sql =SELECT r.res_id，GROUP_CONCAT（DISTINCT） cs.cs_code ORDER BY cs.cs_id SEPARATOR'，'）AS'ContentStandards'FROM hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN topic t on t.t_id = ttr.rt_topicid INNER JOIN topic_to_cs tcs on tcs.cst_topicid = t.t_id INNER JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid WHERE r.res_status ='Active'GROUP BY r.res_id
cur.execute（sql）
for cur.fetchall（）中的行：
res_data [row ['res_id']]。update（row）
db.close（）
pp = pprint .PrettyPrinter（）
pp.pprint（res_data）
我添加了一行假数据到每个tabl e，我从上面的脚本得到了这个输出：
{2L：{'Categories'：'c_name'，
'章节'：'ch_codech_title'，
'ContentStandards'：'cs_code'，
'SchoolSubjects'：'s_name'，
'Topics'：'t_name'，
' res_CreatedOn'：datetime.date（2017,1,1），
'res_category'：1L，
'res_description'：''，
'res_favoriteaccts'：''，
'res_filename'：''，
'res_gradelevel_from'：''，
'res_gradelevel_to'：''，
'res_id'：2L，
'res_onserver'：''，
'res_source'：''，
'res_status'：'有效'，
'res_tags'：''，
'res_title'：'title'，
'res_type'：'type'}}
I am using the following query:
SELECT *,
GROUP_CONCAT(distinct t_name order by t_id separator ', ') 'Topics',
GROUP_CONCAT(distinct ch_title order by ch_id separator ', ') 'Chapters',
GROUP_CONCAT(distinct cs_code order by cs_id separator ', ') 'ContentStandards',
GROUP_CONCAT(distinct c_name order by c_id separator ', ') 'Categories',
GROUP_CONCAT(distinct s_name order by s_id separator ', ') 'SchoolSubjects'
FROM (
SELECT r.res_id, r.res_status, r.res_type, r.res_category, r.res_title, r.res_filename, r.res_source, r.res_gradelevel_from, r.res_gradelevel_to, r.res_tags, r.res_description, r.res_onserver, r.res_favoriteaccts, r.res_CreatedOn, c_id, s_id, t_id, ch_id, cs_id,
CONCAT("<a class=""ReportLink"" href=""page.asp?category=",c_name,""" title=""",c_name,""">",c_name,"</a>") as c_name,
CONCAT("<a class=""ReportLink"" href=""page.asp?subject=",s_name,""" title=""",s_name,""">",s_name,"</a>") as s_name,
CONCAT("<a class=""ReportLink"" href=""page.asp?topic=",t_name,""" title=""",t_name,""">",t_name,"</a>") as t_name,
CONCAT("<a class=""ReportLink"" href=""page.asp?cs=",cs_code,""" title=""",cs_code,""">",cs_code,"</a>") as cs_code,
CONCAT("<a class=""ReportLink"" href=""page.asp?chapter=",ch_title,""" title=""",ch_title,""">",ch_code,": ",ch_title,"</a>") as ch_title
FROM hre_resources r
LEFT JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
LEFT JOIN topics t on t.t_id = ttr.rt_topicid
LEFT JOIN topic_to_cs tcs on tcs.cst_topicid = t.t_id
LEFT JOIN categories c ON c_id = r.res_category
LEFT JOIN topic_to_framework ttc on ttc.ft_topicid = t.t_id
LEFT JOIN ca_hss_frameworkchapters ch ON ch.ch_id = ttc.ft_frameworkid
LEFT JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid
LEFT JOIN schoolsubjects_to_resource str ON str.sr_resid = r.res_id
LEFT JOIN schoolsubjects s ON s.s_id = str.sr_sid) links
WHERE RES_Status = 'Active'
GROUP BY RES_ID
ORDER BY RES_Title ASC, RES_Source DESC
This query returns 64 perfectly formatted records from our mySQL DB, but it takes over 100 seconds to do this.
The query profiler shows the key offenders as:
+-------------------------------+-----------+
| Sending data | 34.530026 |
+-------------------------------+-----------+
| Creating sort index | 24.205878 |
| Storing result in query cache | 56.739204 |
+-------------------------------+-----------+
The EXPLAIN results are below:
+----+-------------+------------+--------+---------------+---------------+---------+------------------------------------+------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------------+---------+------------------------------------+------+---------------------------------------------------------------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 77 | const | 10 | Using index condition; Using where; Using temporary; Using filesort |
| 2 | DERIVED | r | ALL | NULL | NULL | NULL | NULL | 64 | NULL |
| 2 | DERIVED | ttr | ref | RT_ResourceID | RT_ResourceID | 4 | uhri_hre.r.RES_ID | 3 | Using where |
| 2 | DERIVED | t | eq_ref | PRIMARY | PRIMARY | 4 | uhri_hre.ttr.RT_TopicID | 1 | Using where |
| 2 | DERIVED | tcs | ref | CST_TopicID | CST_TopicID | 4 | uhri_hre.t.T_ID | 9 | Using where |
| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | uhri_hre.r.RES_Category | 1 | Using where |
| 2 | DERIVED | ttc | ref | FT_TopicID | FT_TopicID | 4 | uhri_hre.t.T_ID | 21 | Using where |
| 2 | DERIVED | ch | eq_ref | PRIMARY | PRIMARY | 4 | uhri_hre.ttc.FT_FrameworkID | 1 | Using where |
| 2 | DERIVED | cs | eq_ref | PRIMARY | PRIMARY | 4 | uhri_hre.tcs.CST_ContentStandardID | 1 | Using where |
| 2 | DERIVED | str | ref | SR_RESID | SR_RESID | 4 | uhri_hre.r.RES_ID | 1 | Using where |
| 2 | DERIVED | s | eq_ref | PRIMARY | PRIMARY | 4 | uhri_hre.str.SR_SID | 1 | Using where |
+----+-------------+------------+--------+---------------+---------------+---------+------------------------------------+------+---------------------------------------------------------------------+
The tables have the following indexes. Tables not listed have indexes on the ID field (Primary Key):
HRE_Resources:
+--------------+-------+--------+--------+--------------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+--------------+-------+--------+--------+--------------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | RES_ID | 64 | A | | |
| RES_Type | BTREE | No | No | RES_Type | 4 | A | | |
| RES_Category | BTREE | No | No | RES_Category | 10 | A | | |
| RES_Status | BTREE | No | No | RES_Status | 4 | A | | |
| RES_Language | BTREE | No | No | RES_Language | 8 | A | | |
| RES_Source | BTREE | No | No | RES_Source | 21 | A | | |
+--------------+-------+--------+--------+--------------+-------------+-----------+------+---------+
Topics:
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | T_ID | 39 | A | | |
| T_Name | BTREE | No | No | T_Name | 39 | A | | |
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
Topic_to_CS:
+-----------------------+-------+--------+--------+-----------------------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+-----------------------+-------+--------+--------+-----------------------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | CST_ID | 685 | A | | |
| CST_ContentStandardID | BTREE | No | No | CST_ContentStandardID | 228 | A | | |
| CST_TopicID | BTREE | No | No | CST_TopicID | 76 | A | | |
+-----------------------+-------+--------+--------+-----------------------+-------------+-----------+------+---------+
Topic_to_Framework:
+----------------+-------+--------+--------+----------------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+----------------+-------+--------+--------+----------------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | FT_ID | 471 | A | | |
| FT_TopicID | BTREE | No | No | FT_TopicID | 22 | A | | |
| FT_FrameworkID | BTREE | No | No | FT_FrameworkID | 235 | A | | |
+----------------+-------+--------+--------+----------------+-------------+-----------+------+---------+
Topic_to_Resource:
+---------------+-------+--------+--------+---------------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+---------------+-------+--------+--------+---------------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | RT_ID | 387 | A | | |
| RT_ResourceID | BTREE | No | No | RT_ResourceID | 129 | A | | |
| RT_TopicID | BTREE | No | No | RT_TopicID | 77 | A | | |
+---------------+-------+--------+--------+---------------+-------------+-----------+------+---------+
Schoolsubject_to_Resource:
+----------+-------+--------+--------+----------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+----------+-------+--------+--------+----------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | SR_ID | 228 | A | | |
| SR_SID | BTREE | No | No | SR_SID | 25 | A | | |
| SR_RESID | BTREE | No | No | SR_RESID | 228 | A | | |
+----------+-------+--------+--------+----------+-------------+-----------+------+---------+
Categories:
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
| PRIMARY | BTREE | Yes | No | C_ID | 9 | A | | |
| C_Name | BTREE | No | No | C_Name | 9 | A | | |
+---------+-------+--------+--------+--------+-------------+-----------+------+---------+
Some of these indexes are created with more complicated WHEREs that are used in other queries.
I have run complex queries on the DB for other purposes that have nothing to do with the query above and it's performing nominally. As such, I don't think it's a server resources issue.
Can anyone see anything in the query above that might be causing such a slow response?
Many thanks in advance!
PS. Adding some info from the server per a request below...
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 9 |
| Qcache_free_memory | 22736 |
| Qcache_hits | 121910 |
| Qcache_inserts | 9296 |
| Qcache_lowmem_prunes | 8213 |
| Qcache_not_cached | 29475 |
| Qcache_queries_in_cache | 103 |
| Qcache_total_blocks | 295 |
+-------------------------+--------+
解决方案
As I mentioned in the comments above, you seem to be fetching a lot of data from multiple table relationships. This causes several Cartesian products, which is why you get the 626K rows.
The solution is to break this up into individual queries.
Here's what I understand about your model:
Here's what the individual queries would look like, with their respective EXPLAIN plans.
You can see that this eliminates all subqueries and temp tables, it eliminates the Cartesian products so you don't need to use DISTINCT anywhere, and all joins are nicely against primary keys. It might seem repetitive, but the fact that the queries are better optimized should make it run faster.
EXPLAIN
SELECT r.res_id, r.res_status, r.res_type, r.res_category, r.res_title, r.res_filename,
r.res_source, r.res_gradelevel_from, r.res_gradelevel_to, r.res_tags, r.res_description,
r.res_onserver, r.res_favoriteaccts, r.res_CreatedOn, c.c_name AS 'Categories'
FROM hre_resources AS r
INNER JOIN categories c ON c_id = r.res_category
WHERE r.res_status = 'Active'
--------------
+----+-------------+-------+--------+-------------------------+------------+---------+---------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------+------------+---------+---------------------+------+-----------------------+
| 1 | SIMPLE | r | ref | res_status,res_category | res_status | 1 | const | 1 | Using index condition |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.r.res_category | 1 | NULL |
+----+-------------+-------+--------+-------------------------+------------+---------+---------------------+------+-----------------------+
EXPLAIN
SELECT r.res_id, GROUP_CONCAT(s.s_name ORDER BY s.s_id SEPARATOR ', ') AS 'SchoolSubjects'
FROM hre_resources AS r
INNER JOIN schoolsubjects_to_resource str ON str.sr_resid = r.res_id
INNER JOIN schoolsubjects s ON s.s_id = str.sr_sid
WHERE r.res_status = 'Active'
GROUP BY r.res_id
--------------
+----+-------------+-------+--------+---------------------------------+------------+---------+-----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+------------+---------+-----------------+------+--------------------------+
| 1 | SIMPLE | r | ref | PRIMARY,res_status,res_category | res_status | 1 | const | 1 | Using where; Using index |
| 1 | SIMPLE | str | ref | PRIMARY,sr_sid | PRIMARY | 4 | test.r.res_id | 1 | Using index |
| 1 | SIMPLE | s | eq_ref | PRIMARY | PRIMARY | 4 | test.str.sr_sid | 1 | NULL |
+----+-------------+-------+--------+---------------------------------+------------+---------+-----------------+------+--------------------------+
3 rows in set (0.00 sec)
--------------
EXPLAIN
SELECT r.res_id, GROUP_CONCAT(t.t_name ORDER BY t_id SEPARATOR ', ') AS 'Topics'
FROM hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN topics t on t.t_id = ttr.rt_topicid
WHERE r.res_status = 'Active'
GROUP BY r.res_id
--------------
+----+-------------+-------+--------+---------------------------------+------------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+------------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | r | ref | PRIMARY,res_status,res_category | res_status | 1 | const | 1 | Using where; Using index |
| 1 | SIMPLE | ttr | ref | PRIMARY,rt_topicid | PRIMARY | 4 | test.r.res_id | 1 | Using index |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | test.ttr.rt_topicid | 1 | NULL |
+----+-------------+-------+--------+---------------------------------+------------+---------+---------------------+------+--------------------------+
3 rows in set (0.00 sec)
--------------
EXPLAIN
SELECT r.res_id, GROUP_CONCAT(DISTINCT CONCAT(ch.ch_code, ':', ch.ch_title) ORDER BY ch.ch_id SEPARATOR ', ') AS 'Chapters'
FROM hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN topics t on t.t_id = ttr.rt_topicid
INNER JOIN topic_to_framework ttc on ttc.ft_topicid = t.t_id
INNER JOIN ca_hss_frameworkchapters ch ON ch.ch_id = ttc.ft_frameworkid
WHERE r.res_status = 'Active'
GROUP BY r.res_id
--------------
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------------+------+--------------------------+
| 1 | SIMPLE | r | ref | PRIMARY,res_status,res_category | res_status | 1 | const | 1 | Using where; Using index |
| 1 | SIMPLE | ttr | ref | PRIMARY,rt_topicid | PRIMARY | 4 | test.r.res_id | 1 | Using index |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | test.ttr.rt_topicid | 1 | Using index |
| 1 | SIMPLE | ttc | ref | PRIMARY,ft_frameworkid | PRIMARY | 4 | test.ttr.rt_topicid | 1 | Using index |
| 1 | SIMPLE | ch | eq_ref | PRIMARY | PRIMARY | 4 | test.ttc.ft_frameworkid | 1 | NULL |
+----+-------------+-------+--------+---------------------------------+------------+---------+-------------------------+------+--------------------------+
5 rows in set (0.00 sec)
--------------
EXPLAIN
SELECT r.res_id, GROUP_CONCAT(DISTINCT cs.cs_code ORDER BY cs.cs_id SEPARATOR ', ') AS 'ContentStandards'
FROM hre_resources AS r
INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id
INNER JOIN topics t on t.t_id = ttr.rt_topicid
INNER JOIN topic_to_cs tcs on tcs.cst_topicid = t.t_id
INNER JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid
WHERE r.res_status = 'Active'
GROUP BY r.res_id
--------------
+----+-------------+-------+--------+---------------------------------+------------+---------+--------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+------------+---------+--------------------------------+------+--------------------------+
| 1 | SIMPLE | r | ref | PRIMARY,res_status,res_category | res_status | 1 | const | 1 | Using where; Using index |
| 1 | SIMPLE | ttr | ref | PRIMARY,rt_topicid | PRIMARY | 4 | test.r.res_id | 1 | Using index |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | test.ttr.rt_topicid | 1 | Using index |
| 1 | SIMPLE | tcs | ref | PRIMARY,cst_contentstandardid | PRIMARY | 4 | test.ttr.rt_topicid | 1 | Using index |
| 1 | SIMPLE | cs | eq_ref | PRIMARY | PRIMARY | 4 | test.tcs.cst_contentstandardid | 1 | NULL |
+----+-------------+-------+--------+---------------------------------+------------+---------+--------------------------------+------+--------------------------+
Below is the DDL I assumed for the tables. It would have been helpful if you had included this in your original question.
CREATE TABLE categories (
c_id INT PRIMARY KEY,
c_name VARCHAR(100) NOT NULL DEFAULT 'c_name'
);
CREATE TABLE hre_resources (
res_id INT PRIMARY KEY,
res_category INT NOT NULL,
res_status ENUM('Active', 'Inactive') NOT NULL DEFAULT 'Active',
res_type VARCHAR(100) NOT NULL DEFAULT 'type',
res_title VARCHAR(100) NOT NULL DEFAULT 'title',
res_filename VARCHAR(100) NOT NULL DEFAULT '',
res_source VARCHAR(100) NOT NULL DEFAULT '',
res_gradelevel_from VARCHAR(100) NOT NULL DEFAULT '',
res_gradelevel_to VARCHAR(100) NOT NULL DEFAULT '',
res_tags VARCHAR(100) NOT NULL DEFAULT '',
res_description VARCHAR(100) NOT NULL DEFAULT '',
res_onserver VARCHAR(100) NOT NULL DEFAULT '',
res_favoriteaccts VARCHAR(100) NOT NULL DEFAULT '',
res_CreatedOn DATE NOT NULL DEFAULT '2017-01-01',
KEY (res_status),
FOREIGN KEY (res_category) REFERENCES categories(c_id)
);
CREATE TABLE topics (
t_id INT PRIMARY KEY,
t_name VARCHAR(100) NOT NULL DEFAULT 't_name'
);
CREATE TABLE ca_hss_frameworkchapters (
ch_id INT PRIMARY KEY,
ch_title VARCHAR(100) NOT NULL DEFAULT 'ch_title',
ch_code VARCHAR(100) NOT NULL DEFAULT 'ch_code'
);
CREATE TABLE ca_hss_contentstandards (
cs_id INT PRIMARY KEY,
cs_code VARCHAR(100) NOT NULL DEFAULT 'cs_code'
);
CREATE TABLE schoolsubjects (
s_id INT PRIMARY KEY,
s_name VARCHAR(100) NOT NULL DEFAULT 's_name'
);
CREATE TABLE topic_to_framework (
ft_topicid INT NOT NULL,
ft_frameworkid INT NOT NULL,
PRIMARY KEY (ft_topicid, ft_frameworkid),
FOREIGN KEY (ft_topicid) REFERENCES topics(t_id),
FOREIGN KEY (ft_frameworkid) REFERENCES ca_hss_frameworkchapters(ch_id)
);
CREATE TABLE topic_to_resource (
rt_resourceid INT NOT NULL,
rt_topicid INT NOT NULL,
PRIMARY KEY (rt_resourceid, rt_topicid),
FOREIGN KEY (rt_resourceid) REFERENCES hre_resources (res_id),
FOREIGN KEY (rt_topicid) REFERENCES topics(t_id)
);
CREATE TABLE topic_to_cs (
cst_topicid INT NOT NULL,
cst_contentstandardid INT NOT NULL,
PRIMARY KEY (cst_topicid, cst_contentstandardid),
FOREIGN KEY (cst_topicid) REFERENCES topics(t_id),
FOREIGN KEY (cst_contentstandardid) REFERENCES ca_hss_contentstandards(cs_id)
);
CREATE TABLE schoolsubjects_to_resource (
sr_resid INT NOT NULL,
sr_sid INT NOT NULL,
PRIMARY KEY (sr_resid, sr_sid),
FOREIGN KEY (sr_resid) REFERENCES hre_resources(res_id),
FOREIGN KEY (sr_sid) REFERENCES schoolsubjects(s_id)
);
Here's an example in Python of what I mean by merging the results of multiple queries together.
import pprint
import MySQLdb
import MySQLdb.cursors
db = MySQLdb.connect(host="localhost", db="test", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()
res_data = {}
sql = """SELECT r.res_id, r.res_status, r.res_type, r.res_category, r.res_title, r.res_filename, r.res_source, r.res_gradelevel_from, r.res_gradelevel_to, r.res_tags, r.res_description, r.res_onserver, r.res_favoriteaccts, r.res_CreatedOn, c.c_name AS 'Categories' FROM hre_resources AS r INNER JOIN categories c ON c_id = r.res_category WHERE r.res_status = 'Active'"""
cur.execute(sql)
for row in cur.fetchall():
res_data[row['res_id']] = row
sql = """SELECT r.res_id, GROUP_CONCAT(s.s_name ORDER BY s.s_id SEPARATOR ', ') AS 'SchoolSubjects' FROM hre_resources AS r INNER JOIN schoolsubjects_to_resource str ON str.sr_resid = r.res_id INNER JOIN schoolsubjects s ON s.s_id = str.sr_sid WHERE r.res_status = 'Active' GROUP BY r.res_id"""
cur.execute(sql)
for row in cur.fetchall():
res_data[row['res_id']].update(row)
sql = """SELECT r.res_id, GROUP_CONCAT(t.t_name ORDER BY t_id SEPARATOR ', ') AS 'Topics' FROM hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN topics t on t.t_id = ttr.rt_topicid WHERE r.res_status = 'Active' GROUP BY r.res_id"""
cur.execute(sql)
for row in cur.fetchall():
res_data[row['res_id']].update(row)
sql = """SELECT r.res_id, GROUP_CONCAT(DISTINCT CONCAT(ch.ch_code, ':', ch.ch_title) ORDER BY ch.ch_id SEPARATOR ', ') AS 'Chapters' FROM hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN topics t on t.t_id = ttr.rt_topicid INNER JOIN topic_to_framework ttc on ttc.ft_topicid = t.t_id INNER JOIN ca_hss_frameworkchapters ch ON ch.ch_id = ttc.ft_frameworkid WHERE r.res_status = 'Active' GROUP BY r.res_id"""
cur.execute(sql)
for row in cur.fetchall():
res_data[row['res_id']].update(row)
sql = """SELECT r.res_id, GROUP_CONCAT(DISTINCT cs.cs_code ORDER BY cs.cs_id SEPARATOR ', ') AS 'ContentStandards' FROM hre_resources AS r INNER JOIN topic_to_resource ttr ON ttr.rt_resourceid = r.res_id INNER JOIN topics t on t.t_id = ttr.rt_topicid INNER JOIN topic_to_cs tcs on tcs.cst_topicid = t.t_id INNER JOIN ca_hss_contentstandards cs ON cs.cs_id = tcs.cst_contentstandardid WHERE r.res_status = 'Active' GROUP BY r.res_id"""
cur.execute(sql)
for row in cur.fetchall():
res_data[row['res_id']].update(row)
db.close()
pp = pprint.PrettyPrinter()
pp.pprint(res_data)
After I added one row of fake data to each table, I got this output from the script above:
{2L: {'Categories': 'c_name',
'Chapters': 'ch_codech_title',
'ContentStandards': 'cs_code',
'SchoolSubjects': 's_name',
'Topics': 't_name',
'res_CreatedOn': datetime.date(2017, 1, 1),
'res_category': 1L,
'res_description': '',
'res_favoriteaccts': '',
'res_filename': '',
'res_gradelevel_from': '',
'res_gradelevel_to': '',
'res_id': 2L,
'res_onserver': '',
'res_source': '',
'res_status': 'Active',
'res_tags': '',
'res_title': 'title',
'res_type': 'type'}}
这篇关于mySQL>>使用CONCAT，GROUP_CONCAT和多个JOINS进行非常慢的查询（63条记录为100秒）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
