Magento doesn’t unfortunately provide anything out of the box to check whether attribute options have been used, in our particular case we had a lot of duplicated options that had been created by a third party integration and we needed to remove any that weren’t in use.

We turned to google and came across a Stack Exchange question here.

In case the question ever disappears we’ve copied the answer that worked for us here. Please be careful when running this sql command, we’d suggest testing it on a development/staging version of your website and then rolling out the changes from their – and as always make sure you backup your site before you run anything like this:

FROM `eav_attribute` a
INNER JOIN `eav_attribute_option` o ON a.`attribute_id` = o.`attribute_id`
INNER JOIN `eav_attribute_option_value` v ON v.`option_id` = o.`option_id`
INNER JOIN `eav_entity_type` t ON t.`entity_type_id` = a.`entity_type_id`
LEFT JOIN `catalog_product_entity_int` pi ON o.`option_id` = pi.`value` AND o.`attribute_id` = pi.`attribute_id`
LEFT JOIN `catalog_product_entity_varchar` pv ON o.`option_id` = pv.`value` AND o.`attribute_id` = pv.`attribute_id`
WHERE pi.`entity_id` IS NULL
AND pv.`entity_id` IS NULL
AND t.`entity_type_code` = “catalog_product”

Magento 2.4.7 is here!
David Pratt

Magento 2.4.7 Has Arrived!

Magento 2.4.7 is set to land, with the latest release due to be launched on Tuesday the 9th of April 2024. The latest updates sees over 150 performance and security fixes as well as platform enhancements and upgrades.
Read More »
DTF Digital