NULL Values for Attributes in the Database

Question

All of the products on this Magento CE site I'm working on are imported through a modified Dataflow importer. Almost all of the changes to products are made with the Mage_Catalog_Model_Product_Action model. (There is a call to the normal product save model when categories are changed though.) The issue that I'm seeing is a ton of NULL values in the database tables. For instance, if there is a Dropdown attribute called Type and only 10% of products actually have a value for the attribute, the other 90% will be given a NULL value in the database.

Right now the site has around 60,000 SKUs. As an example, the catalog_product_entity_int table has just shy of 1,000,000 rows. 700,00 of those rows contain NULL for the value field.

Does Magento benefit in any way from having a row for every product/attribute combination for all products that have been saved since an attribute was added? I'm sure that NULL values in the catalog_product_entity_int table take up very little hard drive space, but I could see it slowing down joins a lot, (especially left joins.)

Should I be trying to clean these NULL values out?

Answer 1

Do not delete NULL values in the default scope if you intend to override the values in store scope. The way, Magento constructs the SQL query for product loading with left joins, it does not find store specific values if there is no entry for the default scope.

Answer 2

you can see here what queries run when retrieving attribute values with scope website or store view.
Basically magento does something like this in the select:

IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` 

this means that if there is a value for a specific store is selected, otherwise, the default value is selected.
I'm not sure how the if statement in the query works in terms of performance. In case it is faster if the first value is found then you may get some speed, but since 70% of your values are null, I'm sure that speed (if there is one) is balanced by the number of rows it has to analyze.
My opinion is that you should clean these null values.
But backup before doing anything.