Flat tables in Magento and product collection

Domagoj Potkoc — May 30, 2011

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;
}