Are flat tables still a thing we need to worry about in Magento 2? I know there's UI where we can turn the flat tables on, but I'm unsure how well they've been tested, and if they're even needed anymore.

If flat tables are still a thing -- where/when do the attributes for flat tables get generated and/or regenerated. Does this only happen at reindexing? Or can other UI actions trigger a flat table generation? Does it happen at php bin/magento setup:di:compile? Other places?

Also -- how does Magento decide if it needs to make an attribute a flat attribute? Are all EAV attributes flattened? Or are there others which might not be?

3 Answers 3


Yes, flat tables are still a thing. You can enable flat tables for products and categories from stores->configuration->catalog->storefront. enter image description here

Why do we need flat tables?

They do offer significant performance boost for large catalog data. One of our client has only 5 categories and around 250 products so it doesn't really matter if you have flat on or off. It really mattered when we switched it on for another store that had 800 categories and 17000 products and 5 multistores. Performance boost becomes apparent when you start noticing what's happening in the database. Here's what the database looks like when you have multiple websites.

flat product tables for each store

same thing for categories

The point being joins become very expensive with increase in table size even with indexes. Flat tables REDUCE (not eliminate) JOINs.

When are they created or updated

Flat tables are regenerated when you do full reindex with bin/magento indexer:reindex. They are updated on product or category update. Only relevant rows are updated.

Flat tables are not generated on setup:di:compile

**Which attributes go into flat tables? (and some limitations) **

Short answer is all the attributes that are going to be used in product listing (listing + layered navigation). An attributes store front settings decide whether or not the attribute should be included in flat tables

enter image description here

All attributes are not flattened because of limitations I mention below.

Flat table limitation

Finally there is one limitation for these flat tables which is imposed by mysql. There are row size and column size limits on table. You can read more about it on mysql table limits

You will find this <max_index_count>64</max_index_count> interesting node in config.xml of module-catalog.

Hope that helps.

  • 2
    what about flat orders? The option to do flat orders isn't next to the ones for categories and products
    – ahnbizcad
    Commented Jan 22, 2018 at 17:53
  • Is it possible to DROP a flat table and then let it create with indexer:reindex? e.g. in case on of the tables crashed beyond repair.
    – fritzmg
    Commented Feb 14, 2019 at 14:41
  • @ahnbizcad orders are already flat. sales_order table. Commented Aug 2, 2019 at 9:21
  • 1
    support.magento.com/hc/en-us/articles/360034631192 - It is no longer recommended to have flat tables enabled.
    – drew7721
    Commented Jan 17, 2020 at 21:25
  • This answer is irrelevant since M2.3. Look at answer magento.stackexchange.com/questions/196253/… instead
    – Akif
    Commented Nov 3, 2021 at 15:05

Based on magento documentation for flat table mentioned in https://docs.magento.com/m2/ce/user_guide/catalog/catalog-flat.html

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

  • 1
    Lot of queries need to be rewritten :( Commented Nov 21, 2019 at 16:35
  • If there are no flat tables why we need indexing at all ?
    – Duke
    Commented Aug 3, 2020 at 13:51

Flat tables not a thing anymore It doesn't add any performance improvements and would be deprecated in next releases

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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