Phil,
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.
Mike
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
>issue
>
>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
>
>set @rank:=0;
>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
>
>key_buffer_size=256M
>max_allowed_packet=16M
>thread_stack=128K
>thread_cache_size=8
>sort_buffer_size=48M
>join_buffer_size=3M
>read_buffer_size=4M
>query_cache_size=4M
>query_cache_limit=4M
>table_cache=100
>max_connections=20
>max_heap_table_size=64M
>myisam_sort_buffer_size=64M
>
>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
>
>Thanks
