In a web game there is a ranking of the top points games played of the month:
// QUERY
SELECT * FROM game
WHERE date >= date_sub(CURDATE(),INTERVAL 1 MONTH)
ORDER BY points DESC limit 0, 20
But, in that case one player can be the top 1,2,3... so I have changed the query to add a distinct, and then only appears one game for player (the game with max points):
// Query that filters player names
SELECT g.*
FROM game g
INNER JOIN ( SELECT playerName, MAX(points) AS MaxPoints
FROM game
GROUP BY playerName
) groupGame
ON g.playerName = groupGame.playerName AND g.points = groupGame.MaxPoints
WHERE date >= date_sub(CURDATE(),INTERVAL 1 MONTH)
ORDER BY points DESC limit 0, 20
The query works OK but the second query is much slower than the first. Is there any way to optimize it?