While customising my Magento 18.104.22.168 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.