Magento - Add index to table column using upgrade script

Question

Has any body tried adding index to a column in an already existing database table in magento way?

When I tried using

$table->addIndex('index_name', 'field_name', 'index_type'), it was not working.

Last I tried with normal ALTER TABLE query

$installer->run("ALTER TABLE table_name ADD INDEX index_name(field_name)");

and I got it working.

The question is what might be the problem in doing it with magento table DDL functions?

Answer 1

Magento allows you run SQL queries in two ways:

1. Using RAW sql queries
2. Using magento way

Using RAW SQL queries

You can run SQL queries directly. In your scenario,

<?php
$installer = $this;
$installer->startSetup();
$sql=<<<SQLTEXT
ALTER TABLE table_name ADD INDEX index_name(field_name);
SQLTEXT;

$installer->run($sql);
$installer->endSetup();

It will add index in your table directly.

Using magento way

Using magento's way is quite complicated. It's like,

<?php

$installer = $this;
$installer->startSetup();

$tableName = $installer->getTable('Module_name/Table_name');
// Check if the table already exists
if ($installer->getConnection()->isTableExists($tableName)) {
$table = $installer->getConnection();

$table->addIndex(
  $installer->getIdxName(
    'your_namespace/your_table',
    array(
      'column1',
      'column2',
      'column3',
    ),
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
  ),
  array(
    'column1',
    'column2',
    'column3',
  ),
  array('type' => Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE)
)
$installer->endSetup();
}

Here pay attention with

 $tableName = $installer->getTable('Module_name/Table_name'); 

You should add your module name and table name carefully. Other wise it's not work. For more information go here

And don't forgot to add below things in your config.xml to get authentication of your db

<global>
...
<resources>
      <modulename_setup>
        <setup>
          <module>Packagename_ModuleName</module>
        </setup>
        <connection>
          <use>core_setup</use>
        </connection>
      </modulename_setup>
      <modulename_write>
        <connection>
          <use>core_write</use>
        </connection>
      </modulename_write>
      <modulename_read>
        <connection>
          <use>core_read</use>
        </connection>
      </modulename_read>
    </resources>
...
</global>

That's it. Please comment here if you have any question.

Answer 2

addIndex is method of connection object, not table object. Something like below should do the trick. Check the source of Varien_Db_Adapter_Pdo_Mysql for all available methods of $installer->getConnection() object.

<?php 

$installer = $this;
$installer->startSetup();
if ($installer->tableExists($installer->getTable('module/table'))) {
    $installer->getConnection()
        ->addIndex(
            $installer->getTable('module/table'),
            $installer->getIdxName('module/table', array('column1', 'column2'), Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE),
            array('column1', 'column2'),
            Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
        );
}
$installer->endSetup();