Query Optimization

[dai.hop]

Good morning all,

I have the following query:

UPDATE r_lines, r_orders SET r_lines.order_id = r_orders.id WHERE r_lines.order_id = r_orders.transaction_id

The aim of this query is to replace the transaction id's in the r_lines.order_id field with the order id's in r_orders.id.

When I execute this query (via phpMyAdmin) I always get an "Internal Server Error" page after 30 seconds or so. I'm of the opinion that the query is inefficent in some way and is overloading the server.

Table stats:

r_orders
Rows: 16,781

r_lines
Rows: 31,926

Can anyone tell me why the query is causing this error or better still, suggestion an amendment to allow it to run normally?!

Thanks,

dai.hop

[Phill Pafford]

Hmm, well if it's the execution time that's causing the error you could add another condition to break up the UPDATES


UPDATE `r_lines`, `r_orders`
SET `r_lines`.`order_id` = `r_orders`.`id`
WHERE `r_lines`.`order_id` = `r_orders`.`transaction_id`
AND `r_orders`.`transaction_id` BETWEEN 0 AND 10000


Then you would have to adjust your query to run from 10000 to 20000 and so on until the update is complete.

Unfortunately the EXPLAIN command doesn't work for UPDATE queries yet :(

Hope this helps

[NogDog]

Could be a PHP issue, as the default max execution time setting is 30 seconds. If you have access to the PHP.ini file, you could change the value for max_execution_time (http://us.php.net/manual/en/info.configuration.php#ini.max-execution-time). Otherwise if you can modify the phpMyAdmin code you could add a call to set_time_limit (http://us.php.net/manual/en/function.set-time-limit.php)() to change it at the script level.

[dai.hop]

Thanks for your answers.

I eventually solved the problem by assigning an index to the r_lines.order_id field. This allowed the query to complete in approximately 1.2 seconds!

[NogDog]

Thanks for your answers.

I eventually solved the problem by assigning an index to the r_lines.order_id field. This allowed the query to complete in approximately 1.2 seconds!

Or you could do that. :D :cool:

[sknake]

This may be a hint that its time for a database performance audit ;)