update row based on order in resultant table

Question

I'm writing an online math testing program, and currently working on the scripts to calculate the rank that each user got. The following code works, but I cringe every time I see it.

get_set() puts the result of the query into $users

function rank_users_in_test($tid){
  $GLOBALS['DB']->get_set($users,"select user,test from user_results where test=$tid order by points desc,time");
  // $users are already in order by rank thanks to ORDER BY
  $rank = 1;
  foreach ($users as $u){
    $GLOBALS['DB']->query("update user_results set world_rank=$rank where user={$u['user']} and test={$u['test']}");
    $rank++;
  }
}

The query in the loop makes me cry a bit. My question is, is there a way that MySQL can automatically update each user's rank based on the order they appeared in the result on the first query? There is a related question here, but it does not use UPDATE.

I'm using MySQL 5.

One of the principles of normalized databases is to not store calculated values. I suggest dropping the column from the user_results table and querying the existing data when you need it. — Dan Bracuk, Feb 16 '13 at 17:30
I know, but the site endures very heavy traffic the night the results are released. We had it such that results were just queried, but our servers froze up - too many connections. When the results are static, our servers can handle the load. The problem though is that the contest is getting bigger, and this code is reaching the 5min maximum execution time that our servers allow (I can't change that). — twentylemon, Feb 16 '13 at 17:34
Create a temp table with a `auto_increment` rank field, and `insert into mytemp(userid,points...) select userid,points from tests... order by points desc...` to have in one query all the ranks created — Déjà vu, Feb 16 '13 at 17:37

twentylemon · Answer 1 · 2013-02-16T18:06:55.280

Thanks to ring0 above, the following reduced the running time from minutes to mere seconds :D

create table temp (
  rank int auto_increment,
  user int,
  test int,
  primary key(rank)
);

insert into temp(user,test) (select user,test from user_results where test=$tid order by points desc,time);

update user_results ur, temp t set ur.world_rank=t.rank where ur.user=t.user and ur.test=t.test;

drop table temp;

update row based on order in resultant table

1 Answers1