In earlier versions of Magento you may have noticed that saving products is horribly slow especially as your site grows.  This problem originates from a bug in MySQL 5 (bug #42259) where using “IN” can dramatically decrease performance.  This appears to have been corrected in Magento 1.7.x.  However, if upgrading isn’t an option for you right now, you might want to implement this custom fix.

The fix below should work for versions 1.5.x and earlier.  For version 1.6.x this function has been moved to /app/code/core/Mage/Sales/Model/Resource/Quote.php and modified to use prepared statements, but the general idea will remain the same.

  1. Create a local version of /app/code/core/Mage/Sales/Model/Mysql4/Quote.php
  2. Override the method markQuotesRecollectOnCatalogRules() and change the SQL statement to use an INNER JOIN instead of the first IN.

    Old…
    [php]public function markQuotesRecollectOnCatalogRules()
    {
    $this->_getWriteAdapter()->query("
    UPDATE {$this->getTable(‘sales/quote’)} SET trigger_recollect = 1
    WHERE entity_id IN (
    SELECT DISTINCT quote_id
    FROM {$this->getTable(‘sales/quote_item’)}
    WHERE product_id IN (SELECT DISTINCT product_id FROM {$this->getTable(‘catalogrule/rule_product_price’)})
    )"
    );
    }[/php]

    New…
    [php]public function markQuotesRecollectOnCatalogRules()
    {
    $this->_getWriteAdapter()->query("
    UPDATE {$this->getTable(‘sales/quote’)}
    INNER JOIN (
    SELECT DISTINCT quote_id
    FROM {$this->getTable(‘sales/quote_item’)}
    WHERE product_id IN (SELECT DISTINCT product_id FROM {$this->getTable(‘catalogrule/rule_product_price’)})
    ) i ON {$this->getTable(‘sales/quote’)}.entity_id = i.quote_id
    SET trigger_recollect = 1"
    );
    }[/php]

  3. Refresh your cache
0 0 votes
Article Rating
Subscribe
Notify of
guest
8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Bob
Bob
11 years ago

Indeed when a large number of goods or attributes need to be saved, backend works very slow, as each time you save data the system re-indexes the goods through the entire catalog. Asynchronous Re-indexing is a solution of this problem. When a product or category is saved it is not immediately re-indexed, but put into a queue. The queue is re-indexed in the background. This greatly speeds up the backend. This mechanism is implemented by using an extension of http://mirasvit.com/magento-extensions/magento-asynchronous-reindex.html. Even in case you place online shop even on a good hosting, sometimes the products, categories and attributes are saved… Read more »

Kevin
Kevin
11 years ago

Thanks Bob for link. Really amazing extension. I have over 95k and 100+ catalog rules, at now saving time of one product < 2s

Dave
11 years ago

There is one more solution for this problem http://magepro.org/extentions/catalog-database-accelerator.html
The extension is removing unnecessary Magento indexing and other processes on products that are not effected by the save. Thus this helps to reduce product save time.

Arnan
Arnan
11 years ago

So, after few test mirasvit reindexer works as expected. Amazing extension.

Olaf
Olaf
11 years ago

The join was still taking over 30 seconds to save on our site. Changed it to two queries and now the save takes 12 to 18 seconds

$entityIds = $this->_getReadAdapter()->fetchCol("SELECT DISTINCT quote_id
FROM {$this->getTable('sales/quote_item')}
JOIN {$this->getTable('catalogrule/rule_product_price')} ON ({$this->getTable('catalogrule/rule_product_price')}.product_id = {$this->getTable('sales/quote_item')}.product_id)");
if(count($entityIds) > 0) {
$this->_getWriteAdapter()->query("
UPDATE {$this->getTable('sales/quote')} SET trigger_recollect = 1
WHERE entity_id IN (?)", implode(',', $entityIds)
);
}

Kevin
Kevin
Reply to  Olaf
11 years ago

Hm, strange suggestion. It not works for me…

Michael Humphreys
11 years ago

This worked perfectly for us!
new products were taking between 30 and 90 seconds to save – now 5 seconds!

Thank you so much for increasing my productivity!

Shirtmen
7 years ago

Maybe report here as solution https://github.com/OpenMage/magento-lts