Page 2 of 2
Re: Can this be optimized?
Posted: Sun Feb 22, 2009 12:06 pm
by Eran
Returns a the username and the amount of highscores the user has, but when I add them all up, it comes to 1086 even though there are only 751 games
This is because with this query users with the same highscore are each counted. As I suggested, you should probably drop the COUNT() and the last GROUP BY (which work together), add the username and time to the query and count them yourself (as you did before).
Also, add a composite index on gamename + score
Code: Select all
ALTER TABLE `arcade_highscores` ADD INDEX ( `gamename` , `score` ) ;
Should significantly speed up the query
Re: Can this be optimized?
Posted: Wed Feb 25, 2009 4:15 pm
by Citizen
Hey guys,
Just wanted to give a heads up that this thread was a total success.
My end query is:
Code: Select all
SELECT ah.`username`, ah.`gamename`, ah.`time`, ah.`scoreid`
FROM (
SELECT MAX( score ) AS maxscore, MIN( score ) AS minscore, gamename
FROM `arcade_highscores`
GROUP BY gamename
) AS m
INNER JOIN `arcade_games` AS ag ON `ag`.`gameid` = `m`.`gamename`
INNER JOIN `arcade_highscores` AS ah ON (
m.maxscore = ah.score
AND m.gamename = ah.gamename
AND ag.isreverse =0
) OR (
m.minscore = ah.score
AND m.gamename = ah.gamename
AND ag.isreverse =1
)
Which improved the loop query method from 11 second execution time to 9 seconds.
With indexes added, the execution time was reduced to (drum roll please)....
0.27 seconds
Thank you everyone that helped with this optimization. Learned lots
