mysql - 求和查询花费太多时间

Question

以下查询大约需要 19 秒才能获取 7877 条记录。我想减少时间。

SELECT 
dvm.pk_user_id,
dvm.UID, 
IFNULL((SELECT SUM(taskassign.vreward_points) FROM tbl_task_master AS tasks LEFT JOIN tbl_task_assign_details AS taskassign ON tasks.pk_task_id = taskassign.fk_task_id WHERE taskassign.vtaskstatus = '1' AND dvm.pk_user_id = taskassign.fk_user_id GROUP BY taskassign.fk_user_id),0) AS earning_reward_points 
FROM tbl_digital_volunteer_master AS dvm 
ORDER BY earning_reward_points DESC,
pk_user_id ASC 

Answer 1

做同样的事情但使用连接怎么样

SELECT 
dvm.pk_user_id,
dvm.UID, 
IFNULL(t.earning_reward_points,0) AS earning_reward_points 
FROM tbl_digital_volunteer_master AS dvm 
LEFT JOIN (
    SELECT taskassign.fk_user_id, SUM(taskassign.vreward_points) AS earning_reward_points 
    FROM tbl_task_master AS tasks 
    LEFT JOIN tbl_task_assign_details AS taskassign ON tasks.pk_task_id = taskassign.fk_task_id 
    WHERE taskassign.vtaskstatus = '1' 
    GROUP BY taskassign.fk_user_id
) AS t ON  dvm.pk_user_id = t.fk_user_id 
ORDER BY earning_reward_points DESC, pk_user_id ASC