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`) 

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

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;

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.

Posted in: MySQL

Related FAQ's

Marius Ion ANGEL HOT SOFT LLC (800) 316-7677