Help speeding up MySql Query

How would I speed up this mysql query?

   SET @rank = 0;  

  UPDATE dbTable 
     SET rank_fd = @rank := @rank + 1 
ORDER BY fd2 DESC, fd3 DESC;

This query updates ~300,000 table rows. It just takes way too long.... Simple control panel (turbo panel?) says mysqld is only using 8-9% cpu. I know this is always gonna be true, but this query doesn't take nearly as long on my Core i7 machine. I think it takes about 5-10 minutes on my P4 dedicated server... that wouldn't be a problem except I have to do it more than once for different values. It also makes other mysql operations extremely slow (almost hanging), which affects website performances drastically.

Software:

• CentOS release 5.4 (Final),
• Simple Control Panel version 1.4.8,
• PHP 5.2.16
• MySQL client version: 5.1.52

Hardware:

• Intel Pentium 4 CPU 3.0GHz
• 2GB Ram

MORE INFO (ADDED 3/6/2011 @ 10:06PM CST):

//Core i7 920 @ 2.6GHz, 6GB Ram
UPDATE dbTable SET rank_fd =999999999;
#275037 row(s) affected. ( Query took 7.0708 sec )

SET @rank =0;
#Your SQL query has been executed successfully ( Query took 0.0003 sec )

UPDATE dbTable SET rank_fd = @rank := @rank +1 ORDER BY fd2 DESC, fd3 DESC ;
#275037 row(s) affec开发者_开发百科ted. ( Query took 9.9931 sec )



//P4 3.0GHz, 2GB Ram
UPDATE dbTable SET rank_fd =999999999;
#Affected rows: 291468 (Query took 8.2165 sec)

SET @rank =0;
#Your SQL query has been executed successfully (Query took 0.0002 sec)

UPDATE dbTable SET rank_fd = @rank := @rank +1 ORDER BY fd2 DESC, fd3 DESC ;
#Affected rows: 291469 (Query took 305.2104 sec)  

MORE INFO (ADDED 3/7/2011 @ 6:37PM CST):

I've got some new info. If I do a select statement on the P4:

SET @rank =0;
SELECT @rank := @rank +1 AS rank_fd FROM dbTable ORDER BY fd2 DESC, fd3 DESC LIMIT 0, 300000;  
#Showing rows 0 - 29 (292,437 total, Query took 3.0448 sec)

It only takes 3 seconds to calculate everything. The no-calc batch update statement only takes 8 seconds. What is all the extra work being done that causes it fo go above 300 seconds with my original statement. Is there a way to catch update after the select calc statement that doesn't involve PHP. I only say this because if I loop this in PHP, it's gonna take much longer than the original statement.

Thanks for all the help so far!!!

You're modifying every row in the table, of course it will be slow. Depending on how rank_fd is used, you may be able to get away with just putting indexes on fd2 and fd3 and calculating the rank at runtime.

You could use a stored procedure and temp tables to speed up your ranking process.

The following example uses a player score table with 1.5 million rows with 500K player scores over 3 rounds (500K * 3) and updates the rankings for round 1 (500K rows) in approx. 5 seconds.

Hope this helps :)

Example table and stored procedure

drop table if exists player_scores;
create table player_scores
(
round_id smallint unsigned not null,
player_id int unsigned not null,
score_1 int unsigned not null default 0,
score_2 int unsigned not null default 0,
rank int unsigned not null default 0,
primary key (round_id, player_id)
)
engine=myisam;

drop procedure if exists update_player_score_ranking;

delimiter #
create procedure update_player_score_ranking
(
p_round_id smallint unsigned
)
begin

  create table tmp_player_scores engine=memory select 
    round_id, player_id, score_1, score_2, @rank:= @rank + 1 as rank
  from 
    player_scores 
  inner join (select @rank:=0) r
  where
   round_id = p_round_id
  order by 
    score_1 desc, score_2 desc;

  delete from player_scores where round_id = p_round_id;
  insert into player_scores select * from tmp_player_scores;

  drop table if exists tmp_player_scores;

end #

delimiter ;

Test results:

select count(*) from player_scores;
+----------+
| count(*) |
+----------+
|  1500000 |
+----------+
1 row in set (0.00 sec)

select count(*) from player_scores where round_id = 1;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.07 sec)

select * from player_scores where round_id = 1 order by score_1 desc, score_2 desc limit 5;
+----------+-----------+---------+---------+------+
| round_id | player_id | score_1 | score_2 | rank |
+----------+-----------+---------+---------+------+
|        1 |    456937 |   65534 |   49579 |    0 |
|        1 |     72439 |   65534 |   44537 |    0 |
|        1 |     16427 |   65534 |   43045 |    0 |
|        1 |    259871 |   65534 |   32095 |    0 |
|        1 |    324702 |   65534 |   15227 |    0 |
+----------+-----------+---------+---------+------+
5 rows in set (0.71 sec)


call update_player_score_ranking(1);
Query OK, 0 rows affected (5.57 sec)

select * from player_scores where round_id = 1 order by rank limit 5;
+----------+-----------+---------+---------+------+
| round_id | player_id | score_1 | score_2 | rank |
+----------+-----------+---------+---------+------+
|        1 |    456937 |   65534 |   49579 |    1 |
|        1 |     72439 |   65534 |   44537 |    2 |
|        1 |     16427 |   65534 |   43045 |    3 |
|        1 |    259871 |   65534 |   32095 |    4 |
|        1 |    324702 |   65534 |   15227 |    5 |
+----------+-----------+---------+---------+------+
5 rows in set (1.29 sec)