How can I improve this PHP MySql Rank generation function?

Question

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;

    try
    {
        $DBH->exec($query0); 
        $qh = $DBH->prepare($query1);
        $qh->setFetchMode(PDO::FETCH_ASSOC);

        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);
                $qh2->execute();
            }

            return true; 
        } 

        return false; 
    } 
    catch (PDOException $e) 
    {
        save_PDO_error_to_file($e);
        return false;
    }   
}

Thanks for any advice/feedback/help!

Answer 1

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.

Answer 2

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.