A more effecient way to update a large table...

[Nhoj]

I have a script that orders every user by an ever changing number descending very often and then updates their ranking starting with 1 and moving on until every user is covered... Is there a more effecient way to do it than this?

















[code=php:0]$query = mysql_query('SELECT `uID` FROM `users` ORDER BY `uNetWorth` DESC');

$i = 0;
while ($player = mysql_fetch_assoc($query)) {
$i++;
mysql_query('UPDATE `users_list` SET `uRanking` = '.$i.' WHERE `uID` = '.$player['uID'].' LIMIT 1');
}[/code]

Thanks ::)

[anatak]

the only way I think to make it more efficient is by making a stored procedure.
that way you don't have the trafic between php and mysql.
I am not sure how much more efficient it will be though

if I had to do this I would create a temporary table with an autoincrement.
insert the uId into that table so that way you have the ranking (autoincrement) and the uId
and then update the users_list table

anatak

[Nhoj]

How would I go about making that into a stored procedure? - I've never used em...

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html (Wasn't much help >:()

[anatak]

well that is the big problem
it has been 5 years since I used stored procedures so I am not going to be a great help.

But I would post this question in the MySQL forum. I guess your chances are a lot bigger there than here.

I don't think you can make it more efficient using php.
an easy fix would be to use  a cron job you run when your traffic is lowest (if that is ever)

anatak

[Nhoj]

I'll poke around a bit, the traffic is usually always high, not always, but usually... The ranking needs to be updated every time a users information changes and that happens an incredible amount of times per day...

[anatak]

This may be a stupid question
why do you want to update the table ?
you have the ranking already with the uNetWorth.

if you want to display the ranking why don't you use the

$query = mysql_query('SELECT `uID` FROM `users` ORDER BY `uNetWorth` DESC');

$i = 0;
while ($player = mysql_fetch_assoc($query)) {
$i++;
            echo "<tr><td>rank: ".$i."username: ".$player['uNAME']."</td></tr>".
}

you have to build a full table around if of course

[tcollie]

I agree with anatak for displaying your overall ranking listing on your rankings page.  Now the code below is what I use for displaying the user's rank on their stats page.  It's the same concept as yours, but I don't have a designated rank field like you.  I just use networth.  It does add a separate call to the db, but it works for me.

[code]
$query = "SELECT COUNT(*) as rank FROM users
        WHERE networth > (SELECT networth FROM users WHERE uid = '$uid')";
    $res = mysql_query($query);
    $stats['rank'] = mysql_result($res, 0) + 1;
[/code]
And the credit for that piece of code actually goes to Barand.  I needed that piece of code for something I was working on for my daughter's school, and it also worked out great for my game project.

[Nhoj]

I already do the $i loop for my rankings display, the only thing I wanted to use the ranking variable for is displaying someones stats.

Thanks ;D, now I can get rid of t all together