Magento – NULL Values for Attributes in the Database

attributesdatabase

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?

Best Answer

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.

Related Solutions

Magento – Export Brand, Quantity, and Size/Color from Database

Figured it out, and got values from EAV instead of the flat_table:

    SELECT
    at_name.value AS name,
    at_manufacturer_value.value AS brand,
    at_price.value AS msrp,
    at_size_value.value AS size,
    at_color_value.value AS color,
    at_special_price.value AS sale_price,
    at_retailstarupc.value AS star_upc,
    e.sku,
    si.qty

    FROM magento.cataloginventory_stock_item si
    LEFT JOIN magento.catalog_product_entity e ON e.entity_id = si.product_id

    LEFT JOIN magento.catalog_product_entity_varchar at_name ON e.entity_id =         at_name.entity_id AND at_name.attribute_id = '60'
    LEFT JOIN magento.catalog_product_entity_decimal at_price ON e.entity_id = at_price.entity_id AND at_price.attribute_id = '64'
    LEFT JOIN magento.catalog_product_entity_decimal at_special_price ON e.entity_id = at_special_price.entity_id AND at_special_price.attribute_id = '65'
    LEFT JOIN magento.catalog_product_entity_varchar at_retailstarupc ON e.entity_id = at_retailstarupc.entity_id AND at_retailstarupc.attribute_id = '290'
    LEFT JOIN magento.catalog_product_entity_int at_manufacturer ON e.entity_id = at_manufacturer.entity_id AND at_manufacturer.attribute_id = '70'
    LEFT JOIN magento.eav_attribute_option_value at_manufacturer_value ON at_manufacturer.value = at_manufacturer_value.option_id AND at_manufacturer_value.store_id = '0'
    LEFT JOIN magento.catalog_product_entity_int at_size ON e.entity_id = at_size.entity_id AND at_size.attribute_id = '141'
    LEFT JOIN magento.eav_attribute_option_value at_size_value ON at_size.value = at_size_value.option_id AND at_size_value.store_id = '0'
    LEFT JOIN magento.catalog_product_entity_int at_color ON e.entity_id = at_color.entity_id AND at_color.attribute_id = '80'
    LEFT JOIN magento.eav_attribute_option_value at_color_value ON at_color.value = at_color_value.option_id AND at_color_value.store_id = '0'

    WHERE e.type_id = 'simple'
    HAVING qty > 0 AND brand = "Arc'teryx";

Magento Database – Negative Values in sales_flat_order.discount_amount

The discount amounts on the complete order are stored with negative amounts, but are stored positively against each item that they effect.

Negative sales_flat_order
Positive sales_flat_order_item

The negative values is stored against the table sales_flat_quote_address and then copied across to the table sales_flat_order

Related Topic