Too long execution time for large script

Anon

I have a script which is run every hour and it manipulates records in a database. However, as there are around 20,000 records in the table the script is taking much longer than 5 minutes to run.
Is there anyway which I could speed up this execution time?
thanks

Anon

What are you doing to the database? Is it indexed? Are you working with the index fields?

20,000 rows isn't very much - it shouldn't take that long to play with the,

Anon

thanks for the quick reply,
ive been trying to take parts of the code out of the update script and it looks like the section which is taking the most time is this one:

$result = mysql_query("SELECT x,y,z,score,size,rank FROM planet_dump");
while ($myrow = @mysql_fetch_array($result)){
$old_score = "".$myrow["score"]."";
$old_size = "".$myrow["size"]."";
$old_rank = "".$myrow["rank"]."";
$results = mysql_query("UPDATE planet_history set old_score='$oldscore', old_size='$old_size', old_rank='$old_rank' WHERE x='".$myrow["x"]."' AND y='".$myrow["y"]."' AND z='".$myrow["z"]."'");
}

Ive added an index to each of the selected fields yet the script still takes ages to run,
thanks.

Anon

Can we see your database structures?

Anon

Table structures are below:
#

Table structure for table `planet_history`

CREATE TABLE planet_history (
id int(11) NOT NULL auto_increment,
x int(11) NOT NULL default '0',
y int(11) NOT NULL default '0',
z int(11) NOT NULL default '0',
old_score int(11) NOT NULL default '0',
old_rank int(11) NOT NULL default '0',
old_size int(11) NOT NULL default '0',
KEY id (id),
KEY old_score (old_score),
KEY old_rank (old_rank),
KEY old_size (old_size)
) TYPE=MyISAM;

AND
CREATE TABLE planet_dump (
id int(11) NOT NULL auto_increment,
x int(11) NOT NULL default '0',
y int(11) NOT NULL default '0',
z int(11) NOT NULL default '0',
planetname text NOT NULL,
rulername text NOT NULL,
size int(11) NOT NULL default '0',
score int(11) NOT NULL default '0',
fullcoord varchar(20) NOT NULL default '',
rank int(11) NOT NULL default '0',
KEY id (id),
KEY x (x),
KEY y (y),
KEY z (z),
KEY size (size),
KEY score (score),
KEY rank (rank),
KEY id_2 (id)
) TYPE=MyISAM;

thanks

Anon

Okay, you're indexes are way, way off. You should have indexes on the fields you are using in your WHERE clause, not fields you are SELECTing.

x, y and z are in your WHERE for your UPDATE statement, with no index - which means MySQL is having to serial search through all your records on each iteration of your loop.

You need to index x, y and z (preferably together)!

Anon

Thanks for your help, this has given a good reduction in the execution time now.