too long to process?!

toshog

hi all..

can somebody please explain why this code took 38 minutes at 98% CPU usage to finish. uszip_codes has about 42 000 records with 3 fields and us_theaters - about 3400 with 7 fields...

$query = 'select * from uszip_codes';
$result = mysql_query($query);
$num=0;

while ($myrow = mysql_fetch_array($result)) {
	changeZip($myrow['zip'],$myrow['latitude'],$myrow['longitude']);
	$num++;
	} 
function changeZip($zip,$lat,$lon) {

$q = 'update us_theatres set latitude='.$lat.', longitude='.$lon.' where zip='.$zip.';';
$r = mysql_query($q);


}

thanks...

chyan

i have no idea why your code could take up to 38 minutes
i had a database with 100000 more record and sometimes i've using all the
data for update and displaying and it never take even just 1 minute

but i've see nothing strange with your code thats why i have no idea
what the problem cause that

btw : how much max_execution_time is set on your server? because commonly
web hosting server just give 30 sec for that,so why your script can take up to 38 minutes
without an error massage

toshog

after the browser expired - about 60 sec - i executed the script on the command line...

what is weired is that the actual locator function that goes through all of the 42 000 records all the time doesn't get even half a second...

i would still like to know thought....

Weedpacket

One way to save time and processing power would be to do the work in SQL instead of PHP.

update
	us_theatres,uszip_codes
set
	us_theatres.latitude=uszip_codes.latitude,
	us_theatres.longitude=uszip_codes.longitude
where
	us_theatres.zip=uszip_codes.zip

toshog

i was looking for a documentation on how to write those....

where on the mysql site is it?

i still think that the php script should be way faster then 38 min....

tsinka

Hi,

do you have an index on the zip column in us_theatres ?

Create one if not. That should speed up things a lot.

Thomas

Weedpacket

Originally posted by toshog
i was looking for a documentation on how to write those....

where on the mysql site is it?

Not using MySQL myself I wouldn't know where offhand, but I presume that the manual has a section on the UPDATE command.

toshog

right. and i've also had it for a couple of years on my desktop. but i couldn't find scripting tutorials on the mysql site...

Weedpacket

Originally posted by toshog
right. and i've also had it for a couple of years on my desktop. but i couldn't find scripting tutorials on the mysql site...

Does it have a reference section for you to refer to? Does it have an index?

Oh, all right. I'll look it up for you. Hang on, let me just find the online version, first...(clock starts:now - but my traffic load is pretty high at the moment so you'll have to bear with me)
http://www.mysql.com/ ....
Products: http://www.mysql.com/products/ ....
Database server: http://www.mysql.com/products/mysql/ ....
Online Documentation: http://dev.mysql.com/doc/mysql/en/index.html

2 minutes 36 seconds and I'm looking at the manual.

14.SQL Statement Syntax: http://dev.mysql.com/doc/mysql/en/SQL_Syntax.html

14.1.10 Update Syntax: http://dev.mysql.com/doc/mysql/en/UPDATE.html

4 minutes 38 seconds. Second paragraph.

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

5 minutes 3 seconds, a bit further down:

Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

A minute later I found this in the user-contributed notes (which I guess aren't in your desktop copy):

Posted by Adam Boyle on March 2 2004 4:28pm

It took me a few minutes to figure this out, but the syntax for UPDATING ONE TABLE ONLY using a relationship between two tables in MySQL 4.0 is actually quite simple:

update t1, t2 set t1.field = t2.value where t1.this = t2.that;

What were you looking for, again?