phpmysqlnavicat

Mysql taking too long time to respond when using order by on alias

I am trying to sum two fields from two different table based on user id. Here is my query

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;

when I remove ORDER BY points data is being fetched quickly. But with ORDER BY points It's taking too long time for almost 5 minutes. Sometimes connection lost message is returned.

Can someone please help? really struggling with this issue

as Progman asked here is the EXPLAIN of with 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

without 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
Solution

  • Here subquery performs better for avoiding duplicacy

    -- 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;