The following code runs incredibly slowly when performing a WHILE LOOP using data from table product and updating another table stock_figures within the same database.
The code loops through each row in product taking the value from product_id and wholesale_price and then performs some calculations on the product table before updating the stock_figures table with the values.
I'd be grateful of any suggestions which would improve the performance of my queries.
PHP WHILE LOOP
<?
// Retrieve data from database
$loop = " SELECT product_id, wholesale_price FROM product";
$query= mysql_query($loop);
while($rows=mysql_fetch_assoc($query))
{
$row = mysql_fetch_row($query);
$id = $row[0];
$price = $row[1];
?>
QUERIES WITHIN WHILE LOOP
<?
$bawtry_stock = "
SELECT product_id,
( kids_uk_j_105 + kids_c_17 + kids_c_18 + kids_c_19 + ... etc )
AS SUM FROM product WHERE product_id = '$id'";
$result_bawtry = mysql_query($bawtry_stock) or die (mysql_error());
$line = mysql_fetch_row($result_bawtry);
$bawtry = $line[1];
$chain_stock = "
SELECT product_id,
(quantity_c_size_26_chain + quantity_c_size_28_chain + quantity_c_size_30_chain +
... etc )
AS SUM FROM product WHERE product_id = '$id'";
$result_chain = mysql_query($chain_stock) or die (mysql_error());
$line = mysql_fetch_row($result_chain);
$chain = $line[1];
/*
* Declare the total value of all pairs from Bawtry, Chain
*/
$totalpairs = $chain + $bawtry;
/*
* Insert values for stock to write to databse
* Total stock for Bawtry, Chain
* Total value of stock for Bawtry, Chain
*
*/
$bawtry_value = (float)($bawtry * $price);
$chain_value = (float)($chain * $price);
$total_value = (float)($price * ($bawtry + $chain));
$sql2="
UPDATE stock_figures SET
bawtry_stock='$bawtry',
chain_stock='$chain',
totalstock='$totalpairs',
bawtry_value='$bawtry_value',
chain_value='$chain_value',
totalvalue='$total_value'
WHERE id='$id'";
$result2=mysql_query($sql2) or die (mysql_error());
?>
// close while loop
<? } ?>
UPDATED CODE
$sql = "SELECT product_id, wholesale_price,
(kids_uk_j_105 + kids_c_17 + kids_c_18 + kids_c_19 + kids_c_20 + kids_c_21 +
... )
AS bawtry,
(quantity_c_size_26_chain + quantity_c_size_28_chain + quantity_c_size_30_chain +
... )
AS chain from product";
$result = mysql_query($sql) or die (mysql_error());
while ($line=mysql_fetch_assoc($result))
{
$id = $line['product_id'];
$price = $line['wholesale_price'];
$bawtry = $line['bawtry'];
$chain = $line['chain'];
/*
* Declare the total value of all pairs from Bawtry, Chain
*/
$totalpairs = $chain + $bawtry;
/*
* Insert values for stock to write to database
* Total stock for Bawtry, Chain
* Total value of stock for Bawtry, Chain
*
*/
$bawtry_value = (float)($bawtry * $price);
$chain_value = (float)($chain * $price);
$total_value = (float)($price * ($bawtry + $chain));
$sql2="
UPDATE stock_figures SET
bawtry_stock='$bawtry',
chain_stock='$chain',
totalstock='$totalpairs',
bawtry_value='$bawtry_value',
chain_value='$chain_value',
totalvalue='$total_value'
WHERE id='$id'";
$result2=mysql_query($sql2) or die (mysql_error());
However, it's still taking an absolute age to complete. It seems to run really fast when I comment out the UPDATE statement at the end. Obviously this needs to remain in the code, so I'll probably run the whole thing as a cronjob.
Unless any further improvements can be suggested?
It seems you doing a lot of wasted selects.
You first select some data from table products, then for each row you select again from the same table. Twice. Then finally inserting this into another table, stock_figures.
And the only operation you are doing is adding lots of figures together.
All of this can be done in a single query.
select product_id,
whole_sale_price,
sum(kids_uk_j_105,
kids_c_17,
...) as bawtry,
sum(quantity_c_size_26_chain,
quantity_c_size_28_chain,
...) as chain
from products;
If this still is taking lots of time you need to check some server settings and also number of rows
Every write you make is a transaction and depending on your ACID-level it might be slow to do commits. Change innodb-flush-log-at-trx-commit to 2 will speed up writes.
You are doing a full table scan on products-table. I guess this is intended but if that table is big reading it will take a while, and writing all those rows back to stock_figures is going to take even longer.
Consider another approach. For each write (insert, update or delete) to products have a trigger update the corresponding row in stock_figures. Not only will it eliminate the batch job, it will also make stock_figures be correct at any given time.
The first thing is:
$row = mysql_fetch_row($query);
$id = $row[0];
$price = $row[1];
I don't know if it does work for you, but you already take $rows in your while condition so probably you should change it into:
$id = $rows['product_id'];
$price = $row['wholesale_price'];
Then the next 2 queries you can combine info:
SELECT product_id,
( kids_uk_j_105 + kids_c_17 + kids_c_18 + kids_c_19 + ... etc )
AS `SUM` FROM product WHERE product_id = '$id'
UNION ALL
SELECT product_id,
(quantity_c_size_26_chain + quantity_c_size_28_chain + quantity_c_size_30_chain +
... etc )
AS `SUM` FROM product WHERE product_id = '$id'
or even:
SELECT product_id,
( kids_uk_j_105 + kids_c_17 + kids_c_18 + kids_c_19 + ... etc )
AS `SUM1`,
(quantity_c_size_26_chain + quantity_c_size_28_chain + quantity_c_size_30_chain +
... etc )
AS `SUM2`
FROM product WHERE product_id = '$id'
because those 2 queries are run on the same table.
But in fact you can use just one query to get everything about your products as Andreas Wederbrand pointed in his answer.
But there are more problems:
You use old mysql_ function instead of mysqli_ or PDO and your code is vulnerable to SQL Injection
For each product you run 2 extra queries (select with union all if you go my way and update).
kids_uk_j_105, kids_c_17, kids_c_18 is not the best choice.I hope you have set key primary_id at product_id column as least.
When executing many SQL commands, parsing them takes some time. You can reduce this overhead by using http://php.net/manual/en/mysqli.quickstart.prepared-statements.php How much you gain, depends on case.
Prepared statements are also good for security reasons.
This answer does not void other answers here. Try to gain efficiency by reducing number of queries, analyzing their work, merging them if possible etc.