add_index(table_name, column_name, options = {}) public

Adds a new index to the table. column_name can be a single Symbol, or an Array of Symbols.

The index will be named after the table and the first column name, unless you pass :name as an option.

When creating an index on multiple columns, the first column is used as a name for the index. For example, when you specify an index on two columns [:first, :last], the DBMS creates an index for both columns as well as an index for the first column :first. Using just the first name for this index makes sense, because you will never have to create a singular index with this name.

Creating a simple index
 add_index(:suppliers, :name)


 CREATE INDEX suppliers_name_index ON suppliers(name)
Creating a unique index
 add_index(:accounts, [:branch_id, :party_id], :unique => true)


 CREATE UNIQUE INDEX accounts_branch_id_party_id_index ON accounts(branch_id, party_id)
Creating a named index
 add_index(:accounts, [:branch_id, :party_id], :unique => true, :name => 'by_branch_party')


 CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id)
Creating an index with specific key length
 add_index(:accounts, :name, :name => 'by_name', :length => 10)


 CREATE INDEX by_name ON accounts(name(10))

 add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :length => {:name => 10, :surname => 15})


 CREATE INDEX by_name_surname ON accounts(name(10), surname(15))

Note: SQLite doesn’t support index length

Show source
Register or log in to add new notes.
June 6, 2009
8 thanks

add index with :quiet=>true option for indices that are possibly already added

# Allows you to specify indices to add in a migration that will only be created if they do not # already exist, or to remove indices only if they already exist with :quiet=>true module ActiveRecord::ConnectionAdapters::SchemaStatements

def add_index_with_quiet(table_name, column_names, options = {})
  quiet = options.delete(:quiet)
  add_index_without_quiet table_name, column_names, options
  raise unless quiet and $!.message =~ /^Mysql::Error: Duplicate key name/i
  puts "Failed to create index #{table_name} #{column_names.inspect} #{options.inspect}"
alias_method_chain :add_index, :quiet

def remove_index_with_quiet(table_name, column_names, options = {})
  quiet = options.delete(:quiet)
  raise "no options allowed for remove_index, except quiet with this hack #{__FILE__}:#{__LINE__}" unless options.empty?
  remove_index_without_quiet table_name, column_names
  raise unless quiet and $!.message =~ /^Mysql::Error: Can't DROP/i
  puts "Failed to drop index #{table_name} #{column_names.inspect}"
alias_method_chain :remove_index, :quiet


July 24, 2010
2 thanks

If your add_index is being ignored in your migration, see this

My add_index command was producing no change in my MySQL 5.0 database:

add_index :designations, [ :scope_type, :scope_id, :role_id, :user_id ], :unique => true

By just adding an index name, the problem was solved:

add_index :designations, [ :scope_type, :scope_id, :role_id, :user_id ], :unique => true, :name => 'my_index'

This happens when the autogenerated index name gets too long. For more info see:

July 10, 2008
1 thank

migration example

def self.up create_table :regs do |t|

t.column :login, :string, :limit=>'10'
t.column :pass, :string, :limit=>'10'
t.column :email, :string, :limit=>'20'
t.column :fio, :string, :limit=>'30'
t.column :born, :date
t.column :phone_code, :integer, :limit=>'3'
t.column :phone_post, :integer, :limit=>'7'
t.column :password, :string, :limit=>'20'
t.column :pass_when, :date
t.column :pass_who, :string,:limit=>'30'
t.column :wmid, :integer, :limit=>12
t.column :wmr, :integer, :limit=>12
t.column :wmz, :integer, :limit=>12

add_index :regs, [:login, :wmr, :wmz], :unique => true end

July 10, 2008
This note might be spam Show
December 9, 2010 - (<= v2.3.8)
0 thanks

Gotcha: index name must be a string, not a symbol

Using rails 2.3.8 I kept getting an exception when i tried:

add_index :widgets, [:colour, :weight], :name => :index_by_colour_weight

it’s solved by using:

add_index :widgets, [:colour, :weight], :name => 'index_by_colour_weight'