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 [EMAIL PROTECTED]:=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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
