Add a MySQL index key to a column in Magento 1 flat tables

Question

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:

PRIMARY KEY (`entity_id`), KEY `IDX_CATALOG_PRODUCT_FLAT_2_TMP_INDEXER_TYPE_ID` (`type_id`), KEY `IDX_CATALOG_PRODUCT_FLAT_2_TMP_INDEXER_ATTRIBUTE_SET_ID` (`attribute_set_id`), KEY `IDX_CATALOG_PRODUCT_FLAT_2_TMP_INDEXER_NAME` (`name`), KEY `IDX_CATALOG_PRODUCT_FLAT_2_TMP_INDEXER_PRICE` (`price`), KEY `IDX_CATALOG_PRODUCT_FLAT_2_TMP_INDEXER_SKU` (`sku`),

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.

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:

<?php

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

$installer->updateAttribute(
    Mage_Catalog_Model_Product::ENTITY,
    'foobar_attribute',
    'used_for_sort_by',
    1
);

$installer->endSetup();

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.