We've recently upgraded to EE 1.14 (CE 1.9). The mysql triggers solve a lot of problems with our reindexing. However, we have several scripts that update thousands to millions of products at a time. You can imagine the trigger effect.
Is there a way to bypass those triggers while doing a bulk upload?
From magento support:
You can change these to Manual Update programmatically likeso:
$pCollection = Mage::getSingleton('index/indexer')->getProcessesCollection();
foreach ($pCollection as $process) {
$process->setMode(Mage_Index_Model_Process::MODE_MANUAL)->save();
//$process->setMode(Mage_Index_Model_Process::MODE_REAL_TIME)->save();
}
To change back to Update on Save just use the commented out line above and comment out the MODE_MANUAL line.
You can also update these manually from the commandline likeso:
php -f shell/indexer.php -- --mode-manual all
To switch back to update on save:
php -f shell/indexer.php -- --mode-realtime all
On the above commands please copy and paste them. Please be aware there is a space between 2 groups of 2 dashes in each command which can be confusing.
I've had some success setting the indexes to run only when scheduled. By doing that, I was able to run a mysql query that updated the prices.
The index settings are in the core_config_data table in the database. It's a 1/0 boolean with a path of index_management/index_options/%. Just set those to 0 before your script and set them back to 1 when you're done.
I strongly recommend avoiding this approach and would consider making sure you are only updating what is absolutely necessary. If there is a way to reduce inserts/updates to only what is substantial you may find the entries to the _cl table entirely necessary or justified and not having any impact. That being said:
There is no way to bypass the database triggers besides either modifying them to contain some conditional or by dropping them entirely (and recreating them when you are done).
I have had some success with modifying the import process to drop the triggers and re-create them after import by analyzing the triggers created by the install / upgrade scripts included by Magento.
You can see how they create [some of] them using Mview subscribers in:
app/code/core/Enterprise/Index/sql/enterprise_index_setup/install-1.13.0.0.php
If following this approach you have to make sure you are dropping and creating only the necessary triggers. There are some vitally important triggers in there that should remain in tact in order to preserve SEO link juice and etc. For example, the ones for the catalog_product_entity_url_key and catalog_category_product_url_key that insert into enterprise_url_rewrite_redirect. It also now becomes a pain to maintain because any changes that Magento will make to the triggers you will also have to keep an eye on and patch along side of Magento's patches :(
