Mysql使用order by on alias时响应时间过长

Question

我试图根据用户id从两个不同的表中求和两个字段。这是我的查询

SELECT
users.id,
users.`name`,
users.`type`,
((SELECT IFNULL(SUM(answers.points),0) FROM answers WHERE answers.answered_by=users.id)
+
(SELECT IFNULL(SUM(points.point),0) FROM points WHERE points.user_id=users.id)) points 
FROM
users
WHERE
users.type = '3' GROUP BY users.id ORDER BY points DESC LIMIT 0,100;

当我删除ORDER BY点时，数据正在被快速获取。但按点排序耗时太长，差不多5分钟。有时返回连接丢失消息。

有人能帮帮我吗?真的很纠结这个问题

作为程序问这里是与ORDER BY的解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY users   ALL PRIMARY             18396   Using where; Using temporary; Using filesort
3   DEPENDENT SUBQUERY  points  ALL                 39  Using where
2   DEPENDENT SUBQUERY  answers ALL                 240347  Using where

没有ORDER BY

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY users   index   PRIMARY PRIMARY 4       100 Using where
3   DEPENDENT SUBQUERY  points  ALL                 39  Using where
2   DEPENDENT SUBQUERY  answers ALL                 240347  Using where

Answer 1

这里子查询可以更好地避免重复

-- MySQL
SELECT u.id
, u.name
, u.type
, COALESCE(p.points, 0) points
FROM users u
LEFT JOIN (SELECT t.user_id, SUM(points) points
FROM ( SELECT answered_by AS user_id, points
FROM answers
UNION ALL
SELECT user_id, point AS points
FROM points ) t
GROUP BY t.user_id ) p
ON u.id = p.user_id
WHERE u.type = '3'
ORDER BY p.points DESC LIMIT 0, 100;

Answer 2

我可以建议下一个查询:

SELECT
users.id,
users.`name`,
users.`type`,
(
IFNULL(SUM(answers.points),0) + 
IFNULL(SUM(points.point),0)
) points 
FROM users
LEFT JOIN answers ON answers.answered_by=users.id
LEFT JOIN points ON points.user_id=users.id
WHERE
users.type = 3 
GROUP BY users.id 
ORDER BY points 
DESC LIMIT 0,100;

使用JOIN应该比子查询更有效