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

score 1 · Accepted Answer · answered Jul 03 '12 at 07:33

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.

score 0 · Answer 2 · answered Jul 03 '12 at 04:32

I think you can do all of this with one query, something like

UPDATE high_scores a SET rank = (SELECT count(uid) + 1 FROM high_scores b WHERE b.score > a.score) ORDER BY score DESC

I don't have MySQL available right now to try this, unfortunately, so if this turns out to not be feasible at all I'll delete this.

score 0 · Answer 3 · edited May 23 '17 at 11:48

Have a quick google of mysql's 'CASE'.

You'll most likely find the answer here though: Stackoverflow #9346755

Essentially, you have one SQL line that has it's own "internal switch-statement". You just have to tell it what to do when a given field has a certain value. EG. in the above link, it's saying..

When `id` is 1, then set the value to 12. 
When `id` is 2, then set the value to 42. 
etc.

Your only concern might be with the 7000+ rows.

But, using PHP, I'd have the data in an array and then using array_slice(), get 200/300 records at a time, set up the SQL line, and execute. Then get the next 200/300 records and repeat, until it reaches the end of the PHP array.

Edit: Keep in mind though, the above will only work when you're updating ONE field, based on another field (such as a key).

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

3 Answers3