Home / Speed Up Queries / Question No: 49

My update query works slow. The sample Table is as follows:

CREATE TABLE `project` 
        ( `id` int(11) NOT NULL, `score` double NOT NULL, 
    `project_rank` int(11) NOT NULL, PRIMARY KEY (`id`) 
        ) 
ENGINE=MyISAM;

The table has 2M rows when I run the following query:

SET @rank:=0;
UPDATE project SET project_rank=@rank:=rank+1 ORDER BY score DESC, id;

The process takes long time to update. Any idea to speed up this query.

You may apply following tricks:
Check what happens if you drop the index when you do the update? Does it run faster? If so then the index is slowing you down. In this case you should increase the key_buffer_size to 756M.
If there isn't much difference, try the update without the "ORDER BY" clause. If it runs considerably faster, then you need to increase your sort_buffer_size. For more help on optimizing the sort, the see the link: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html.