How to Improve Magento 2 Performance with Flat Catalog

How to Improve Magento 2 Performance with Flat Catalog
Posted in: Magento 2 Guides

Hello to all Magento practitioners. I guess you are continuously wondering about the ways to make your large e-store faster and more customer friendly. Well, you've landed in the right place. In this article, I’ll tell you about a relatively painless way to make a Magento 2 store perform better. As we know, Magento 2 slowdown (or drop in system performance) is one of the challenges large e-stores face. So, let’s dive in.

Magento 2 weak performance: the why?

Magento uses the Entity-Attribute-Value catalog by default. What does it mean for regular system users? This means that 1 product having, say, 6 obligatory attributes is stored in 6 different tables (the same goes for categories).

“How come?!”,  our respected reader will exclaim, “This is counterproductive!” Couldn’t agree more, as swollen databases hurt the e-store performance. To fight this threat, Magento experts developed 2 miraculous features:

  • Use Flat Catalog Product
  • Use Flat Catalog Category

Magento 2 Flat tables: what are they?

A Flat table is an additional table storing all information about a product or a category. Working principle here is fairly simple: the shorter the query to the database, the better the store performance. That’s it, we’ve freed the rabbit from the hat. You think it’s time to leave? Hold on, I’ll tell you how our cute fluffy friend got there.)

Here is step-by-step instruction for activating Use Flat Catalog Product and Use Flat Catalog Category: Store > Configuration > Catalog > Storefront > Use Flat Catalog Category или Use Flat Catalog Product = YES

If you address the database now, you’ll see a table uniting numerous attributes; the same works for categories. The system will digest this “dish” much faster, it’s not that choky hamburger!

What has changed in the work of the system, optimized database query aside? Now Flat tables update every minute or according to your Cron settings.

Let’s test the work of activated Magento 2 Flat Catalog step by step:

We’ll test it on a simple product.

  1. Open the simple product page
  2. Now let’s change the product name. I like the name Amasty. Surprised?
  3. Now save the changes clicking Save.
  4. Now find the modified product using the search function in the frontend.

Expected result: automatic update will allow you to see the changes in a minute (or according to your Cron settings).

If your Cron doesn’t follow the “here and now principle”, you could use the command for manual Cron activation php bin/magento cron:run .

Voila! Now that your Magento performance has improved and the whole system shines and sparkles, you may well wonder, ‘If Magento 2 Flat Catalog is simpler/more performance-friendly, why not use it by default?’ This question has puzzled many clever people,  and I’m not an exception. The Internet offers an endless variety of answers. As I value your time, I offer you the three versions that  I like most. Besides, experts say they are valid. So here they are:

  • Reason #1. A limited number of magento product attributes (categories) in a database table.
  • Reason#2. EAV catalog is far more flexible, so it gives developers a lot more room for action.
  • Reason #3. In practice, if you have 5 stores that sell 300 products in 10 categories, the difference between EAV and Magento 2 Flat catalog will be significant. Imagine the situation: an insect flies some 100 m away, and you are trying to guess what insect it is, a fly or a mosquito. The thing is, using  Flat catalog pays off only when your large Magento 2 system performance is going down, and to see it, you should run your system performance testing regularly.

Look through this article to configure your system and the working environment in an optimal way. And don’t forget to enable PROFILER.

Flat table for testing your store

So we’ve just figured out that a Flat table catalog proves most effective for dealing with large (information-intense)  databases. You are likely to have a staging site with some models that you bought at (at least I hope so ). Now you want to test your Magento 2 Flat table catalog performance under load and with regard to the installed modules.

To set up a basic test, follow the step-by-step instruction below:

  • Step 1. Roll out a Magento 2 solution relying on a large database. To do so, follow this guide. Don’t forget to choose a relevant store profile. This will help you generate an adequate amount of data for your store depending on its size - large, medium or small.
  • Step 2. Choose one or several suitable modules and install them. For example,  you can choose:
    • Magento 2 Mass Product Actions that enables you to change product attributes directly in the product grid. This saves a lot of  time, which is important in testing;
    • Magento 2 Duplicate Categories that will help you test a Flat table catalog category using changes in the group of categories under different conditions.
  • Step 3. Go to command line and Turn on cache: php bin/magento cache:enable
  • Step 4. Run the command php bin/magento index:reindex
  • Step 5. Activate Use Flat Catalog Product and/or Use Flat Catalog Category

Now your store is ready for testing with Magento 2 flat catalog.

To crown it all

Magento is a productive and flexible system. Well, it does host numerous difficulties, secrets, and subtleties, but as you gradually learn them, you’ll be able to turn your system and its stores into a stunning fairy-tale unicorn that paces at a cheetah speed. And this beautiful unicorn will bring you a rich monetary take!

December 10, 2018
December 13, 2018
November 14, 2018
September 4, 2019
You have a mistake in step 3: ?ommand. You probably mean command.
Polina Litreyeva
September 4, 2019
Hello, Emet! Thanks for your careful reading. We have already corrected it.
September 11, 2019
Hi! Do you have any extension for cron job?
Polina Litreyeva
September 11, 2019
Hello! Thank you for the question. Yes, we do. You can check out our <a href="">Cron Scheduler</a>.
Jaden Bridges
March 26, 2021
Thanks, overall a good guide, added the custom menu w\o problems
Vladimir Derachits
March 26, 2021
Hi, Jaden, thanks for your feedback! We are glad to share our experience and be helpful.
Xena k
March 26, 2021
GJ author, I liked the way you present info!
Vladimir Derachits
March 26, 2021
Hi Xena, thanks for sharing your opinion! We are glad to know that you liked the article.
Leave your comment

Your email address will not be published

This blog was created with Amasty Blog Pro

This blog was created with Amasty Blog Pro

© 2009-2023 Amasty. All Rights Reserved.