Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

More efficient Product Flat Table construction
 
AcerisAu
Jr. Member
 
Total Posts:  3
Joined:  2013-03-04
 

While customising my Magento 1.7.0.2 install I stumbled across a strange issue in regards to the way the product flat tables were handling some of my custom attributes. In particular attributes I had created with a scope of store view.

It would appear that the values of my custom attribute were not being stored in the product flat table. The column would appear but all the values were null.

After doing a bit of digging I soon found where the attribute was being saved into the db and noticed some peculiar behaviour. Examining app\code\core\Mage\Eav\Model\Resource\Entity\Attribute\Option.php I noticed that Magento does a left join against the eav results for the attribute on the default store id ‘0’ and the actual store id in my case ‘1’. Problem here is that if there is no value set on the default store level, as is the case in my senario then the left join would fail to output the value in store id ‘1’. A record must exist on the default store id ‘0’ inorder for Magento to see the value in store id ‘1’ so that it can then populate it into the flat table. This means that a whole bunch of NULL records must be created in the eav tables just to ensure that the store values are being populated in the flat tables.

Although this may seem trivial to some, in my case and I would imagine in many other cases there would be an incredible amount of bloated NULL records in the eav tables. I have almost 70,000 null store_id ‘0’ records in my catalog_product_entity_int and over 500,000 null value records in my catalog_product_entity_varchar.

I dare not modify the core as I don’t know if these null records are required for any other purpose in magento. But it seems like an incredible waste to require a null value record for every attribute. I would’ve thought that it would be far more efficient if there were no null value records in the eav tables. Rather add one extra join to the Option.php queries to left join against the catalog_product_entity table to return the appropriate store value.

Although we need to add an extra join I would think that a more scalable way to do it would be something like this…

SELECT IFNULL(t3.value,t2.value) AS VALUE
FROM catalog_product_entity t1
LEFT JOIN catalog_product_entity_int t2
ON t1.entity_id = t2.entity_id
AND t2.entity_type_id = ‘ENTITY_TYPE_ID’
AND t2.attribute_id = ‘ATTRIBUTE_ID’
AND t2.store_id = ‘DEFAULT_STORE_ID’
LEFT JOIN catalog_product_entity_int t3
ON t1.entity_id = t3.entity_id
AND t3.entity_type_id = ‘ENTITY_TYPE_ID’
AND t3.attribute_id = ‘ATTRIBUTE_ID’
AND t3.store_id = ‘STORE_ID’

And then we won’t need to have, in my case, over 500,000 records bloating my db with unencessary null values.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top