时间戳

首页 > 解决方案 > 求和查询花费太多时间

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

问题描述

下面的查询大约需要 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

标签： mysql

解决方案

做同样的事情但加入一个怎么样

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

推荐阅读