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 :)