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(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

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

get-attribute-statistic_splitted_by_type.sql

/* varchar */
SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    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 

WHERE eet.entity_type_code = 'catalog_product' AND ea.attribute_id IN (SELECT DISTINCT(attribute_id) FROM catalog_product_entity_varchar)
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC

/* datetime */
SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    count(cpeda.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_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
WHERE eet.entity_type_code = 'catalog_product' AND ea.backend_type = 'datetime'
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC

/* decimal */
SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    count(cpedc.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_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
WHERE eet.entity_type_code = 'catalog_product' AND ea.backend_type = 'decimal'
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC

/* int */
SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    count(cpei.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_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
WHERE eet.entity_type_code = 'catalog_product' AND ea.backend_type = 'int'
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC

/* text */
SELECT 
    eet.entity_type_code, 
    ea.attribute_id, 
    ea.attribute_code, 
    ea.backend_type, 
    count(cpet.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_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
WHERE eet.entity_type_code = 'catalog_product' AND ea.backend_type = 'text'
GROUP BY ea.attribute_id
ORDER BY ea.attribute_id ASC

Modification to original GIST of user aboritskiy

• Removed catalog_product_entity_url_key join as this is only relevant for the Magento Enterprise Edition