I have a mysql table that has now 12 million records and I'm trying to update a column (rank) with the following UPDATE code:
SET @r=0; UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC;
Almost every minute new rows are inserted. So, after one hour, I have several rows not ranked and I need to update the whole table again.
However, it takes too much time. Is there any way to optimize or a different way to update the table faster? Maybe a different approach? Partitioning the table?