Caching row ranks in MySQL?

Question

I have a column I want to sort by, with periodical updates on the rank (daily). I currently use this in code

get all rows from table order by column

rank = 1
foreach row in table
   update row's rank to rank
   rank++

this takes an update for each row in MySQL. Are there more efficient ways to do this?

Answer 1

Use an update with a join:

set @rank := 0;

update tbl a join
  (select id, @rank := @rank + 1 as new_rank from tbl order by col) b
  on a.id = b.id set a.rank = b.new_rank;

If expecting to have a lot of rows, you'll get the best performance by doing the join against a table that is indexed, e.g.:

set @rank := 0;

create temporary table tmp (id int primary key, rank int)
  select id, @rank := @rank + 1 as rank from tbl order by col;

update tbl join tmp on tbl.id = tmp.id set tbl.rank = tmp.rank;

Finally, you could potentially make it faster by skipping the update step entirely and swapping in a new table (not always feasible):

set @rank := 0;

create table new_tbl (id int primary key, rank int, col char(10),
  col2 char(20)) select id, @rank := @rank + 1 as rank, col, col2
  from tbl order by col;

drop table tbl;
rename table new_tbl to tbl;