Magento 1 attribute usage statistic

README.md

Magento 1 attribute usage statistic

The idea here is super simple - check how much is current attribute used and decide which can be removed from the shop to increase the performance and/or reduce database load. Script might be quite heavy to run at once, thus consider splitting it in two by commenting out some left joins and corresponding count selects.

get-attribute-statistic.sql

SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    count(cpeda.value_id),
    count(cpedc.value_id),
    count(cpeg.value_id),
    count(cpei.value_id),
    count(cpemg.value_id),
    count(cpet.value_id),
    count(cpeuk.value_id),
    count(cpev.value_id)
FROM eav_attribute AS ea 
    CROSS JOIN eav_entity_type AS eet 
        ON ea.entity_type_id = eet.entity_type_id 
    LEFT JOIN catalog_product_entity_varchar AS cpev
        ON ea.attribute_id = cpev.attribute_id AND cpev.value IS NOT NULL AND ea.entity_type_id = cpev.entity_type_id 
    LEFT JOIN catalog_product_entity_datetime AS cpeda
        ON ea.attribute_id = cpeda.attribute_id AND cpeda.value IS NOT NULL AND ea.entity_type_id = cpeda.entity_type_id
    LEFT JOIN catalog_product_entity_decimal AS cpedc
        ON ea.attribute_id = cpedc.attribute_id AND cpedc.value IS NOT NULL AND ea.entity_type_id = cpedc.entity_type_id
    LEFT JOIN catalog_product_entity_gallery AS cpeg
        ON ea.attribute_id = cpeg.attribute_id AND cpeg.value IS NOT NULL AND ea.entity_type_id = cpeg.entity_type_id
    LEFT JOIN catalog_product_entity_int AS cpei
        ON ea.attribute_id = cpei.attribute_id AND cpei.value IS NOT NULL AND ea.entity_type_id = cpei.entity_type_id
    LEFT JOIN catalog_product_entity_media_gallery AS cpemg
        ON ea.attribute_id = cpemg.attribute_id AND cpemg.value IS NOT NULL
    LEFT JOIN catalog_product_entity_text AS cpet
        ON ea.attribute_id = cpet.attribute_id AND cpet.value IS NOT NULL AND ea.entity_type_id = cpet.entity_type_id
    LEFT JOIN catalog_product_entity_url_key AS cpeuk
        ON ea.attribute_id = cpeuk.attribute_id AND cpeuk.value IS NOT NULL AND ea.entity_type_id = cpeuk.entity_type_id

WHERE eet.entity_type_code = 'catalog_product'
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC