I am using the fastsimpleimport module to create my custom import. This has a setDropdownAttributes() function to automatically create needed attribute values but it does not remove unused values. Those still show up in my advanced search.

How can I search and remove unused attribute values?

I think they are stored in eav_attribute_option_value but I am not sure how to check if they are used by a product.


2 Answers 2


LuFFy's answer is technically incorrect as it has 3 issues:

  • it will remove values for admin store view only leaving some store-views related garbage
  • it will remove unused values of attributes of all entity types
  • it will remove ALL values from attributes with "multiple select" type regardless if they are assigned to any products or not because they options are stored in catalog_product_entity_varchar and not catalog_product_entity_int

The query below addresses these problems:

    DELETE o, v
      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"
  • @tim-bezhashvyly This is a much better answer. However, this doesn't correctly match multiple-select attributes. If you change ON o.option_id = pv.value to ON FIND_IN_SET(o.option_id, pv.value) then this will correctly match multiple-select attributes.
    – Tyler V.
    Commented Mar 28, 2015 at 2:21
  • Might I also suggest that you add a.is_user_defined = 1 to your WHERE clause as well. You likely don't want to remove any attribute values from system attributes.
    – Tyler V.
    Commented Mar 28, 2015 at 3:13
  • This doesn't work on multi-select Commented Jan 24, 2019 at 21:27
delete c,b
from eav_attribute a
inner join eav_attribute_option b on a.attribute_id = b.attribute_id
inner join eav_attribute_option_value c on c.`option_id` = b.option_id
LEFT join catalog_product_entity_int pi on b.option_id=pi.value and pi.store_id=0 and b.attribute_id=pi.attribute_id
where entity_id is null

Try this Query..


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.