更新MYSQL表时While循环内的性能降低

Question

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?

Answer 1

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.