Most of you probably know that Magento uses an EAV database structure for categories, and products. In some cases, this solution is not the best or fastest. and for that, there is an option of Magento 2 Flat Catalog.
I will show you here examples of how to speed up product collection. Magento has config options: “Use Flat Catalog Category” and “Use Flat Catalog Product”.
The difference between the Magento 2 EAV catalog and the Magento 2 Flat Catalog is as follows:
1. Eav Catalog
EAV is an entity attribute value database model, where data is fully in normalized form. Each column’s data value is stored in its respective data type table. For example, for a product,
The product ID is stored in
Product name in
Product price in
The product created date in
Product description in
EAV is complex as it joins 5-6 tables even if you want to get just one product’s details.
Columns are called attributes in EAV.
2. Flat Catalog
The flat model uses just one table, so it’s not normalized and uses more database space. It clears the EAV overhead,
It’s good when comes to performance, as it will only require one query to load whole product instead of joining 5-6 tables to get just one product’s details.
Columns are called fields in the flat model.
Magento implemented indexers which will periodically query the standard collections and populate flat database tables in the following format. Where * is store id.
These tables have the non-normalized product and category data that are intended to be read-only. This allows Magento to fetch category and product data in a single query.
You can enable a flat catalog by navigating to
Store > Configuration > Catalog > Storefront > Use Flat Catalog Category | Use Flat Catalog Product . And set this setting to yes.
If you are adding a new attribute in the EAV table for the catalog. Then don’t forget to run re-indexing
(System > Tools > Index Management). Re-indexing refreshes your flat catalog tables.