Below is a little PHP code snippet which works great(returns results quickly)
for single
queries. However when I put this code into a loop incrementing the order_index
and line,
my application can take as long as 2 minutes to return results. Is there any
other way
to execute this series of queries more effeciently to come up with
$order_line_due?
The only steps I have taken so far to decrease query time was to verify that
there were
indexes set on the "order_index" and "line" fields in each table queried.
mysql_select_db("orders");
$query = "select order_quantity ";
$query .= "from order_lines where (order_index = '$order_index')";
$query .= "and (line = $line)";
@extract(mysql_fetch_array(mysql_query($query)));
$linequery = "select sum(quantity) as already_received ";
$linequery .= " from receiving_lines ";
$linequery .= " where ( order_index = '$order_index' ) ";
$linequery .= " and ( line = $line ) ";
@extract(mysql_fetch_array(mysql_query($linequery)));
$linequery = "select sum(quantity) as adjust_receive ";
$linequery .= " from receiving_line_adjustments ";
$linequery .= " where ( order_index = '$order_index' ) ";
$linequery .= " and ( line = $line ) ";
@extract(mysql_fetch_array(mysql_query($linequery)));
$order_line_due = $order_quantity - $already_received + adjust_receive;
I hope the PHP code is not to hard for the MySQL gurus to decipher in order to
help!
James E Hicks III
Noland Company
2700 Warwick Blvd
Newport News, VA 23607
757-928-9000 ext 435
[EMAIL PROTECTED]
For the filter (mysql MySQL SQL table row insert select delete)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
