社区首页 >问答首页 >多次连接同一表时优化Mysql查询

问多次连接同一表时优化Mysql查询
EN

Stack Overflow用户

提问于 2017-10-26 02:01:05

回答 2查看 406关注 0票数 2

Can anybody help me optimize this mysql query?
The query is taking more than 1 min to fetch 1000 rows of data. 
The sub query with FIND_IN_SET(id,GROUP_CONCAT(t.id))!=0 in the select statement is taking too much time to fetch data.

另外，请提供可以用于优化查询的提示，因为我们必须多次加入相同的表。查询如下：

   SELECT mem.id,
    CASE 
    WHEN t.due_date IS NOT NULL THEN
    (SELECT description FROM descriptionTable WHERE CODE=
    (SELECT task_type FROM memberTask WHERE FIND_IN_SET(id,GROUP_CONCAT(t.id))!=0 AND due_date=MIN(t.due_date) LIMIT 1))
    ELSE
    (SELECT description FROM descriptionTable WHERE CODE=
    (SELECT task_type FROM memberTask WHERE FIND_IN_SET(id,GROUP_CONCAT(t1.id))!=0 AND due_date=MIN(t1.due_date) LIMIT 1))
    END AS task_type
    FROM member mem
    INNER JOIN memberProgram p ON mem.id=p.member_id
    LEFT JOIN memberTask t ON t.program=p.prog_name AND t.member_id=p.member_id AND t.status=1
    LEFT JOIN memberTask t1 ON t1.member_id=p.member_id AND t1.status=1 AND t1.program IS NULL 
    GROUP BY mem.id

提前谢谢！

sql

mysql

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-10-31 04:36:12

您使用该查询的方式将增加查询执行的复杂性，并且它将必须通过许多交叉连接的行来提取所需的行。更好的解决方案将首先从目标表: memberTask中提取所需的行，然后将其连接到主查询。

SELECT 
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NOT NULL THEN t1.id ELSE NULL END,'')),',','') AS first_dueDate,
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NOT NULL THEN 'first' ELSE 'second' END,'')),',','') AS selectFlag,
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NULL THEN t1.id ELSE NULL END,'')),',','') AS first_dueDate,
aa.member_id FROM memberTask t1
INNER JOIN (SELECT due_date,program,aa.member_id,MAX(tsk.id) AS selId FROM memberTask tsk 
INNER JOIN ( SELECT t.member_id,t.program,t.task_type,MIN(due_date) AS seldate FROM memberTask t WHERE tsk.status=1 
GROUP BY t.member_id,IFNULL(t.program,''))aa 
ON tsk.member_id=aa.member_id AND IFNULL(tsk.program,'')=IFNULL(aa.program,'') AND tsk.due_date=aa.seldate 
GROUP BY due_date,program,aa.member_id) bb ON t1.id=bb.selId GROUP BY t1.member_id,t1.program

查询的详细内容如下：

SELECT t.member_id,t.program,t.task_type,MIN(due_date) AS seldate FROM memberTask t WHERE tsk.status=1 
GROUP BY t.member_id,IFNULL(t.program,'')

这是最内部的查询，它将根据memberid及其选择最小到期日的程序来提取记录。

SELECT due_date,program,aa.member_id,MAX(tsk.id) AS selId FROM memberTask tsk 
INNER JOIN (Inner Most Query)aa 
ON tsk.member_id=aa.member_id AND IFNULL(tsk.program,'')=IFNULL(aa.program,'') AND tsk.due_date=aa.seldate 
GROUP BY due_date,program,aa.member_id

此查询将在具有相同到期日的行中提取最大id。

SELECT 
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NOT NULL THEN t1.id ELSE NULL END,'')),',','') AS first_dueDate,
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NOT NULL THEN 'first' ELSE 'second' END,'')),',','') AS selectFlag,
REPLACE(GROUP_CONCAT(COALESCE(CASE WHEN t1.program IS NULL THEN t1.id ELSE NULL END,'')),',','') AS first_dueDate,
aa.member_id FROM memberTask t1
INNER JOIN (second query) bb ON t1.id=bb.selId GROUP BY t1.member_id,t1.program

这将把整个多行数据拖到单行。

现在，在将所选数据连接到主查询时，您应该使用以下情况。

SELECT * FROM member mem
INNER JOIN program pro om mem.id=pro.member_id
LEFT JOIN tmpTable tl ON t1.member_id=mem.member_id AND
CASE WHEN FIND_IN_SET(pro.`prog_name`, t1.prog_relate_task) THEN t1.program=pro.program AND t1.selectFlag='first'
ELSE t1.selectFlag='second' END

这是对你的案件的一般理解。您可以根据需要更改拉记录。

让我知道它是否有效？

票数 3

Stack Overflow用户

发布于 2017-10-26 02:40:08

我仍然不确定我是否完全理解这个查询应该做什么。我的理解是:对于每个成员来说，使用最小到期日获得任务(无论是否与程序相关)，并为此获取任务类型的描述。

因此:选择成员并在带有LIMIT的子查询中获取描述

select
  m.id,
  (
    select d.description
    from membertask t
    join descriptiontable d on d.code = t.task_type
    where t.member_id = m.id
    order by t.due_date
    limit 1
  ) as description
from member;