Magento: Add column index from upgrade scrip

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookEmail this to someone

During the last few weeks I’ve been having a lot of fun with my current project. Recently I had to invent SQL queries in order to fetch data from the database. “At the end of the day” I had a nice looking-query but it was really slow. The query was taking around 1.2 sec to execute but it was really simple and had to be executed in a production environment every 30 minutes. By our standards, we couldn’t allow such a slow query on a Magento production shop. In addition, I will say that the query was executed on a table where we had many insert and update queries while the customer was interacting with the Magento frontend.

After a short investigation I found out that adding and index to a column that I was using in the where clause reduced execution time from 1.2 sec to 0.02 sec. The good news was that the table to which I wanted to add the index was part of our own module (not part of a core or community module) and I had the freedom to add this index without doing any “harm”.

In order to achieve the task I used the Magento upgrade script. If you are interested in trying the same in your own project, then you can take a look at the following example upgrade script:

/** @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

$installer->getConnection()
    ->addIndex(
        $installer->getTable('my_example_module/my_table'),
        'IDX_MY_EXAMPLE_COLUMN',
        'my_example_column'
    );

$installer->endSetup();

It’s important to note that the function addIndex is available from Magento CE 1.6 and if you are using and older version, you just have to change the function name from addIndex to addKey.

A bit more explanation:

  • $installer->getTable(‘my_example_module/my_table’) – returns the table name on which I want to create the index
  • IDX_MY_EXAMPLE_COLUMN – is the index name
  • my_example_column – is the column name on which I want to add the index

What about the index type?

* It’s not obvious, but the function addIndex has more than 3 parameters. The 4th one is $indexType and has a default value ‘index’. This means that if you leave the 4th parameter then by default your index will be of the INDEX type

The supported values of $indexType are:

  • primary
  • unique
  • index
  • fulltext

If you want to create a unique index, then you just have to call the function addIndex in the following way:

/** @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

$installer->getConnection()
    ->addIndex(
        $installer->getTable('my_example_module/my_table'),
        'IDX_MY_EXAMPLE_COLUMN',
        'my_example_column',
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
    );

$installer->endSetup();

What about older Magento versions?

In case you are using a version of Magento older than Magento CE 1.6, then you should use the function addKey and the parameter $indexType can’t be taken from the constants from the interface Varien_Db_Adapter_Interface. This means that you should just type the index type as shown in the following example:

/** @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

$installer->getConnection()
    ->addKey(
        $installer->getTable('my_example_module/my_table'),
        'IDX_MY_EXAMPLE_COLUMN',
        'my_example_column',
        'unique'
    );

$installer->endSetup();

In case you are interested to see how the function addIndex works, then take a look at the class Varien_Db_Adapter_Pdo_Mysql . Also, if you want to know more about the install / upgrade scripts in Magento, I recommend this article by Vinai Kopp – Magento Setup Scripts

Thanks to Armin Beširović (a friend and a great Magento developer) for asking me to polish up this article and explain in details about the index types.

Your thoughts / questions?

Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookEmail this to someone

Tsvetan Stoychev

Tsvetan aka. Cecko is the founder of Cecko's Lab. He is Magento addicted since Magento CE 1.2.1.2 and has worked on over 30 Magento projects. At the moment he is in charge to take care about the money flow of the company, to keep constant communication with the clients and to keep the people in the office busy.

More Posts

Follow Me:
TwitterLinkedIn