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
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:
- Entity = Table
- Attribute = Field
- Value = Value
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
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:
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 process
logEvent() 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
_afterCommit we perform partial re-index.
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:
- catalog / product (SAVE, DELETE, MASS_ACTION)
- catalog / category (SAVE, DELETE)
- catalog / resource_eav_attribute (SAVE, DELETE)
- customer / group (SAVE)
- cataloginventory / stock_item (SAVE)
- tag / tag (SAVE)
- core / store (SAVE, DELETE)
- core / store_group (SAVE, DELETE)
- core / website (SAVE, DELETE)
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. :(