16

As a follow-up to a previous question I would like to know:

When should I use the category flat table option?

While googling I found an advice to enable it "when you have very much categories". But what is much?

I can easily understand, that activating flat tables for products is a good decision in terms of performance, but is it really as much important for catalogs?

And what implications conclude from this? I just learned that enabling flat category tables lead to not being able to save category product positions. At least not as easy as without the flat option (thx to Marius for your help). Are there any other mentionable things?

Thanks in advance :)

Improve this question

2 Answers 2

Reset to default
10

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 starting with catalog_product_... and catalog_category_....

All these 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, for example in the category product list, only one query has to be executed to this table instead of querying the multiple other tables containing the attribute data.

However, this data is aggregated from the data in the other tables, that means that every time you save a product several indexes (System > Configuration > Index Management) are refreshed to compile this product data. In some cases like for example while developing this might be unnecessary. You want to see all changes immediately even though the page load time could take a while.

So in conclusion, whenever you launch a shop to the public, turn them on and when programming try not to use full product or catalog objects but instead use the data provided by the flat indexes.

Improve this answer
4
  • I think I would always activate flat product tables in a productive environment. But I was not so sure about the flat category tables. So you say you would always activate both? Regardless of how many catalogs you have?
    – Celldweller
    Sep 17, 2013 at 8:51
  • Always activate them, the more categories the greater the benefit of course but even when you only have 10 categories it will still save a couple of queries.
    – Sander Mangel ♦
    Sep 17, 2013 at 8:55
  • 4
    You should always activate flat catalog entities, but in theory there is a case when it does not work. This happens when you have to many attributes used in product listing and you go over the mysql max row limit. I've seen it happen only once before. You don't need to worry much, but in case it happens you will know why.
    – Marius ♦
    Sep 17, 2013 at 10:29
  • @Celldweller can we provide any more info on your question?
    – Sander Mangel ♦
    Sep 20, 2013 at 12:07
0

If you have much categories or many custom made attributes for categories it is a good choice becuase of reduced query complexity. Keep in mind that heavy load benefits even more from it. The main downside is that categories and flat_categories use a very different interface, so often code has to be very carefull in case of enabled flat categories.

Improve this answer
2
  • There they are again. Much and many. ;-) So you would also say "depends on"? Would you activate flat category tables when you have 10 catalogs? Or 50? Is it even possible to draw a line?
    – Celldweller
    Sep 17, 2013 at 8:50
  • Need to know what are the cases with some example for code changes to be taken care of while enabling flat catalog.
    – Anurag Khandelwal
    Oct 10, 2016 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.