add_index
![Very extensive documentation Importance_5](https://d2vfyqvduarcvs.cloudfront.net/images/importance_5.png?1349367920)
- 1.0.0 (0)
- 1.1.6 (18)
- 1.2.6 (0)
- 2.0.3 (0)
- 2.1.0 (1)
- 2.2.1 (0)
- 2.3.8 (19)
- 3.0.0 (0)
- 3.0.9 (-3)
- 3.1.0 (0)
- 3.2.1 (-2)
- 3.2.8 (0)
- 3.2.13 (0)
- 4.0.2 (38)
- 4.1.8 (0)
- 4.2.1 (0)
- 4.2.7 (3)
- 4.2.9 (0)
- 5.0.0.1 (0)
- 5.1.7 (0)
- 5.2.3 (32)
- 6.0.0 (17)
- 6.1.3.1 (11)
- 6.1.7.7 (0)
- 7.0.0 (-1)
- 7.1.3.2 (12)
- 7.1.3.4 (0)
- What's this?
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 column name(s), unless you pass :name as an option.
Creating a simple index
add_index(:suppliers, :name)
generates:
CREATE INDEX index_suppliers_on_name ON suppliers(name)
Creating a index which already exists
add_index(:suppliers, :name, if_not_exists: true)
generates:
CREATE INDEX IF NOT EXISTS index_suppliers_on_name ON suppliers(name)
Note: Not supported by MySQL.
Creating a unique index
add_index(:accounts, [:branch_id, :party_id], unique: true)
generates:
CREATE UNIQUE INDEX index_accounts_on_branch_id_and_party_id ON accounts(branch_id, party_id)
Creating a named index
add_index(:accounts, [:branch_id, :party_id], unique: true, name: 'by_branch_party')
generates:
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)
generates:
CREATE INDEX by_name ON accounts(name(10))
Creating an index with specific key lengths for multiple keys
add_index(:accounts, [:name, :surname], name: 'by_name_surname', length: {name: 10, surname: 15})
generates:
CREATE INDEX by_name_surname ON accounts(name(10), surname(15))
Note: only supported by MySQL
Creating an index with a sort order (desc or asc, asc is the default)
add_index(:accounts, [:branch_id, :party_id, :surname], name: 'by_branch_desc_party', order: {branch_id: :desc, party_id: :asc})
generates:
CREATE INDEX by_branch_desc_party ON accounts(branch_id DESC, party_id ASC, surname)
Note: MySQL only supports index order from 8.0.1 onwards (earlier versions accepted the syntax but ignored it).
Creating a partial index
add_index(:accounts, [:branch_id, :party_id], unique: true, where: "active")
generates:
CREATE UNIQUE INDEX index_accounts_on_branch_id_and_party_id ON accounts(branch_id, party_id) WHERE active
Note: Partial indexes are only supported for PostgreSQL and SQLite.
Creating an index that includes additional columns
add_index(:accounts, :branch_id, include: :party_id)
generates:
CREATE INDEX index_accounts_on_branch_id ON accounts USING btree(branch_id) INCLUDE (party_id)
Note: only supported by PostgreSQL.
Creating an index with a specific method
add_index(:developers, :name, using: 'btree')
generates:
CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQL CREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL
Note: only supported by PostgreSQL and MySQL
Creating an index with a specific operator class
add_index(:developers, :name, using: 'gist', opclass: :gist_trgm_ops) # CREATE INDEX developers_on_name ON developers USING gist (name gist_trgm_ops) -- PostgreSQL add_index(:developers, [:name, :city], using: 'gist', opclass: { city: :gist_trgm_ops }) # CREATE INDEX developers_on_name_and_city ON developers USING gist (name, city gist_trgm_ops) -- PostgreSQL add_index(:developers, [:name, :city], using: 'gist', opclass: :gist_trgm_ops) # CREATE INDEX developers_on_name_and_city ON developers USING gist (name gist_trgm_ops, city gist_trgm_ops) -- PostgreSQL
Note: only supported by PostgreSQL
Creating an index with a specific type
add_index(:developers, :name, type: :fulltext)
generates:
CREATE FULLTEXT INDEX index_developers_on_name ON developers (name) -- MySQL
Note: only supported by MySQL.
Creating an index with a specific algorithm
add_index(:developers, :name, algorithm: :concurrently) # CREATE INDEX CONCURRENTLY developers_on_name on developers (name)
Note: only supported by PostgreSQL.
Concurrently adding an index is not supported in a transaction.
For more information see the {“Transactional Migrations” section}[rdoc-ref:Migration].
![Default_avatar_30](https://www.gravatar.com/avatar/59436ecd4fe6ad7c34f67654d839f05f?size=30&default=http://apidock.com/images/default_avatar_30.png)
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 rescue raise unless quiet and $!.message =~ /^Mysql::Error: Duplicate key name/i puts "Failed to create index #{table_name} #{column_names.inspect} #{options.inspect}" end 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 rescue raise unless quiet and $!.message =~ /^Mysql::Error: Can't DROP/i puts "Failed to drop index #{table_name} #{column_names.inspect}" end alias_method_chain :remove_index, :quiet
end
![Default_avatar_30](https://www.gravatar.com/avatar/4cfa27623a7f68e3d297e786e2264d24?size=30&default=http://apidock.com/images/default_avatar_30.png)
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:
![Default_avatar_30](https://www.gravatar.com/avatar/f2100bf6d5dcd4b378fb3e43e4d4173e?size=30&default=http://apidock.com/images/default_avatar_30.png)
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 end
add_index :regs, [:login, :wmr, :wmz], :unique => true end
![Default_avatar_30](https://www.gravatar.com/avatar/23ff2b06aa26d1e953464cec53bc1523?size=30&default=http://apidock.com/images/default_avatar_30.png)
Adding index with other operator classes (PostgreSQL)
To perform on search by LIKE:
SQL Query:
SELECT users.* FROM users WHERE name LIKE 'Doug%';
Explain:
# Without index Seq Scan on users (cost=0.00..82183.32 rows=98524 width=418) Filter: ((name)::text ~~ 'Doug%'::text)
Adding index with operator class ‘varchar_pattern_ops’
add_index :users, :name, order: {name: :varchar_pattern_ops} execute 'ANALYZE users;'
New Explain:
# With index Bitmap Heap Scan on users (cost=2444.46..56020.97 rows=98524 width=418) Filter: ((name)::text ~~ 'Doug%'::text) -> Bitmap Index Scan on index_users_on_name (cost=0.00..2419.83 rows=75940 width=0) Index Cond: ((name)::text ~>=~ 'Doug'::text)
![Default_avatar_30](https://www.gravatar.com/avatar/f2100bf6d5dcd4b378fb3e43e4d4173e?size=30&default=http://apidock.com/images/default_avatar_30.png)
![Default_avatar_30](https://www.gravatar.com/avatar/eda85182ff398cbe4a4f70abf1b4c828?size=30&default=http://apidock.com/images/default_avatar_30.png)
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'