Bok

Update as I think this must be memory settings somehow, though I've tweaked them without success.. I ran some tests on a smaller subset, and then tried it with varying numbers of rows to update. 435632 rows in both A and B, A is already ranked accordingly with it's auto_increment field. update B set rank=0 where id < 100000; (id is not necessarily sequential) Query OK, 51873 rows affected (0.68 sec) Rows matched: 51873 Changed: 51873 Warnings: 0 update A,B set B.rank=A.rank where B.id=A.id Query OK, 51873 rows affected (8.07 sec) Rows matched: 435632 Changed: 51873 Warnings: 0 update B set rank=0 where id < 200000; Query OK, 60829 rows affected (0.81 sec) Rows matched: 60829 Changed: 60829 Warnings: 0 update A,B set B.rank=A.rank where B.id=A.id Query OK, 60829 rows affected (43.47 sec) Rows matched: 435632 Changed: 60829 Warnings: 0 update B set rank=0 where id < 300000; Query OK, 74680 rows affected (2.85 sec) Rows matched: 74680 Changed: 74680 Warnings: 0 update A,B set B.rank=A.rank where B.id=A.id Query OK, 74680 rows affected (33.50 sec) Rows matched: 435632 Changed: 74680 Warnings: 0 update B set rank=0; Query OK, 435632 rows affected (16.46 sec) Rows matched: 435632 Changed: 435632 Warnings: 0 update A,B set B.rank=A.rank where B.id=A.id Query OK, 435632 rows affected (11 min 12.23 sec) Rows matched: 435632 Changed: 435632 Warnings: 0 So at some point, it's hitting some limit, anyone care to suggest which limit it is or how to find it ?

Table A is a temporary table used only to generate the ranks themselves via the auto_increment field..,sorry the int(7) was a mistake, it is int(11). I now believe the problem is on the particular box, though I've yet to figure out exactly what it is. I tried the same thing on another similar box and the update ran in 5mins (first time it was changing all 1.7M rows), second time it only updated ~ 300,000 and took 1.2mins. I'll run more tests today to see if there is a better way to generate these rankings.

I don't think so, table B which is being updated does not have an index on rank...

Can't, it's a changing target as scores change through the day..that's why I use a seperate table with the auto-increment first. I can do it one other way which is pretty fast, I just can't understand why this one is so slow !!

Explains show this mysql> explain select A.id,B.rank from B ,A where B.id = A.id; +----+-------------+--------------+--------+---------------+------+---------+------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+---------------+------+---------+------------------------+---------+-------------+ | 1 | SIMPLE | A | ALL | name | NULL | NULL | NULL | 1786054 | | | 1 | SIMPLE | B | eq_ref | name | name | 4 | dcfree.A.id | 1 | Using index | +----+-------------+--------------+--------+---------------+------+---------+------------------------+---------+-------------+ 2 rows in set (0.00 sec) using mk_query_profiler gives +----------------------------------------------------------+ | 1 (4090.6125 sec) | +----------------------------------------------------------+ __ Overall stats _______________________ Value _____________ Total elapsed time 4090.613 Questions 1 COMMIT 0 DELETE 0 DELETE MULTI 0 INSERT 0 INSERT SELECT 0 REPLACE 0 REPLACE SELECT 0 SELECT 0 UPDATE 0 UPDATE MULTI 1 Data into server 114 Data out of server 68 Optimizer cost 2150679.621 __ Table and index accesses ____________ Value _____________ Table locks acquired 2 Table scans 1 Join 0 Index range scans 0 Join without check 0 Join with check 0 Rows sorted 0 Range sorts 0 Merge passes 0 Table scans 0 Potential filesorts 0 Sorry, couldn't figure out how to change the font to make this look neater..

I don't believe so.. table A has two indexes 1 is on rank 2 is on id table B is indexed on id only Very puzzling..

Can anyone explain why this takes so long..?? The aim is to add ranks to Table B based on the score contained within it Given two tables A) rank int(11) PRI auto_increment id int(11) score double B) id int(11) PRI score double rank int(7) + 30 more fields.. Table B contains around 1.7M rows, A is initially empty I run insert into A (id,score) select id,score from B order by score desc,id; all well and good - takes about 1min, at this point table A contains the same number of rows ranked accordingly then update A,B set B.rank=A.rank where A.id = B.id; This takes over an hour... I'm kind of confused on this as it should be a straight match on the index right ? Thanks Bok

Try altering the WAIT_TIMEOUT value in your ini. I had a similar problem and pushing this value up to 3600 solved it.

Cool. Thanks for that! Any idea why? +-----------+ | curtime() | +-----------+ | 11:25:36 | +-----------+ 1 row in set (0.00 sec) ERROR 1061 (42000): Duplicate key name 'forstats' Query OK, 373261 rows affected (5.50 sec) Records: 373261 Duplicates: 0 Warnings: 0 Query OK, 296975 rows affected (3 min 55.44 sec) Rows matched: 373261 Changed: 296975 Warnings: 0 +-----------+ | curtime() | +-----------+ | 11:29:37 | +-----------+ 1 row in set (0.00 sec)

As gameid, date and popup are int's shouldn't you NOT have quotes around them ? I can't recall whether mysql cares during syntax.

So I tried the following CREATE TABLE `temp` ( `rank` int(11) NOT NULL auto_increment, `name` varchar(10) NOT NULL default '', PRIMARY KEY (`rank`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; ALTER TABLE `temp` ADD INDEX ( `name` ); select curtime(); alter table aaaaaah add index forstats (metric1 desc,id); insert into temp (name) select id from aaaaaah order by metric1 desc,id asc; update aaaaaah set projrank = (select rank from temp where temp.name = aaaaaah.id); select curtime(); But it never finishes.. +-----------+ | curtime() | +-----------+ | 10:30:39 | +-----------+ 1 row in set (0.00 sec) Query OK, 373261 rows affected (5.87 sec) Records: 373261 Duplicates: 0 Warnings: 0 Query OK, 373261 rows affected (4.60 sec) Records: 373261 Duplicates: 0 Warnings: 0 So it is hanging in the update statement. Machine is a dual xeon 3.6, 2Gb Ram, plenty of disk, nothing else running. I see no errors, nothing..just never finishes. mysqld is using 100% cpu. I left it for over an hour before finally killing it. Any ideas?

Not really. The updated information comes from xml files which are parsed. These update the main table, after which I need to run the ranking.

This is an update. I presume you mean insert into a temp table with an auto-increment values from the table, then update main table with subselect from temp table.. This is what I'm thinking of trying.

Using mysql version 5. I have a largish table containing statistics which is updated daily with various scores 1M+ rows Format is (it actually contains many more columns than this but I don't think they are relevant) Name Rank Score Every time it updates I need to update the ranks based on the scores. Is this possible to do via sql only? Currently I use an index on score to cache to an array before reading through an updating them all once again but it's time and memory consuming. Regards Bok