I've added an attribute:

$installer->addAttribute(Mage_Catalog_Model_Product::ENTITY, 'show_in_subview', array(
    'type'                       => 'varchar',
    'label'                      => 'Show in Subview',
    'input'                      => 'multiselect',
    'backend'                    => 'eav/entity_attribute_backend_array',
    'required'                   => 0,
    'user_defined'               => 0,
    'visible'                    => 1,
    'is_filterable'              => 1,
    'is_configurable'            => 0,
    'sort_order'                 => 14,
    'source'                     => 'ho_subviews/attribute_source_subview',
    'global'                     => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
    'group'                      => 'General',
    'used_in_product_listing'    => true,

As you can see I've used a custom source to fill the multi select field. Saving and loading the values works like a charm.

Filtering the collection can be done using: $collection->addAttributeToFilter('show_in_subview', array('finset'=> Mage::app()->getStore()->getCurrentSubviewId()));

The problem is that the collection doesn't use indexes since the column type isn't SET (http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set).

I noticed that when adding a normal multi-select field, the query is a join on the catalog_product_index_eav table which does use indexes and thus will be more performant.

I'm not exactly sure where to look to use this table as well. I'm guessing the backend class is responsible for that (that was the only difference between a manually created drop down field or the above). So eav/entity_attribute_backend_array doesn't use the catalog_product_index_eav table.

  • What is the default backend that is used for multiselect or isn't there any?
  • How can I extend eav/entity_attribute_backend_array so it uses the catalog_product_index_eav table?
  • Hey Paul, I actually haven't worked with custom product attributes a ton, so I'm not exactly sure what the reason is that some custom EAV attributes use the index table but the custom one you've defined isn't. It looks to me like the Mage_Eav_Model_Entity_Attribute_Backend_Array is imploding an array into CSV in beforeSave(). That would explain why it's probably resulting in a WHERE field IN (a,b,c) in the query somewhere which isn't going to be very performant. If you know of another EAV attribute that is using indexes nicely, maybe you can look at the backend that it's using...? Jul 19, 2013 at 13:48
  • It actually is resulting in (...) WHERE FIELD_IN_SET('5') which can search through a csv field, but doesn't use indexes it uses a table scan. Jul 20, 2013 at 11:10
  • Interesting. I'm not really sure the difference between IN() and FIELD_IN_SET(). I would imagine that neither will use indexes...? Jul 23, 2013 at 0:12
  • The difference is that normally a field have values like: a or b, in that case u use IN(). In this case the field is a multiselect field and the values become: a,b and b,c. Now if you want all the rows that contain b you can use FIELD_IN_SET() Jul 23, 2013 at 13:31
  • I think your attribute's source model needs to tell the EAV module how to be indexed. See Mage_Eav_Model_Entity_Attribute_Source_Table as a reference, noting the getFlatColums, getFlatColums, getFlatIndexes, getFlatUpdateSelect methods.
    – benmarks
    Jul 23, 2013 at 18:47

1 Answer 1


At the end I used a default multi-select attribute. With that solution we miss a bit of flexibility, but since the subviews don't change thát often it isn't a problem.

  • The default attribute uses indexes, which is fast
  • The default attribute is compatible with the SOLR backend

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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