How can I improve this PHP MySql Rank generation function?
(Mostly looking to improve speed, but any feedback is very welcome)

Right now I keep stats and ranks in 2 separate tables. The only problem with this is I don't know how combine the select and update queries to possibly increase speed by cutting the PHP processing of the results and the update out of it. Also this function is very time consuming. It takes about 5-10 minutes to process 300,000+ ranks. This isn't really too bad, except I have to do this 50+ times. I do like how it doesn't lock up my database because getting the ranks only takes a few seconds, and all the updates are done individually, which leaves room for other queries to process instead of locking for 5-10 minutes each time.

function get_ranking($rank, $rank_order, $DBH, $test_mode=true)
    $test_mode == true ? $limter = ' LIMIT 50' : $limter = ''; 

    $query0 = 'SET @rank=0';
    $query1 = 'SELECT @rank:=@rank+1 AS rank, player_id FROM player_stats ORDER BY '.$rank_order.$limter;

        $qh = $DBH->prepare($query1);

        if ($qh->execute()) 
            while ($result = $qh->fetch())
                $query2 = 'UPDATE `player_ranks` SET '.$rank.' = \''.$result['rank'].'\' WHERE `player_id` =\''.$result['player_id'].'\'';

                $qh2 = $DBH->prepare($query2);

            return true; 

        return false; 
    catch (PDOException $e) 
        return false;

Thanks for any advice/feedback/help!


Something you might try is keeping everything inside the database. Using the SELECT, create a temporary table:

CREATE TEMPORARY TABLE tmp_ranks (rank int auto_increment primary key, player_id int)
AS SELECT NULL AS rank, player_id AS player_id FROM player_stats
ORDER BY '.$rank_order.$limter

This should give you a table that looks like

| rank | player_id |
|    1 | 123456789 |
|    2 | 123456780 |

Now, using an UPDATE with a JOIN, update your player_ranks table:

UPDATE player_ranks, tmp_ranks SET player_ranks.$rank = tmp_ranks.rank
WHERE player_ranks.player_id = tmp_ranks.player_id;

This should run fairly quickly, but given your data size, could take a while. I'd suggest testing it on a non-production box first, in case it locks things up for a good while.

Note that I left your PHP variables inline, so you'll need to fix those.

  • \$\begingroup\$ You have a good point. I originally used this statement similar to this to update my ranks: UPDATE dbTable SET rank_fd = @rank := @rank + 1 ORDER BY fd2 DESC, fd3 DESC;. It was still very slow and locked up my database during the update (which locked up my website). My testing box(i7 920, 6GB RAM, 4x 320GB Raid 0) is a beast in comparison to my server (only a P4, 2GB ram, 250GB HDD). The server runs about 5x+ slower. I'm not familiar with JOINs and TEMPORARY tables. I'll have to go test this out though, looks interesting. \$\endgroup\$ – PiZzL3 May 19 '11 at 15:24
  • \$\begingroup\$ I also thought about splitting the queries into smaller queries using LIMIT(to fit in php memory, querying all data won't fit) and getting the results for each section and doing a single update query for each player. I'm not really sure if that will be faster, but I'm thinking it would at least cut down on some of the php processing overhead and should cut down on some of the sql overhead(going from 15M update queries to 300K). \$\endgroup\$ – PiZzL3 May 19 '11 at 15:31

It seems like you're just ordering player_stats by some passed-in column and then going through numbering the result starting at 1. It might be simpler to forget storing explicit rank in your DB, and instead implement the numbering on the view layer.

  • \$\begingroup\$ Interesting idea... unfortunately that would void the purpose of this function. I already have what you suggest on a leader-board that I generate with a different function (just slightly different). The purpose of this function is to make each players rank (in each of the 50+ categories) readily accessible for their viewing without having to calculate it out every time the data is accessed. Even numbering it in the view-model for 50x+ 300k+ ranks would be too costly (I have to generate my leader-board on a cron job). \$\endgroup\$ – PiZzL3 May 16 '11 at 13:52

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.