Flat tables in Magento and product collection

Featured Image

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“.

    protected function _construct()
        if ($this->isEnabledFlat()) {
            $this->_init('catalog/product', 'catalog/product_flat');
        else {

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:

public function getMainTable() {
        return $this->getFlatTableName($this->getStoreId());

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.

  	public function getFlatTableName($store = null)
        if (is_null($store)) {
            $store = $this->getStoreId();
        return $this->getTable('catalog/product_flat') . '_' . $store;

For every store you will have one flat table.


Care to rate this post?


Domagoj Potkoc

Backend Developer

Domagoj is Magento Certified Developer who enjoys playing tennis after long hours in front of computer screen.

Other posts from this author

Discussion 10 Comments

Add Comment
  1. Ian

    Hi Domagoj,

    Great article thanks.

    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?

    Thanks again,

  2. James Thompson

    Yes, it would be interesting to know the downsides of a flat product catalog.

  3. after enable this, all my products and categories shows 404

  4. xbb

    1 article, 3 comments with 2 questions, no answer from author.

  5. Piotr

    xbb – did You know a term SEO and traffic ? ;-)

  6. Aftab Naveed

    xbb, Piotr they are doing it for their own gains ;) but still they are educating the people.

  7. After enable this, my all products shows 404 error…

  8. After enabling flat catalog I am seeing old featured products on the homepage. Is there something I’m missing here?

  9. Andre F

    @Ted Fuller – Before you activate this, you should go to System -> Index Management and Reindex both Flat Categories and Flat Products indexes.
    Also make sure you keep this indexes up to date.


  10. hong

    Can help me to create a module in admin page, How to get product not bestseller in magento

Add Your Comment

Please wrap all source codes with [code][/code] tags.