How to Add Index Using Migration in Ruby on Rails

In Ruby on Rails, you add an index using a migration by calling add_index inside the migration file. This method takes the table name and column(s) to index, improving database query speed.

Syntax

The add_index method is used inside a migration to add an index to a database table. It requires the table name and the column or columns to index. You can also specify options like unique: true to enforce uniqueness.

• table_name: The name of the table where the index will be added.
• column_name(s): The column or array of columns to index.
• options: Optional settings like unique: true or name: 'custom_index_name'.

ruby

add_index :table_name, :column_name
add_index :table_name, [:column1, :column2], unique: true, name: 'custom_index_name'

Example

This example shows how to add an index on the email column of the users table to speed up lookups. It also demonstrates adding a unique index to ensure no duplicate emails.

ruby

class AddIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email, unique: true
  end
end

Output

== 20240601000000 AddIndexToUsersEmail: migrating ==============================
-- add_index(:users, :email, {:unique=>true})
   -> 0.0100s
== 20240601000000 AddIndexToUsersEmail: migrated (0.0101s) =====================

Common Pitfalls

Common mistakes when adding indexes include:

• Forgetting to specify the correct table or column name, causing migration errors.
• Not adding unique: true when uniqueness is required, leading to duplicate data.
• Adding indexes on columns that rarely get queried, which wastes database resources.
• Trying to add an index on a non-existent column, which causes migration failure.

ruby

class WrongAddIndex < ActiveRecord::Migration[7.0]
  def change
    # Wrong: column name typo
    # add_index :users, :emial

    # Correct:
    add_index :users, :email
  end
end

Quick Reference

Method	Description	Example
add_index	Adds an index to a table column	add_index :users, :email
add_index with unique	Adds a unique index to enforce uniqueness	add_index :users, :email, unique: true
add_index on multiple columns	Adds a composite index on multiple columns	add_index :orders, [:user_id, :status]
remove_index	Removes an index from a table	remove_index :users, :email

Key Takeaways

Use add_index in migrations to improve database query speed.

Specify the correct table and column names to avoid migration errors.

Use unique: true option to enforce unique values in a column.

Avoid adding indexes on columns that are rarely queried to save resources.

Always test migrations in development before applying to production.