sql - 如何在MySQL中优化查询？

Question

我需要改善查询，特别是执行时间，这是我的查询：

SELECT SQL_CALC_FOUND_ROWS p.*,v.type,v.idName,v.name as etapaName,m.name AS manager,
    c.name AS CLIENT, 
    (SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(duration)))
    FROM activities a 
    WHERE a.projectid = p.projectid) AS worked, 
    (SELECT SUM(TIME_TO_SEC(duration)) 
    FROM activities a 
    WHERE a.projectid = p.projectid) AS worked_seconds,
    (SELECT SUM(TIME_TO_SEC(remain_time)) 
    FROM tasks t 
    WHERE t.projectid = p.projectid) AS remain_time

FROM projects p

INNER JOIN users m
ON p.managerid = m.userid

INNER JOIN clients c
ON p.clientid = c.clientid

INNER JOIN `values` v
ON p.etapa = v.id

WHERE 1 = 1 

ORDER BY idName 

ASC

该执行时间为aprox。 5秒如果我删除此部分： (SELECT SUM(TIME_TO_SEC(remain_time)) FROM tasks t WHERE t.projectid = p.projectid) AS remain_time
执行时间减少到0.3秒。有没有一种方法可以获取rest_time的值以减少exec.time？

从PHP调用SQL（如果这与任何建议的解决方案有关）。

Answer 1

听起来您需要在tasks上建立索引。

尝试添加以下内容：

create index idx_tasks_projectid_remaintime on tasks(projectid, remain_time);

相关的子查询应该只使用索引并且执行得更快。