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.