Most efficient way to update 1000's of rows with mySQL?

Question

I'm working on a project that so far has 7000+ rows of leaderboard data, which contains name, id, score, and rank. It's continuously expanding and I've currently hit a snag in which the server times out after trying to update everything every time someone posts a new score.

Currently I'm using PDO with a query combined with a looped execute() call. Is there a significantly faster way of doing this (perhaps in a single mySQL query)? Alternatively, do you have any suggestions for how to handle updating a leaderboard?

    $stmt = $dbh->prepare("SELECT * FROM high_scores ORDER BY score DESC");
    $stmt->execute();

    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $results = $stmt->fetchAll();

    //

    $numItems = count($results);

    // is this OK or a no-no?

    try {

        $stmt = $dbh->prepare("UPDATE high_scores SET rank = :rank WHERE uid = :uid");

        for($i=0; $i<$numItems; $i++){
            $rank = $i + 1;
            $uid = $results[$i]['uid'];
            $stmt->execute(array(':rank' => $rank, ':uid' => $uid));
        }

        return true;

    } catch (PDOException $e) {
        echo($e->getMessage()); 
    }

Answer 1

Why would you need to store the rank? That's a denormalization form, did you need it really or did you implement that step because it was expected to be needed?

See for example this solution:

http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Maybe try how this performs?

Also, if you see the example he made you can use a variable in a query. That will also allow you for this update statement to combine it in a single query. A query in a loop is a hard one, will hardly even be really efficient unfortunately.