Can this be optimized?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Can this be optimized?

Post 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
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Re: Can this be optimized?

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