mysql - 如何优化count by max的主从查询？

Question

我有一个查询，要获取每个游戏的当前玩家数量，并根据玩家填充的玩家位置百分比进行排序。

事情是这样的:

SELECT 
  g.* , COUNT( p.ID ) NUM_OF_PLAYERS 
FROM 
  games g, 
  players p
WHERE  
  g.ID = p.GAME_ID 
GROUP BY 
  g.ID 
ORDER BY 
  COUNT( p.ID ) / g.MAX_NUM_OF_PLAYERS DESC 

大约需要 4 秒。

解释主要给了我这个:

如何让它更快？

Answer 1

尝试这个查询。是不是更快了？

select g.*,p.NUM_OF_PLAYERS NUM_OF_PLAYERS 
from games g
left join 
(
select GAME_ID, COUNT( ID ) NUM_OF_PLAYERS 
  from players group by GAME_ID
) p on (g.ID = p.GAME_ID)

ORDER BY 
  p.NUM_OF_PLAYERS/g.MAX_NUM_OF_PLAYERS DESC