I think it’s just their name for some technique that should improve the performance. Maybe they call it “flat” because it stores/caches a couple more things in the database that would otherwise need multiple queries.
So you don’t really need a guide. Just activate it and see if you gain any performance
But I’d also be interested in what it does exactly, more out of curiosity.
- Id (number)
- Created on (date)
- Name (text)
- SKU (text)
The common way to store this information would be to have one table holding all of this info. But, magento uses the EAV database structure, which is quite different… In this scheme, the data is divided by the data type on smaller tables. For our above simplified product, the tables would be:
- product - Stores the id
- product_datetime - Holds the product’s id and the “Created on” value
- product_varchar - Holds the product’s id and the attributes “Name” and “SKU”.
When magento shows a product, it joins these tables using the product id. Using the EAV method it is possible to add and remove attributes without making any drastic changes on the table structure, making the upgrade proccess much easier, and a lot of other advantages for developers.
With the flat product feature, magento keeps one updated table which joins all the product’s attributes in only one place. So, in our hypothetical example above, when you click “Rebuild flat product table”, magento will populate it’s flat table, gathering all product’s info in only one table. The flat table would have the following fields:
| id | created on | name | sku |
After enabling the flat catalog, magento will start looking for products data on the flat table, instead of joining tables every time it shows a product.
The process is a little bit more complicated than that, since the product has around 50 attributes, plus the user-created attributes, plus 3 different data types, and multiple stores and languages. But that can give you an idea of how it works.
I think the important question for me is, are there any drawbacks to enabling the flat catalog? If it’s main feature is performance increase, then is there any reason why it shouldn’t be enabled in Magento by default?
I guess the main reason for it not to be enabled by default is that it is a new feature, a big modification in the magento system, and it can bring up new bugs with it. Actually, there are some popping up already, just take a look at the forums. =) Also, if you don’t have a huge amount of products on your store (Varien says less than 1000, I would say less than that), you don’t really need to use the flat table because the EAV scheme won’t really mean a performance decrease.
About the drawback, as you would have data stored in two places with the flat catalog options enabled (the eav tables and the flat table), it can occur that the flat table gets outdated by some reason (namely: bug). But after a few months of bug extermination, i think there will be no reason for it not to be enabled.
I haven’t seen the code deep enough though, there might be something i’m missing here. =)
Ah, yes, one drawback is that it will use more space from your database quota. Now you will have some replicated data on your database, so…
Another one: everytime you create or update a product, it has to update the flat table as well… So, it will get a little slower when doing such things, but i don’t think you will notice it…
But the performance gain is great. Guess i’ll activate it on my shops in some weeks. =)
Yeah, I’ll be waiting a few weeks before updating to 1.3 (and taking advantage of the flat catalog) but it sounds brilliant in theory if the performance increase is as noticeable as it’s supposed to be.
I am hoping Magento enables you to turn off the other tables if not using them as updating two tables with the same information seems ridiculous. Our whole ability to interact with Magento is through the DB as the dataflow is worthless for large catalogs with price updates, inventory updates, attribute updates etc. etc.. While the performance change will be welcomed, this idea of maintaining another table sounds daunting. I hope Magento has a decent plan for those of us who work directly with the DB and large catalogs.
I have imported 1100 products via the DB to a new magento install. Once you click “Rebuild flat catalog”, it reads the old eav structure and populates the flat one, so, no big trouble for us here.
In any case, if you really need to disable the flat catalog feature, you can just go to System -> Configuration -> Catalog and disable it. =)
It’d be nice if you could just edit the flat DB correctly and ignore the relational DB.. would probably be easier to set up and maintain. I doubt it’s possible though. We have a 30,000-item catalog and are building a solution using the API. It imports at a paltry 500 items/hour.
It’d be nice if you could just edit the flat DB correctly and ignore the relational DB.. would probably be easier to set up and maintain. I doubt it’s possible though. We have a 30,000-item catalog and are building a solution using the API. It imports at a paltry 500 items/hour.
Have you thought of running multiple import processes, so you would get 500 items/hour/process ?
