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
