Series caching in MySQL?

Question

I have a column that I want to sort with periodic updates in rank (daily). I am currently using this in code

get all rows from table order by column

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

every row in MySQL requires an update. Are there better ways to do this?

Answer 1

Use update with connection:

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 you expect that you will have many rows, you will get maximum performance by joining with an indexed table, for example:

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 can do it faster by completely skipping the update step and replacing it 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;