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.
- Create a local version of /app/code/core/Mage/Sales/Model/Mysql4/Quote.php
- 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] - Refresh your cache
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 »
Thanks Bob for link. Really amazing extension. I have over 95k and 100+ catalog rules, at now saving time of one product < 2s
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.
So, after few test mirasvit reindexer works as expected. Amazing extension.
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)
);
}
Hm, strange suggestion. It not works for me…
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!
Maybe report here as solution https://github.com/OpenMage/magento-lts