Magento2.2.5 – Role and Functionality of Indexing Tables Explained

indexerindexingmagento2

I've read the documentation about indexing in Magento 2, but it's very much on the surface. What I am wondering is how do the internals of the indexing mechanism work. Can somebody explain this to me? If I look for example at the Category/Product relation I can determine the following:

The following tables are involved in my setup:

catalog_category_product
catalog_category_product_cl
catalog_category_product_index
catalog_category_product_index_replica
catalog_category_product_index_store1
catalog_category_product_index_store1_replica
catalog_category_product_index_store2
catalog_category_product_index_store2_replica
catalog_category_product_index_tmp

So far, I understand the following about the indexing process:

Category/Product relations are stored in catalog_category_product.
Whenever the is a change to this relation, a new version is pushed to the changelog (catalog_category_product_cl) to be picked up by the Materialized View implementation by comparing versions in the mview_state-table.
catalog_category_product_index_tmp is populated during indexing and swapped at the end the process with catalog_category_product_index.

But here are my questions I still have left:

What is the purpose of the _replica-table?
Why are there indexes per store even though the catalog_category_product_index has a store_id-table?
Not sure if this is a bug, but why are there differences between the products in catalog_category_product_index::store_id=1 and catalog_category_product_index_store1? To clarify: In catalog_category_product_index I have a product that is assigned to 6 category ID's, whereas the same product in the dedicated _store1-index is only assigned to 4 category ID's. Could be a bug though.

Any help/explanation from core Magento developers or more experienced Magento developers is appreciated.

Best Answer

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

Related Solutions

Magento – Products occasionally disappear

60,000 products * 40 store views = 2,400,000 products

I'm honestly not surprised you've got catalogue consistency issues. Magento doesn't scale well at all with large catalogues combined with large numbers of store views.

Your issue is almost entirely caused by an index not being complete, so your theories of 1, 1a and 1b are all correct.

We've seen this on a LOT of stores where the catalogues are large and re-indexing is performed.

The fixes for most of these stores was to optimise the indexing process for each index. We swapped MySQL out for SOLR - which gives an almost immediate reduction in population time. There are some other aspects to SOLR that serve to benefit you, such as the index not being dropped entirely until the previous process is committed; and that updates again don't remove data until committed.

Although, with 2.4 Million products - you're still going to struggle, unless you make some significant application-level changes.

My advice would be to take a proper look at why you are re-indexing, and at what frequency - and try and find some alternatives to this process that can perform in real-time.

I think you'll struggle to get a definitive answer on SE because of how tailored the answer needs to be to your store/business.

Database – Rebuild Index Taking Forever in Magento

Try indexing from the command line / ssh. Your VPS should have that functionality.

Execute the following from your /magento_installation/shell

Verifying the status if the indexes:

php indexer.php --status

To reindex the problem index, in your case the Product Flat Data

php indexer.php --reindex catalog_product_flat

Additional information: How to process Magento indexes from the command line

If an error presents itself at that time, there may be a problem with your mysql configuration or with the database itself. Feel free to post the relevant information.

UPDATE BASED ON NEW INFORMATION

I ran into this exact problem last year. You need to create a backup of your database and access via phpmyadmin.

I ended up dropping the table(s) (or you can TRUNCATE them).

catalog_product_flat_1
catalog_product_flat_2

Then use the indexer.php to reindex.

You can reach more about this here

Best Answer

Related Solutions

Magento – Products occasionally disappear

Database – Rebuild Index Taking Forever in Magento

Related Topic