Php Fastest way to iterate through an array and update mysql?

Question

I wrote a product price/stock update script for Magento. I load the csv into an array and then iterate through it. The current code takes around 10 minutes to complete for 5,000 products, is there a faster way to do this? I've already bypassed Magento's API as that was extremely slow and switched to updating the database directly since its not many tables and its faster. Using timers to record the time, it takes about 10 minutes for the foreach loop and two minutes for the reindexALL

$con = mysql_connect("localhost","root","");
$selected = mysql_select_db("magento",$con);

$processes = Mage::getSingleton('index/indexer')->getProcessesCollection();
$processes->walk('setMode', array(Mage_Index_Model_Process::MODE_MANUAL));
$processes->walk('save');

foreach($all_rows as $final)
{

$sql = mysql_query("SELECT entity_id from catalog_product_entity where sku = '".$final[ITEM]."'");

if ($row = mysql_fetch_array($sql)) {

//update price
$pricenew = $final['PRICE'] + ($final['PRICE']*.30);
mysql_query("UPDATE catalog_product_entity_decimal SET value = '$pricenew' where attribute_id = 75 AND entity_id = '".$row[entity_id]."' ");

//update retail price
$retailprice = $final['RETAIL'];
mysql_query("UPDATE catalog_product_entity_decimal SET value = '$retailprice' where attribute_id = 120 AND entity_id = '".$row[entity_id]."' ");

//update stock quantity and is in stock
$stockquantity = $final['QTY'];
$stockquantity = number_format($stockquantity, 4, '.', '');
mysql_query("UPDATE cataloginventory_stock_item SET qty = '$stockquantity', SET is_in_stock = 1 where product_id = '".$row[entity_id]."' ");

}

$processes->walk('reindexAll');
$processes->walk('setMode', array(Mage_Index_Model_Process::MODE_REAL_TIME));
$processes->walk('save');

mysql_close($con);

Not an immediate answer to your question, but don't use `mysql_*`, it's begun the deprecation process. Look into `PDO` or `mysqli_*` instead — Elias Van Ootegem, Sep 24 '12 at 07:54
The products are already in the database though, I just need to update the price/stock from a csv on a daily basis. Also price and stock are in two different tables. Would it still be best to use LOAD DATA INFILE for that? — user1155594, Sep 24 '12 at 08:01

Bud Damyanov · Answer 1 · 2012-09-24T08:05:31.983

If your table catalog_product_entity_decimal has index, that covers id (obviously it is) - then you have no other ways to speed it up. Since the slowest thing here is physical changing of the value. Probably you can put a WHERE clause to to avoid of updating the price to the same value.

Other thoughts: While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

UPDATE t
SET c1 = ‘x’, c2 = ‘y’, c3 = 100
WHERE c1 = ‘x’
AND d = CURDATE()

You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

EXPLAIN SELECT c1, c2, c3 FROM  t WHERE c1 = ‘x’ AND    d = CURDATE()

You should now apply the same principles as you would when optimizing SELECT statements.

Php Fastest way to iterate through an array and update mysql?

1 Answers1