Flat tables in Magento and product collection

You probaly know that Magento uses EAV database structure for categories, products. In some cases this solution is not the best or fastest.

I will get you here example how to speed up product collection. Magento has config option: “Use Flat Catalog Product”. In order to see this option, go to Magento admin section and click on: System -> Config -> Catalog and Frontend.

If you enable this option, you will adjust Magento to use “Flat resource” for product collection. Take a look at product collection class “Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection” you will see a method “_construct“.

If flat is enabled in Magento admin section, collection will use resource: ‘catalog/product_flat’, php class “Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat”
without EAV database structure. SQL queries will be simplier, faster and you would need to have better performance on product listing.
In this Flat resource there is method:

This method returns flat table name and it depends about current store. Flat table name for store_id “1” is “catalog_product_flat_1“, below you can see method which is in charge for giving current table name.

Hello!
Thank you for your tutorial , When enable Use Flat Catalog Product i could not get Disabled product collection, someone says “Flat Products only contains active products…”, our website getting too many products we have to Flat catalog , somewhere we have to show disabled products please help me to resolve the issue

As someone who’s expecting to run into performance issues in the near future, I dont suppose you could give a brief summary of the pro’s and cons of using these flat tables – or post a link to somewhere that does detail this?

Your email address will not be published. Required fields are marked *

Comment *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.