Flat tables in Magento and product collection

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

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.

You made it all the way down here so you must have enjoyed this post! You may also like:

How To Connect Google Analytics 4 To Magento 2 Bojan Mareljic
Bojan Mareljic, | 36

How To Connect Google Analytics 4 To Magento 2

3 best open-source eCommerce platforms in 2021 Zrinka Antolovic
Zrinka Antolovic, | 8

3 best open-source eCommerce platforms in 2021

Working with large Magento collections Kresimir Banovic
, | 10

Working with large Magento collections

15 comments

  1. Got this error:
    Fatal error: Call to undefined method Mage_Catalog_Model_Resource_Category_Flat_Collection::getAllIdsSql()
    Using magento 1.7.0.2

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

    Thanks again

    Vishwa

  3. Hi,
    I have enable the flat catalog category but facing some problem. Its changing my top menu and left menu to the default menu which was in template.
    Please let me know how can i resolve this issue.

    Thanks,
    Regards,
    Iftikhar

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

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

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

  7. 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> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.