How can I have the Magento product flat tables (catalog_product_flat_1 etc) built with a MySQL index (key) on a specific column?

It seems that by default each flat table has these indexes:


I would like to add another key to that. My understanding is that I can't simply run an SQL migration to add the key, as it will be lost the next time the Magento indexer runs. I would also have to manually add migrations for each new flat table built for any new store scopes that might be added in future.

Is there a standard way of achieving this within Magento?

This has been quite difficult to search for due to Magento using the term "index" to mean its flat table design as opposed to MySQL indexes.

1 Answer 1


The Magento indexers will put a MySQL index (key) on a column in the flat tables if the attribute has used_for_sort_by set to true.

An example migration to get that set looks like this:


/* @var $installer Mage_Catalog_Model_Resource_Setup */
$installer = $this;



After that migration is applied and the Magento indexers are run, the foobar_attribute column in the flat tables will have a MySQL index on it.

However, depending on the frontend design of your installation, this may cause "foobar attribute" to appear in the "sort by" dropdown menu that customers use to sort the catalogue views. This needs addressing separately if it's a problem.

  • And is it not so that one can go to attributes and select "Used for Sorting in Product Listing" in attribute settings? plz confirm
    – snh_nl
    Commented Dec 28, 2017 at 11:24

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.