[SOLVED] Updating ranks on a table

[Bok]

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

[Wildbug]

Maybe.  Can you use the "INSERT INTO table SELECT ..." form?

 

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

[Bok]

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.

 

 

 

[Wildbug]

Well, if you're updating the statistics daily, it sounded to me like your statistics table is seperate from your data table, and the statistics are newly calculated each day.  So, I thought you might DELETE FROM data_table, then INSERT INTO statistics_table SELECT ..../* calculated stats from data table */....?

[Bok]

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.

[Bok]

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?

 

 

 

[Wildbug]

Looks like it's hanging on the UPDATE query.

 

Try:

UPDATE aaaaaah,temp SET aaaaaah.projrank=temp.rank WHERE aaaaaah.id=temp.name;

[Bok]

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)

 

 

[Wildbug]

Queries written with subqueries are often more inefficient, so it's often better to use the more direct method if one exists -- in this case the multi-table UPDATE.