75

In official documentation: https://devdocs.magento.com/guides/v2.3/extension-dev-guide/indexing.html there is the stement: `Allows tracking database changes for a certain entity (product, category and so on) and running change handler. Emulates the materialized view technology for MySQL using triggers and separate materialization process (provides executing ...


65

There are different kind of indexes in Magento. All of the indexers are there to make things run faster. I will cover here only a few of them. Flat Index There are 2 such indexes. One for categories and one for products. By default the category and product entities (and customers and customer addresses but they are not important in this situation) are EAV ...


36

You are running this command from ROOT and indexer.php is located under shell directory. So try this commands instead: php shell/indexer.php --reindex catalog_product_attribute For re-indexing all indexes : php -f shell/indexer.php reindexall Listing all commands : php shell/indexer.php --reindex catalog_product_attribute php shell/indexer.php --reindex ...


31

Those tables are related to MView from Magento 2. It's the indexer that runs in cron every minute by default. MView is used if you set the indexer mode to 'Index by Schedule'. Magento checks if the _cl tables exist, if not, are created every time the indexer/MView runs. The code related to these table creation is in \Magento\Framework\Mview\View::subscribe()...


28

A server timeout might causing Magento Admin Reindexing to stuck on "Processing" forever. What you need to do is to get SSH access for your server and depending on which reindex you got stuck, preform the following command lines. php yourmagentofolder/html/shell/indexer.php -reindex catalog_product_attribute php yourmagentofolder/html/shell/indexer.php -...


26

Yes, flat tables are still a thing. You can enable flat tables for products and categories from stores->configuration->catalog->storefront. Why do we need flat tables? They do offer significant performance boost for large catalog data. One of our client has only 5 categories and around 250 products so it doesn't really matter if you have flat on or off. It ...


24

Update on Save :The index is set to update whenever a change is saved. Update on Schedule :The index is set to update on schedule according to a cron job. On a small store,Update on Save is fine and will add very little load. If you have a larger store with multiple admins, working in the backend then your indexes could be getting triggered constantly. ...


21

You need to enable visible_on_front to push into the Catalog Flat tables. Category flat tables are affected by used_in_product_listing. As for the limit in MySQL I believe that is a separate question and should be migrated out as a separate Q&A.


18

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/...


15

You can fix this from the command line by reseting the index which will invalidate the state of the index and remove the processing flag from the index. php bin/magento indexer:reset Optionally you may also append the index name to the command to just just reset that index.


14

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::...


12

use for layered navigation. Filterable (with results) means that links will only appear for values where the number of results (the number in parentheses next to each value) is greater than zero. Filterable (no results) means that links will appear for all values, whether the number of results is zero or greater.


12

I found out, that the status is set in \Magento\Framework\Mview\View::suspend() and removed in \Magento\Framework\Mview\View::resume(). These methods are only called in \Magento\Indexer\Model\Indexer::reindexAll() to suspend the scheduled indexing before a forced full reindexing and to resume it afterwards or if an exception occurs. But if the process is ...


11

Can't take credit for this as it is taken from original post at: https://stackoverflow.com/questions/4945307/can-someone-explain-magentos-indexing-feature-in-detail Magento's indexing is only similar to database-level indexing in spirit. As Anton states, it is a process of denormalization to allow faster operation of a site. Let me try to explain some of ...


11

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 ...


11

DELETE FROM `catalog_product_entity_datetime` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`); DELETE FROM `catalog_product_entity_decimal` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`); DELETE FROM `catalog_product_entity_gallery` WHERE entity_id NOT IN (SELECT entity_id FROM `catalog_product_entity`); ...


11

OK I think I have the answer here. The issue is that Magento checks table status and doesn't find the key as an auto_increment value (even though it is.) If you run this: show table status; And scroll down to your offending table, you may find that the AUTO_INCREMENT value is set to null. That's what Magento means by "Table status incorrect". My fix was ...


10

Just a guess, but it sounds like Your command line php executable uses a different php.ini file than the web server The php.ini file your command line version uses has a different, and invalid, value set for the mysql.default_socket ini setting When you specify a MySQL server host as localhost, you're telling the system to use this socket file to connect ...


9

Here you go $process = Mage::getSingleton('index/indexer')->getProcessByCode('catalog_product_flat'); //the ID is 4 $process->reindexEverything(); Then you should be all set


9

After lot of debugging, i found the sound code which does reindex product by product id. Here is the way - require_once('app/Mage.php'); umask(0); Mage::app(); // Get products $productsIds = array('11','222','3333'); // product id array // Reindex category/product association $event = Mage::getModel('index/event'); $event->setNewData(array( '...


8

I was struggling with the same issue, and got a clean solution to it. Mage_Index_Model_Indexer_Abstract has an inbuilt function called isVisible() If this function returns false, then the indexer is disabled completely. So you can extend class Mage_CatalogInventory_Model_Indexer_Stock and then add isVisible() function to it which returns false (hardcode /...


8

Reference from Magento document is already here so I'm skipping that part. Magento implemented materialized view in 2.0 which tracks changes for all indexers. Each indexer has a _cl table which gets entity_id and a auto_increment version_id from triggers added on main tables. When cron job executes, indexer gets last version_id for each view from mview_state ...


8

Based on magento documentation for flat table mentioned in https://docs.magento.com/m2/ce/user_guide/catalog/catalog-flat.html Starting with Magento 2.3.0+, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues


8

Yes, it must be faster than Magento v1.x (by at least 20%). Improved scalability consists of improved indexers, new Magento performance toolkit, Varnish Cache, Full page caching, What is Magento 2 index Indexing is how Magento transforms data such as products, categories, and so on, to improve the performance of your storefront. As data changes, the ...


7

There is a cron called "enterprise_index_clean_changelog" to handle the cleaning of those tables. It is configured via the admin at System -> Configuration -> Advanced -> Index Management -> Index Clean Schedule. From there you can enable/disable it and adjust the start time and frequency.


7

it seams that you have zombie data in the flat table for store with id 1. The prepareFlatTable method is trying to add a Foreign Key to the catalog_product_entity table and that fails. Most probably because you already have in the catalog_product_flat_store_1 table a product id that is not in the catalog_product_entity table. First backup your database (...


7

Update on save will fire some (indexing related) events after a product is saved, so a full reindex won't be necessary after product updates. Manual mode won't fire anything, so product updates will run faster, but you'll need to reindex everything in order to see some changes in frontend Note that "product updates" does mean some frontend processes too, ...


7

The mview.xml is used along with indexer.xml to setup indexers. The mview.xml file declares: indexer view ID indexer class the database tables the indexer tracks what column data is sent to the indexer The indexer.xml file declares: indexer ID indexer class name indexer title indexer description indexer view ID You can find more information about custom ...


7

After much travail, I found a solution using environment variables, which I know isn't what you wanted. You can set the parameters using environment variables like this (replacing $APP_ROOT with your actual Magento root): env \ "MAGE_DIRS[base][path]=$APP_ROOT" \ "MAGE_DIRS[pub][uri]=" \ "MAGE_DIRS[media][uri]=media" \ "MAGE_DIRS[static][uri]...


7

catalog_category_product_index_tmp is used to store intermediate data for a batch product in the process of indexation *_replica tables are used for full indexation process to not affect the Store Front in process of indexation *_store* tables added in 2.2.5 to split index per store and table catalog_category_product_index become outdated


Only top voted, non community-wiki answers of a minimum length are eligible