MySQL子查询，连接和排名查询-为什么很慢？

Question

我的查询需要花费很长时间才能加载members中的800+行和members_points中的5,100+行。

是否有一种我忽略的更有效的方法，或者用MySQL 5.5.33编写这个查询的另一种方法？

*更新: 5.5是我的本地主机，5.7是实时服务器。没有看到任何明显的差别。

这是我到目前为止所得到的-谢谢你的反馈

  $result = $conn->query("
  SELECT subquery.*, 
         @rank := @rank + 1 `rank`
  FROM (SELECT m.id, 
               m.Name,
               m.Column_1,
               m.Column_2,
               m.Column_3,
               SUM(m.id = mp.id_from) total_sent,
               SUM(m.id = mp.id_to) total_received,
               SUM(m.id = mp.id_from) - SUM(m.id = mp.id_to) `points`
        FROM members m
        INNER JOIN members_points mp
        WHERE Account_Active LIKE 'TRUE'
        GROUP BY m.id, 
                 m.Name) subquery, (SELECT @rank := 0) variable
  ORDER BY `points` DESC;
  ");

Answer 1

SELECT subquery.*, 
         @rank := @rank + 1 `rank`
  FROM (SELECT m.id, 
               m.Name,
               m.Column_1,
               m.Column_2,
               m.Column_3,
               SUM(m.id = mp.id_from) total_sent,
               SUM(m.id = mp.id_to) total_received,
               SUM(m.id = mp.id_from) - SUM(m.id = mp.id_to) `points`
        FROM members m
        INNER JOIN members_points mp
        WHERE Account_Active LIKE 'TRUE'
        GROUP BY m.id, 
                 m.Name) subquery, (SELECT @rank := 0) variable
  ORDER BY `points` DESC;
  ");

在执行任何JOINs之前，先从计数开始。

SELECT id_from, COUNT(*) AS from_ct FROM members_points GROUP BY 1
SELECT id_to,   COUNT(*) AS   to_ct FROM members_points GROUP BY 1

然后使用这些作为子查询并向外工作。

如果你想或不想让一个活跃的成员和一个不活跃的成员聊天，可能会出现问题。桌子上有多少行？

现在看看这是否适合subquery

SELECT m.id, 
           m.Name,
           m.Column_1,
           m.Column_2,
           m.Column_3,
           froms.from_ct total_sent,
           tos.to_ct     total_received,
           froms.from_ct - tos.to_ct `points`
    FROM members m
    JOIN ( SELECT id_from, COUNT(*) AS from_ct FROM members_points GROUP BY 1
         ) AS froms  ON froms.id_from = m.id
    JOIN ( SELECT id_to,   COUNT(*) AS   to_ct FROM members_points GROUP BY 1
         ) AS tos    ON tos.id_to = m.id
    JOIN members_points mp
    WHERE m.Account_Active = 'TRUE'
    GROUP BY m.id, 
             m.Name