Where is the "CREATE TABLE" definition for catalog_product_flat* tables?

Question

I'm looking to add indexes the table to it to make lookups faster. If you manually add an index in MySQL, it will disappear at the next Magento "reindexing" event.

Answer 1

This happens here \Mage_Catalog_Model_Resource_Product_Flat_Indexer::prepareFlatTable

The table is built with magentos DDL $table = $adapter->newTable($tableName);

$table->addColumn(...);
$table->addIndex(...);
$table->addForeignKey();
$table->setComment("Catalog Product Flat (Store {$storeId})");
$adapter->createTable($table);

---

And if you are looking for a nice event, I think catalog_product_flat_rebuild should do

//\Mage_Catalog_Model_Resource_Product_Flat_Indexer::updateEventAttributes
public function updateEventAttributes($storeId = null)
{
    Mage::dispatchEvent('catalog_product_flat_rebuild', array(
        'store_id' => $storeId,
        'table'    => $this->getFlatTableName($storeId)
    ));
}

This method is called in

public function rebuild($store = null)
{
    // ...
    $this->prepareFlatTable($storeId);
    $this->cleanNonWebsiteProducts($storeId);
    $this->updateStaticAttributes($storeId);
    $this->updateEavAttributes($storeId);
    $this->updateEventAttributes($storeId);
    // ...

But I only read code, so please test, whether this works as expected :-)