Table of contents
Notes: From Magento version 2.1 and above, this is no longer a best practice and you should refrain from using any extension that involves the use of Flat catalog as this feature is known to cause performance degradation and other indexing issues.
When you have a large Magento store with thousands of products and want to optimize its speed and performance, you should consider enabling Flat Catalog instead of the default Entity-Attribute-Value (EAV) catalog.
Entity-Attribute-Value (EAV) Catalog, in the one hand, stores attribute values in many tables. For example, for a product:
- Product ID is in “catalog_product_entity_int” table
- Name in: catalog_product_entity_varchar
- Price in: catalog_product_entity_decimal
- Created date in: catalog_product_entity_datetime
- Product description in: catalog_product_entity_text
As those many tables stores all the attributes, SQL queries are long, complex and time-consuming.
Flat Catalog, on the other hand, creates flat tables that store all necessary information about a product or a category. The flat table is updated automatically every minute, or according to your cron job settings. Therefore, the queries are shorter and your store will perform better. The Flat Catalog indexing can also speed up the processing of product collection and price rules in cart.
Enable Flat Catalog in Magento 1 and Magento 2
You should enable flat catalog in a development environment before using it on live site.
Magento 1
Step 1: In your backend, navigate to System > Configuration > Catalog > Catalog
Step 2: Expand the Frontend section.
- Set Use Flat Catalog Category to “Yes”
- Set Use Flat Catalog Product to “Yes”
Step 3: Click Save Config button.
Step 4: After saving config, there will be a message at the top of the workspace:
- Click the Index Management link:
- Click on Reindex Required.
- Click Reindex Data.
*After enabling Flat Catalog, you can check the results by following these steps:
1. For a Single Product:
Step 1: Rename a Product:
- On the Admin menu, select Catalog > Manage Products
- Choose a product to modify.
- Rename the product: for example, add the text “_DEMO” to the Name field.
- Click Save button.
Step 2: Open your store website in a new tab:
- Search for the product you edited, and browse to its assigned category.
Now due to the flat catalog, the change is updated every minute or according to your Cron job settings.
2. For a Category:
Step 1: Create new Category:
- Navigate to Catalog > Categories > Manage Categories.
- Select an existing category. Click Add Subcategory button.
- In the Name field, enter “Test Category” for example.
- Set “Is Active” to “Yes”.
- Click the Category Products tab, click Reset Filter.
- Select products to the new category by tick the checkbox.
- Click Save Category button.
Step 2: Open your store website in a new tab:
- Find the category you have created.
Now due to the flat catalog, the change is updated every minute or according to your Cron job settings.
Magento 2:
Step 1: In your backend, navigate to Stores > Settings > Configuration:
Step 2: Go to Catalog > Catalog. Expand the Storefront section.
- Uncheck the checkbox at “Use system value”.
- Set Use Flat Catalog Category to “Yes”.
- Set Use Flat Catalog Product to “Yes”
Step 3: Click Save Config button.
Step 4: After Save config, there is a message at the top of the workspace:
- Click the Cache Management link, then follow instructions to refresh the cache:
- Tick at the checkbox at Configuration and Page Cache.
- Choose Refresh action, then click Submit button
*After enabling Flat Catalog, you can check the results by following these steps:
1. For a Single Product
Step 1: Rename a Product:
- On the Admin menu, select Catalog > Products
- Choose a product to modify.
- Rename the product: for example, add the text “_DEMO” to the Product Name field.
- Click Save button.
Step 2: Open your store website in a new tab:
- Search for the product you edited, and browse to its assigned category.
Now due to the flat catalog, the change is updated every minute or according to your Cron job settings.
2. For a Category:
Step 1: Create new Category:
- Navigate to Catalog > Categories
- Select an existing category. Click Add Subcategory button.
- Set “Enable Category” to “Yes”
- In the Category Name field, enter “Test Category” for example.
- Expand the Products in Category tab, click Reset Filter.
- Select products to the new category.
- Click Save button.
Step 2: Open your store website in a new tab:
- Find the category you have created.
Now due to the flat catalog, the change is updated every minute or according to your Cron job settings.
