jsmars jsmars - 1 month ago 8
MySQL Question

How do I make this personal best highscore MySQL query more efficient?

I have a database for game leaderboard highscores holding currently about 30.000 entries, and the below query looks for the personal best highscore for a certain game and player, but it takes about 60 seconds to execute. I'm thinking there should be a much more efficient way to do this, maybe using a composite index, but which one would that be?

SELECT name, score, date, version, mode, attempts, time, id
FROM highscore h1
WHERE score = (
SELECT MAX( score )
FROM highscore h2
WHERE name = h1.name && gamename = "asteroids" && name = "bob"
)


I currently have indexes for:

id
score
name
name-gamename (composite)


Any help is greatly appreciated!

Answer

I would write the query using standard SQL syntax:

SELECT name, score, date, version, mode, attempts, time, id
FROM highscore h1
WHERE score = (SELECT MAX( score ) 
               FROM highscore h2
               WHERE h2.name = h1.name AND h2.gamename = 'asteroids' AND h2.name = 'bob'
              );

For this purpose, you want a composite index: highscore(name, gamename, score).

If you are only looking for one row (even when there are ties), then this might be a wee bit faster:

SELECT name, score, date, version, mode, attempts, time, id
FROM highscore h 
WHERE h.gamename = 'asteroids' AND h.name = 'bob'
ORDER BY score DESC
LIMIT 1;

The appropriate index is highscore(name, gamename, score).

Comments