How it works: Magento 2 indexers
Magento’s 2 broken Db Shema is the darkest part of Magento architecture.
Customers who use valueless Magento 2 EAV and multi-store functionality and create every attribute possible for all his products. The result, flat-catalog product indexer won’t work!
The error you are likely to see in Magento is as vague as Can't initialize indexer process.
The EAV model is great in theory but not in practice.
In the context of Magento, it is a real issue and is one of the major contributors to the slowness of the Magento platform. Basically, Magento 2 is eCommerce CMS with bad designed 3-d party modules like WP for the blog, without any clue about performance and architecture.
The reason for this slowness is that in a relational database, the EAV model makes it so that every simple SQL query is one gigantic query made of tons of JOINs.
To give you an example, querying a product in Magento may need to join no less than 11 tables!
catalog_product_entity, catalog_product_entity_datetime, catalog_product_entity_decimal, catalog_product_entity_int, catalog_product_entity_gallery, catalog_product_entity_group_price, catalog_product_entity_media_gallery, catalog_product_entity_text, etc, etc.
In order to fix this issue, the Magento team created what they call “flat tables” which are tables that are created by querying the database with an EAV query (i.e. the query with a million joins) and putting the results in a table with as many columns as there is attributes being returned by the original query.
In theory, choosing to use EAV was not a bad idea for small business eCommerce CMS. In practice, this idea did not scale for large online stores and it has made Magento hugely complex, slow, and hard to use. The complex not because it is smart it is complex because it is cumbersome obfuscated spaghetti code.
As a business, you will spend or will pay for a humongous number of engineering hours optimizing the Magento 2 mistakes which are presented by Magento officials as a feature.
A table in MySQL has a limitation of 65535 bytes of overall row length, when you present a multitude of attributes (specifically drop-down/multiple select/text/image), they are allocated a 255 character limit, as per MySQL’s varchar. In Magento2 products catalog in the "flat" mode suffers from this limitation depending on the number and combination of the product attributes that participate in the flat product index.
Depending on what our client wants to do, there will likely be an extension to bypass this fault and allow some intelligence to modify the SQL statement and the resulting flat table to allow more than the ~85 text field/multiple select/image limitation.
However, it will work only for a small store. Big stores will have a lot of pain with Magento 2 database. And here it is:
Utilizing all silly Magento features your database will grow exponentially quickly and will have a really bad performance. If you wanna good eCommerce usee proper framework and architecture. Magento is overadvertised legacy CMS for not technically educated merchants. Most of the merchants don’t have the technical background to understand what Magento sells and sales representatives can sell whatever they want.
