Magento uses the Entity-Attribute-Value (EAV) model for storing customer, product, and category data in its database.
Although the EAV model enables you to have completely extensible attributes for those objects, object attributes are stored in multiple tables rather than one very large table.
Because attributes are stored in many tables, and that includes copies of some of the same attributes, SQL queries can be long and complex.
The Flat Catalog feature creates new tables on the fly, where each row contains all necessary data about a product or category.
You can enable Flat Catalog by navigating to:
Stores >> Settings >> Configuration.
Then select Catalog >> Catalog.
Open Frontend and switch Use Flat Catalog Category and Use Flat Catalog Product both to Yes:
When prompted to clear the cache, click on Cache Management and flush the cache.
Verify the Results!
Method 1: Verify the Results for a Single Product:
1. On the Admin Dashboard, select Products >> Catalog, then, do the following:
a. Choose a product to modify. Then, open the product in edit mode: Actions >> Edit
b. In the Product Name field, add the text “_DEMO”.
2. Click the Save button.
3. On a new browser tab, navigate to the home page of your store.
4. Search for the product you edited, and browse to its assigned category.
If necessary, refresh the page to verify the results. The change will appear within the minute or according to your cron job schedule.
Method 2: Verify the Results of Multiple Products
1. On the Admin Dashboard, select Products >> Categories.
2. In the upper-left corner, select: Store View: All Store Views
3. In the panel on the left, select an existing category. Click the Add Subcategory button, and do the following:
a. In the Category Name field, enter “Test Category.”
b. Set Enable Category to Yes.
c. Click the Save Category button.
d. Click the Products in Category tab. Then, click the Reset Filter button to display all products.
e. Select the checkbox of several products to add them to the new category. Then, click the Save Category button.
4. On a new browser tab, navigate to the home page of your store. then, Find the category you created.
If necessary, refresh the page to verify the results. The change will appear within the minute or according to your cron job schedule.
That’s how you switch to use a Flat Catalog in Magento v2.
