Using a Flat Catalog

Starting with Magento 2.3.0+, the use of a flat catalog is no longer a best practice and is not recommended. Continued use of this feature is known to cause performance degradation and other indexing issues. A detailed description and solution is available in the Help Center.

Magento typically stores catalog data in multiple tables, based on the Entity-Attribute-Value (EAV) model. Because product attributes are stored in many tables, SQL queries are sometimes long and complex.

In contrast, a flat catalog creates new tables on the fly, where each row contains all the necessary data about a product or category. A flat catalog is updated automatically—either every minute, or according to your cron job. Flat catalog indexing can also speed up the processing of catalog and cart price rules. A catalog with as many as 500,000 SKUs can be indexed quickly as a flat catalog.

Before you enable a flat catalog for a live store, make sure to test the configuration in a development environment.

Step 1: Enable the Flat Catalog

  1. On the Admin sidebar, go to Stores > Settings > Configuration.

  2. In the panel on the left under Catalog, choose Catalog.

  3. Expand the Storefront section. Then, do the following:

    • Set Use Flat Catalog Category to Yes. If necessary, clear the Use system value checkbox.

    • Set Use Flat Catalog Product to Yes.

    Flat Catalog Configuration

  4. When complete, click Save Config.

  5. When prompted to update the cache, click the Cache Management link in the system message and follow the instructions to refresh the cache.

Step 2: Verify the Results

Method 1: Verify the Results for a Single Product

  1. On the Admin sidebar, go to Catalog > Categories.

  2. Open a product in edit mode.

  3. In the Name field, add the text _TEST to the end of the product name.

  4. Click  Save .

  5. On a new browser tab, navigate to the home page of your store. Then, do the following:

    • Search for the product you edited.

    • Use the navigation to browse to the product under its assigned category.

    If necessary, refresh the page to see the results. The change will appear within the minute, or according to your Cron schedule.

    Storefront with Flat Catalog

Method 2: Verify the Results for a Category

  1. On the Admin sidebar, go to Catalog > Categories.

  2. In the upper-left corner, verify that Store View is set to All Store Views.

    If prompted, click OK to confirm.

  3. In the category tree, select an existing category. Then, click Add Subcategory and do the following:

    • In the Category Name field, enter Test Category.

    • When complete, click Save.

      Test Subcategory

    • Expand the Products in Category section. Then, click Reset Filter to display all products.

    • Select the checkbox of several products to add them to the new category. Then, click Save.

      Test Category Products

  4. On a new browser tab, navigate to the home page of your store. Then, use the store navigation to browse to the category you created.

    If necessary, refresh the page to see the results. The change will appear within the minute or according to your cron schedule.

Step 3: Remove the Test Data

Do the following to remove the test data and restore the original product name and catalog configuration.

Remove the test category

  1. On the Admin sidebar, go to Catalog > Categories.

  2. In the category tree, select the test subcategory that you created.

  3. In the upper-right corner, click Delete. Then when prompted to confirm, click OK.

    This will not remove the products that are assigned to the category.

Restore the original product name

  1. On the Admin sidebar, go to Catalog > Categories.

  2. Open the test product in edit mode.

  3. Remove the _TEST text that you added to the Product Name.

  4. In the upper-right corner, click Save.

Restore the original catalog configuration

  1. On the Admin sidebar, go to Stores > Settings > Configuration.

  2. In the panel on the left under Catalog, choose Catalog.

  3. Expand the Storefront section, and do the following:

    • Set Use Flat Catalog Category to No.

    • Set Use Flat Catalog Product to No.

  4. When complete, click Save Config. Then when prompted, refresh the cache.