tutorialindexes

Tutorial :: Creating indexes on table columns

In our example of a member site, we might want to improve performance of looking up members by their username at a database level by adding an index.

We can also enforce at a database level that the username column we added to the members table in our second migration is unique.

Let's begin by creating a new migration file 004_member_indexes.cfc.

In our up function we use the addIndex function to create the index. Set the unique argument to true.

addIndex(table='members',columnNames='username',unique=true);

In our down function we want to remove this index using the removeIndex function. We could have specified the name of the index as an argument to addIndex but by default it will be the table name, followed by an underscore, followed by the first column name of the index.

removeIndex(table='members',indexName='members_username');

So our migration file looks like this:

<cfcomponent extends="plugins.dbmigrate.Migration" hint="unique member usernames">
  <cffunction name="up">
    <cfscript>
    addIndex(table='members',columnNames='username',unique=true);
    </cfscript>
  </cffunction>
  <cffunction name="down">
    <cfscript>
    removeIndex(table='members',indexName='members_username');
    </cfscript>
  </cffunction>
</cfcomponent>

Save your file and load up your application in a browser. Click on the DBMigrate link under Plugins in the CFWheels framework debugging footer, or if you already have it open, refresh the page. You should see that this new migration shows up in the available migrations.

Use the select box to choose this migration and click the go button. This will migrate the database to version 004 and provide feedback to say that it added the index.

Check your database to confirm.

Now try migrating the database back to version 003 and check the database again to confirm the index has been removed.

Thanks for viewing this tutorial. I hope it will grow over time to give you more examples.