Magento Flat Tables

In this blog post we will see what are flat tables in magento and also see how indexing process happens for flat tables.

What are flat tables

We know that product/category are stored in magento database using the EAV model. EAV model is basically a distributed attribute model which has many advantages, but one of its major drawback is speed. Since data is distributed across many tables in EAV, to load a single entity many sql queries are required which makes the EAV structure slow. Flat tables on the other hand means, storing all data in a single table instead of multiple tables. So this reduces the number of queries required and hence increases the speed.
So all attributes of an EAV entity become column name of a single table and all attribute data relating to an entity is stored in a single table.

Flat table options are available for category and product only. Flat table can be activated from
admin -> System -> Configuration -> Catalog -> Frontend (User Flat Catalog Category, User Flat Catalog Product). If we set this to yes, it will ask us to reindex data using (Index Management). Once this is done flat tables get ready to use in frontend.

Flat Table Implementation

To understand how flat tables are implemented in magento, we need to understand the indexing process for flat tables. We will look into the indexing process of catalog/product entity below.

The indexer class used for product is “Mage_Catalog_Model_Resource_Product_Flat_Indexer”. The main function which reindex’s all data is rebuild()

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

As we can see the rebuild() is called recursively for each store_id and then various functions are called for each store. Lets look at each function in detail

prepareFlatTable
This function creates/updates the flat index table, add/update/drops columns, index, constraints etc to the table.

Table name is “catalog_product_flat_1”, “catalog_product_flat_2”, here 1:2 are the store_ids which we have. So for each store a new flat table is created.
Next for each attribute from the eav model, the function “getFlatColumns()” is called on every attribute. The attribute model in turn calls “getFlatColumns()” on the attribute source model. The source model can return an array of columns or empty array depending on which the table columns are decided.
This is function definition of “getFlatColumns()” in class

    public function getFlatColumns()
    {
        // If source model exists - get definition from it
        if ($this->usesSource() && $this->getBackendType() != self::TYPE_STATIC) {
            return $this->getSource()->getFlatColums();
        }

        if (Mage::helper('core')->useDbCompatibleMode()) {
            return $this->_getFlatColumnsOldDefinition();
        } else {
            return $this->_getFlatColumnsDdlDefinition();
        }
    }

If an attribute is static attribute or it doesn’t use a source model then its column is always returned

                $columns[$this->getAttributeCode()] = array(
                    'type'      => $helper->getDdlTypeByColumnType($type),
                    'length'    => $size,
                    'unsigned'  => $prop['UNSIGNED'] ? true: false,
                    'nullable'   => $prop['NULLABLE'],
                    'default'   => $prop['DEFAULT'],
                    'extra'     => null
                );

If source model is defined for an attribute then columns are taken from source model.

Next we have the function “getFlatIndexes()”. This also behaves same as getFlatColumns(), this function is called for each attribute. If the attribute uses a source model then getFlatIndexes() is taken from the source model, else if attribute type is static then index is added or if attribute backend is “varchar” index is added.

Also the maximum number of attribute flat table can support is 64 which is defined the Catalog config.xml at node
‘global/catalog/product/flat/max_index_count’

There are two more important configuration settings
‘global/catalog/product/flat/add_filterable_attributes’ : if this is set to 1, only attribute with is_filterable = 1 are added to flat tables
‘global/catalog/product/flat/add_child_data’ : if this is set to 1, two column are added ‘is_child’ and ‘child_id’. Both these store relationship between configuration, grouped, bundled and its associated simply product.

cleanNonWebsiteProducts
This function removes products which don’t belong to the current store under process.

updateStaticAttributes
As the name suggest this function gets the value of all static attributes for enabled products and updates it in the flat table.

updateEavAttributes
As the name suggests this function gets the value of columns for EAV attributes. This calls the function “getFlatUpdateSelect()” on the attribute model to get the select query.

updateEventAttributes
This dispatches an event

    public function updateEventAttributes($storeId = null)
    {
        Mage::dispatchEvent('catalog_product_flat_rebuild', array(
            'store_id' => $storeId,
            'table'    => $this->getFlatTableName($storeId)
        ));
    }

updateRelationProducts
This function updates the relationship between products like configuration and simple. This function only runs if we have enabled “add child data” through config.xml as mentioned above.

In the end we set the build status in flat flag class “Mage_Catalog_Model_Product_Flat_Flag”. This basically sets the build status of each store.

Flat Table Process For Category

The class for flat tables is “Mage_Catalog_Model_Resource_Category_Flat” and the main function is reindexAll()

The tables for category flat table are “catalog_category_flat_store_1”, “catalog_category_flat_store_2” etc. Next index and constraints are added. These don’t have any dependency on attribute tables are predefined in the class already. After this in the rebuild() function, the data is added to the flat table, this process is quite simple compared to product and you see code directly in the file to understand it.