The best way to update user ratings without killing a server

Question

I have a website with a user rating as the central part, but the number of users has grown to more than 50,000, and it puts a strain on the server to sort through all those that update the rating every 5 minutes. Is there a better way that you can use to quickly update ranks at least every 5 minutes? It doesn’t have to be with php, it could be something that runs as a perl script or something in this case if something like this can improve the work (although I'm not sure why this would be just leaving my options open here).

This is what I am doing now to update ranks:

$get_users = mysql_query("SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC");
$i=0;
while ($a = mysql_fetch_array($get_users)) {
    $i++;
    mysql_query("UPDATE users SET month_rank = '$i' WHERE id = '$a[id]'");
}

UPDATE (decision):

Here is a solution code that takes less than 1/2 second to execute and update all 50,000 rows (make ranking the primary key, as Tom Haig suggested).

mysql_query("TRUNCATE TABLE userRanks");
mysql_query("INSERT INTO userRanks (userid) SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC");
mysql_query("UPDATE users, userRanks SET users.month_rank = userRanks.rank WHERE users.id = userRanks.id");

Answer 1

Make an userRanks.rankauto-increment primary key. If you then enter user IDs userRanksin descending order of rank, this will increase the column rankin each row. It should be very fast.

TRUNCATE TABLE userRanks;
INSERT INTO userRanks (userid) SELECT id FROM users WHERE status = '1' ORDER BY month_score DESC;
UPDATE users, userRanks SET users.month_rank = userRanks.rank WHERE users.id = userRanks.id;

Answer 2

My first question is: why do you perform this operation like polling every five minutes?

, - , . , 50 000 .

, "status = '1'" , , , , , ?

, .

, , , , , .

Answer 3

:

set @rnk = 0;
update users 
set month_rank = (@rnk := @rnk + 1)
order by month_score DESC

(@rnk), . , month_rank .

Answer 4

. , , .

100% ( MySQL ), , MS SQL Server 2000

DECLARE @tmp TABLE
(
    [MonthRank] [INT] NOT NULL,
    [UserId] [INT] NOT NULL,
)

INSERT INTO @tmp ([UserId])
SELECT [id] 
FROM [users] 
WHERE [status] = '1' 
ORDER BY [month_score] DESC

UPDATE users 
SET month_rank = [tmp].[MonthRank]
FROM @tmp AS [tmp], [users]
WHERE [users].[Id] = [tmp].[UserId]

MS SQL Server 2005/2008 , , CTE.

Answer 5

, , , . , .

?

Answer 6

. , . , 50 000 . , - . , , , .

mysql_query("delete from month_rank_staging;");
while(bla){
  mysql_query("insert into month_rank_staging values ('$id', '$i');");
}
mysql_query("update month_rank_staging src, users set users.month_rank=src.month_rank where src.id=users.id;");

() , . , . , , mysql, ..

Answer 7

, . , ...

Answer 8

. , . . , . WHERE UPDATE - , auto_increment, . . , ( ). SELECTS, .