Can anyone provide a clarification on the differences between EAV and Flat catalogs, when accessing the sub categories of a category?
Magento Catalogs – Differences Between EAV and Flat Catalogs
eavflat-catalogmagento-1.7
Related Solutions
If you have PHPMyAdmin installed on your server you can take a look at Magento's tables. You'll see that there are a lot starting with catalog_product_...
and catalog_category_...
.
All these are used for each product and category so that means a lot of complicated and heavy queries for every page you call.
To lighten the load on MySQL and speed up your shop Magento offers flat tables. For example catalog_product_flat_1
. If you take a look at the structure of this table you'll see it has a lot of the basic product data which means that, for example in the category product list, only one query has to be executed to this table instead of querying the multiple other tables containing the attribute data.
However, this data is aggregated from the data in the other tables, that means that every time you save a product several indexes
(System > Configuration > Index Management
) are refreshed to compile this product data. In some cases like for example while developing this might be unnecessary.
You want to see all changes immediately even though the page load time could take a while.
So in conclusion, whenever you launch a shop to the public, turn them on and when programming try not to use full product or catalog objects but instead use the data provided by the flat indexes.
We can join eav table to flat table. Below is the example for joining the wishlist table to the customer's eav tables:
$wishlistCollection = Mage::getModel('wishlist/wishlist')->getCollection();
$wishlistCollection = Mage::helper('abc')->joinEavTablesIntoCollection($wishlistCollection, 'customer_id', 'customer');
echo $wishlistCollection->getSelect()->__toString();
After that in your helper file i.e "abc" create the below function:
public function joinEavTablesIntoCollection($collection, $mainTableForeignKey, $eavType){
$entityType = Mage::getModel('eav/entity_type')->loadByCode($eavType);
$attributes = $entityType->getAttributeCollection();
$entityTable = $collection->getTable($entityType->getEntityTable());
$index = 1;
foreach ($attributes->getItems() as $attribute){
$alias = 'table'.$index;
if ($attribute->getBackendType() != 'static'){
$table = $entityTable. '_'.$attribute->getBackendType();
$field = $alias.'.value';
$collection->getSelect()
->joinLeft(array($alias => $table),
'main_table.'.$mainTableForeignKey.' = '.$alias.'.entity_id and'.$alias.'.attribute_id = '.$attribute->getAttributeId(),
array($attribute->getAttributeCode() => $field)
);
}
$index++;
}
$collection>getSelect()>joinLeft($entityTable,'main_table.'.$mainTableForeignKey.' = '.$entityTable.'.entity_id');
return $collection;
}
This is the answer to the first part of your question.
Best Answer
If flat catalog is enabled, then in any product list the flat tables will be used. The flat tables are generated one per store view when rebuilding the flat index. They contain all the attributes marked as 'Used in product listing', one column for each attribute. This is a big boost in performance because instead of a lot of joins to get the product attributes Magento only reads from one table (actually 2, the flat table and the prices index table).
IN the product view page the flat table is not used. Instead all the attributes are loaded from the EAV tables.