Magento2 Flat Catalog


Magento stores catalog data in multiple tables, based on the Entity-Attribute-Value (EAV) model. Because product attributes are stored in many tables, SQL queries are sometimes long and complex for fetching different data from different tables.


Whereas a flat catalog creates new tables and fills data during indexing, where each row contains all the necessary data about a product or category. A flat catalog is updated automatically‚ÄĒeither every minute or according to your cron job for index management. Flat catalog indexing can also speed up the processing of catalog and cart price rules. A catalog with as many as 50k+ SKUs can be indexed quickly as a flat catalog.
Please note this is best practice for lower magento2 version or Magento1 version but from Magento2.3.0 it's not best practice to use flat tables for catalog.
From version Magento 2.3.0 and higher, the use of a flat catalog is no longer a best practice and is not recommended. Use of flat table will decrease performance and create an issue for index management and other performance issues due to the indexing problem.

How to Enable the Flat Catalog?


On the Admin sidebar, click on Stores. Then under Settings, click on Configuration.
In the panel on the left under Catalog, choose Catalog.
Click on the Storefront section. Then, do the following:


Set Use Flat Catalog Category to Yes. If necessary, clear the Use system value checkbox.
Set Use Flat Catalog Product to Yes.

Flat Catalog Configuration


When complete, tap  Save Config.
Refresh cache and run indexing.

You need to make attribute to use in the product listing to use the attribute in flat tables

 

 

Read more »