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 {
            $this->_init('catalog/product');
        }
        $this->_initTables();
    }

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.

Interested in hiring us?

Have a chat with us. You would be surprised how small changes can make your business even more successful.


About Domagoj Potkoc

Backend Developer

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

Read more posts by Domagoj / Visit Domagoj's profile

10 comments

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

  2. @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.

    Cheers

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

  4. 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,
    Ian

Leave a Reply

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

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