title: Slow Performance within While Loop when Updating MYSQL Table
body: <p>The following code runs incredibly slowly when performing a WHILE LOOP using data from table <code>product</code> and updating another table <code>stock_figures</code> within the same database. </p>
  
  <p>The code loops through each row in <code>product</code> taking the value from <code>product_id</code> and <code>wholesale_price</code> and then performs some calculations on the <code>product</code> table before updating the <code>stock_figures</code> table with the values. </p>
  
  <p>I'd be grateful of any suggestions which would improve the performance of my queries. </p>
  
  <p><strong>PHP WHILE LOOP</strong></p>
  
  <pre><code>&lt;?
  
  // 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];
  
  ?&gt;
  </code></pre>
  
  <p><strong>QUERIES WITHIN WHILE LOOP</strong></p>
  
  <pre><code>&lt;?
  
          $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());
  
  
  
  ?&gt;
  
  // close while loop
  &lt;? } ?&gt;
  </code></pre>
  
  <p><strong>UPDATED CODE</strong></p>
  
  <pre><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());
  </code></pre>
  
  <p>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. </p>
  
  <p><strong>Unless any further improvements can be suggested?</strong></p>