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. Try increasing 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. See the link
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html for more
info on optimizing the sort.
BTW, I would definitely change the VarChar(10) to Char(10) because fixed
length records should be faster.
At 06:49 PM 2/7/2008, Phil wrote:
>I'm trying to write an update which generates ranking data for a table.
>Table is as follows
>CREATE TABLE `A` ( `id` INT NOT NULL ,
>`score` DOUBLE NOT NULL ,
>`projrank` INT NOT NULL ,
>`other` VARCHAR( 10 ) NOT NULL
>) ENGINE = MYISAM
>Real table actually contains 30 or so more fields but it gives a similar
>Score changes often, so 4 times per day I want to re-rank the data.
>Primary index is on score desc, id
>So I run the following
>update A set projrank=@rank:=rank+1 order by score desc,id
>For 20,000 rows the update takes 0.8 seconds
>For 50,000 rows it takes 1.9 seconds
>For 140,000 rows it takes ~ 5 seconds
>Scale up to 400,000 and it takes 7 minutes??
>I'm sure it's probably some setting in my.cnf but I've tweaking them to no
>avail. Currently I have them set as follows
>which is probably too high in places.
>Ideas? The full table actually has 1.7M rows in it which takes over an hour
>to update.. I've been puzzling on this for weeks now.
>Server is a 4core opteron 275 with 2Gb ram