17

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 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?

23

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 Jan 22 '18 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 Feb 14 at 14:41
  • @ahnbizcad orders are already flat. sales_order table. – vitoriodachef Aug 2 at 9:21

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.