Magento Flat Tables

10th March 2016

Within the admin area, Magento gives us the ability to enable Flat Catalog Product and Flat Catalog Category options.

Why would you want to enable these? Well, usually, these tables store information about category and product data in many different tables, and so querying for this information, such as when on a category page can affect performance.

So Magento will take the information from the different tables and add to a single flat table. The product and category flat tables are prefixed with catalog_product_flat_ and catalog_category_flat_ respectively.

The store ID is then appended onto the end of the table name, so if you have multiple stores in one Magento instance, you will have several of these flat tables.

If you have a look at these tables in the database you will see it has a lot of the product data which means that only one query has to be executed to this table instead of querying the multiple other tables containing the attribute data.

We can see that the product collection model checks to see if the flat catalog product option is enabled within its _construct() method.

<?php
class Mage_Catalog_Model_Resource_Product_Collection extends Mage_Catalog_Model_Resource_Collection_Abstract
{
    ....
    protected function _construct()
    {
        if ($this->isEnabledFlat()) {
            $this->_init('catalog/product', 'catalog/product_flat');
        }
        else {
            $this->_init('catalog/product');
        }
        $this->_initTables();
    }
    ....
}

Within the reindexAll() method of the Mage_Index_Model_Process class, a call is made to the rebuild() method of the Mage_Catalog_Model_Resource_Product_Flat_Indexer class that updates the product data.

<?php 
class Mage_Catalog_Model_Resource_Product_Flat_Indexer extend Mage_Index_Model_Resource_Abstract {
    ....
    public function rebuild($store = null)
    {
        if ($store === null) {
            foreach (Mage::app()->getStores() as $store) {
                $this->rebuild($store->getId());
            }
            return $this;
        }

        $storeId = (int)Mage::app()->getStore($store)->getId();

        $this->prepareFlatTable($storeId);
        $this->cleanNonWebsiteProducts($storeId);
        $this->updateStaticAttributes($storeId);
        $this->updateEavAttributes($storeId);
        $this->updateEventAttributes($storeId);
        $this->updateRelationProducts($storeId);
        $this->cleanRelationProducts($storeId);

        $flag = $this->getFlatHelper()->getFlag();
        $flag->setIsBuilt(true)->setStoreBuilt($storeId, true)->save();
        return $this;
    }
    ....
}

There can be an issue with flat tables if you are using a lot of product attributes that are set to Used In Product Listing and Used for Sorting in Product Listing within the admin. Attempting to reindex the flat indexes may present the following error.

Exception message: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

The issue is that MySQL has a maximum allowable characters per row set as 65535. So if you have several varchar type attributes that have a length of 255, 255 * the number of attributes used can quickly near or exceed the 65535 limit.

If you notice a big performance difference with enabling flat tables and want to overcome this problem, then you need to start limiting some of the attribute storage in product flat data down to less than the 255 char maximum that’s automatically assigned. Alternatively, you can set the attribute’s Used In Product Listing and Used for Sorting in Product Listing options to No and then rebuild the indexes.

Note: This article is based on Magento Community/Open Source version 1.9.