1. What is the EAV Catalog?
EAV is an entity attribute value database model, where data is fully in normalized form. Each column data value is stored in their respective data type table. For instance, 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 composite of 5 to 6 tables even if you just want to get one product’s details.
Every column is called an attribute in EAV.
2. What is the Flat Catalog?
On the other hand, the Flat model uses only one table, therefore 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.
In the flat model, columns are called fields.
Magento implemented indexers which will periodically query the standard collections and populate flat database tables in following format. Where * is store id.
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.
3. When should we use the Flat catalog?
If you have PHPMyAdmin installed on your server you can take a look at Magento’s tables. You’ll see that there are a lot of them starting with catalog_product_… and catalog_category_…, which are used for each product and category so that means a lot of complicated and heavy queries for every page you call.
To lighten the load on MySQL and speed up your shop, Magento offers flat tables. For example catalog_product_flat_1. If you take a look at the structure of this table you’ll see it has a lot of the basic product data which means that in the category product list, only one query has to be executed to this table instead of querying the multiple other tables including the attribute data.
However, this data is aggregated from the data in the other tables; hence, once you save a product, several indexes (System > Configuration > Index Management) are refreshed to compile this product data. In some cases, this might be unnecessary. Even though you want to see all changes immediately the page load time could take a while.
In short, whenever launching a shop, you should turn them on and use the data provided by the flat indexed instead of using full product or catalog objects.
You can enable the flat catalog by navigating to System > Configuration > Catalog > Frontend > Use Flat Catalog Category | Use Flat Catalog Product and set this to yes.
If you are adding a new attribute in EAV table for catalog, remember to run re-indexing (System > Configuration > Index Management), which will refresh your flat catalog tables.