2
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

You will have to test performance, but a GROUP BY should help:

SELECT playerName, MAX(points) AS points /*[, other columns]*/
FROM game
WHERE date >= date_sub(CURDATE(),INTERVAL 1 MONTH)
GROUP BY playerName /*[, other columns]*/
ORDER BY points DESC
LIMIT 0, 20
|improve this answer|||||
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.