I have a shop and all the time ALL indexes are invalid. I noticed, that I have no clue, when an index is invalidated.
Can you give me a list of "all" events, which make one or more of these indexes to get invalid?
Doing a grep on the code directory will get you a list of files that triggers an invalidation.
grep -Ri '::STATUS_REQUIRE_REINDEX' .
The following core files trigger an invalidation
./core/Mage/CatalogSearch/Model/Indexer/Fulltext.php
./core/Mage/Catalog/Model/Product/Indexer/Flat.php
./core/Mage/Catalog/Model/Product/Indexer/Price.php
./core/Mage/Catalog/Model/Category/Indexer/Flat.php
./core/Mage/Catalog/Model/Category/Indexer/Product.php
./core/Mage/Catalog/Model/System/Config/Backend/Catalog/Product/Flat.php
./core/Mage/Catalog/Model/System/Config/Backend/Catalog/Category/Flat.php
./core/Mage/Catalog/Model/Indexer/Url.php
./core/Mage/Backup/Helper/Data.php
./core/Mage/CatalogInventory/Model/Indexer/Stock.php
./core/Mage/ImportExport/Model/Import.php
Basic events in the Magento CE core are
Product Attributes
specific save attribute (is part of flat product), store, store_group if flat catalog product is enabled
Product Prices
checking configuration settings save (like price scope). or product save, website create / delete
Catalog URL Rewrites
no specific invalidation
Product Flat Data
Specific save attribute (is part of flat product), if flat catalog product is enabled, After enable flat product
Category Flat Data
Check is flat catalog category is enabled and specific save category, After enable flat category
Category Products
check is flat catalog category is enabled and specific save category
Catalog Search Index
specific save attribute (is part of searchable attributes), store, store_group if flat catalog product is enabled
Tag Aggregation Data
Never invalidated except general conditions mentioned below
Stock Status
specific System > Config settings in inventory tab save, show out of stock products for example
All of them are invalidated after a rollback on the backup from System > Tools > Backup and creating, deleting or moving around websites, stores and storeviews
After running dataflow imports for product the following is invalidated: catalog_product_price, catalog_category_product, catalogsearch_fulltext, catalog_product_flat
Several indexers like flat data and the URL indexer seem to also be invalidated by saving core_config_data values.
Maybe it's an idea to temporarily create a rewrite for this site on
Mage_Index_Model_Resource_Process
Then do something like:
<?php
class YourNamespace_YourModule_Model_Resource_Process
extends Mage_Index_Model_Resource_Process
{
public function updateStatus($process, $status)
{
if ($status === Mage_Index_Model_Process::STATUS_REQUIRE_REINDEX) {
Mage::log(sprintf('Indexer %s was invalidated.', $process->getIndexer()->getName()), null, 'invalid_index.log', true);
foreach (debug_backtrace() as $db) {
Mage::log(sprintf('%s::%s', $db['class'], $db['function']), null, 'invalid_index.log', true);
}
}
return parent::updateStatus($process, $status);
}
}
That should easily pinpoint when an indexer gets invalidated and the call trace that was responsible for it.
Since others have already answered your questions specifics. I thought it may be better to explain why indexing is needed and how it relates to Magento and the relationship to modern databases.
Index: an alphabetical list of names, subjects, etc., with references to the places where they occur, typically found at the end of a book.
So what exactly is an Index in terms of Databases?
An index is a data structure that sorts a number of records on one or more fields, and speeds up data retrieval. This is to avoid scanning through the disk blocks that a table spans, when searching through the database.
And what Indexing is in terms of Magento? The by-product of EAV (Entity Attribute Value) AKA a database within a database. With multiple lookup tables, gathering all attributes flagged as indexed to be combined into one flat table of all lookup tables, for faster queries and less I/O, and CPU cycles.
I recall a mention that when Magento was initially being developed, flexibility was high on the priority list, which is understandable why they chose to go with the EAV data model. Ultimately however the cost for such flexibility came at a cost to performance and it has plagued Magento from the start.
In general, the Magento Engineers were tasked, first and foremost, with building the most flexible, customizable system possible, and worry about performance later. Why is Magento So Slow?
EAV is great for data warehousing but terrible for transactional. So why do we need indexes to begin with? Since the same approach of the relational model has been reimplemented, Magento now has to handle all of the things MySQL does itself internally. Some things to consider, such as Indexes already existing in MySQL tables. With that in mind also, consider the EAV data model now:
The same has to be reimplemented, which is very "anti-pattern" IMO.
Also, this the same reason you find var/locks which the indexer uses to lock the indexing process. Same reasons databases have row/table locking.
Now when a record, say a product value has been changed the flat table or index (as what MySQL would refer to it as) has to be updated to be reflected for queries upon the newly changed data to be found quickly and efficiently without scanning through numerous records. The flat tables exist as it used in the very same reason MySQL has them, without such an index (like a book) it requires a full table scan to retrieve the record. This means massive amounts of I/O for both Disk and Memory as well as CPU cycles to locate the requested data, which is very bad for performance.
Since Magento uses the EAV data model, there are numerous lookup tables that have to be scanned to piece all of the data together to locate the data that's been requested. This is what happens if you disable the Flat catalogs. Just like the MySQL, scanning for the record vs using an index (flat table) to be utilized to locate the record quickly while preserving precious I/O Cycles. Creating a table and not adding any indexes is the same as not using the flat tables in magento. While these two scenarios can work well in different scenarios, See Ben at Sonassi's very good answer to this question. (Hint it involves understanding the scope of data.)
While it isn't a direct answer to your question, understanding the moving parts and being better prepared for them should help alleviate some of the headaches that come with indexing. "Treat the problem rather than the symptom."
Exploring more into the internals of modern database systems can help with better understanding of how and why Indexing is necessary and how it relates (somewhat) to Magento's Indexing as well.
To summarize: Understand your problem scopes before blindly applying solutions. As not every bit of data will be exactly the same and planning and implementing solutions AFTER you've got a good/full understanding of the problem. Database optimization can be very rewarding for change management. Such as preventing the dreaded DEADLOCKS.
You may also want to consider setting all of your indexers to Manual and setup alternative processes to rebuild the index at off-peak time hours (when admins are away). Only Product Prices and Stock Status should be set to Update on Save.
Now consider how indexing works from a technical point of view. The main module is responsible for indexing Mage_Index. Basic models of the indexer: Indexer, Process, Event.
Mage_Index_Model_Indexer is the indexer, all interactions with other modules module Mage_Index occur through this service. It contains the following methods:
processEntityAction() Creates and registers the event and starts the indexing processlogEvent() Creates an event and register it for subsequent indexing;indexEvent() Runs the indexing events;getProcessesCollection() Returns the collection of all the processes such as Product Attributes, Product Prices, Catalog Url Rewrites, etc.
Usually after changing the essence, such as the method _afterSave or _afterCommit we perform partial re-index.The Mage_Index_Model_Process or process is the essence of your indexer that stores the status, the last run operation. All processes are stored in the table index_process. The program has a method getIndexer() which returns the index of the model. Most of the tasks delegated by the process of the index model.
Mage_Index_Model_Event stores information about the event that occurred. For example, we store the product and after saving, we create a new event and store information about what type of entity we just saved what id have the spirit and what action we performed for this substance.
A general list of when invalidation occurs:
Any resource model with registered index in the module's config.xml, upon save of the transaction. afterCommitCallback() is called with a prefix. This is where index events are logged, since it is at the end of a successful transaction.
...and It makes me sad that EAV is still around in Magento 2. :(
References:
