“EAV” & “Flat Catalog” in Magento 2

Most of you probably know that Magento uses EAV database structure for categories, products. In some cases this solution is not the best or fastest.

I will show you here example how to speed up product collection. Magento has config option: “Use Flat Catalog Category” and “Use Flat Catalog Product”.

Difference between EAV catalog and Flat catalog is Magento as following:

1. Eav Catalog

EAV is entity attribute value database model, where data is fully in normalized form. Each column data value is stored in their respective data type table. Example, for a product,

  • Product ID is stored in catalog_product_entity_int table,

  • Product name in catalog_product_entity_varchar table,
  • Product price in catalog_product_entity_decimal table,
  • Product created date in catalog_product_entity_datetime table,
  • Product description in catalog_product_entity_text table.

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

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

Magento implemented indexers which will periodically query the standard collections and populate flat database tables in following format. Where * is store id.

  • catalog_category_flat_store_*
  • catalog_product_flat_*

These tables have non-normalized product and category data that’s intended to be read only. This allows Magento to fetch category and product data in a single query.

You can enable flat catalog by navigating to Store > Configuration > Catalog > Storefront > Use Flat Catalog Category | Use Flat Catalog Product . And set this settings to yes.

If you are adding new attribute in EAV table for catalog. Then don’t forget to run re-indexing (System > Tools > Index Management). Re-indexing refreshes your flat catalog tables.

By | 2018-07-11T12:16:48+00:00 July 22nd, 2016|Database, Magento 2|

Leave A Comment