add_index(table_name, column_name, options = {})
Instance Public methods
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
1 | add_index(:suppliers, :name) |
generates:
1 | CREATE INDEX suppliers_name_index ON suppliers(name) |
Creating a unique index
1 | add_index(:accounts, [:branch_id, :party_id], unique: true) |
generates:
1 | CREATE UNIQUE INDEX accounts_branch_id_party_id_index ON accounts(branch_id, party_id) |
Creating a named index
1 | add_index(:accounts, [:branch_id, :party_id], unique: true, name: 'by_branch_party') |
generates:
1 | CREATE UNIQUE INDEX by_branch_party ON accounts(branch_id, party_id) |
Creating an index with specific key length
1 | add_index(:accounts, :name, name: 'by_name', length: 10) |
generates:
1 2 3 | CREATE INDEX by_name ON accounts(name(10))add_index(:accounts, [:name, :surname], name: 'by_name_surname', length: {name: 10, surname: 15}) |
generates:
1 | CREATE INDEX by_name_surname ON accounts(name(10), surname(15)) |
Note: SQLite doesn't support index length.
Creating an index with a sort order (desc or asc, asc is the default)
1 | add_index(:accounts, [:branch_id, :party_id, :surname], order: {branch_id: :desc, party_id: :asc}) |
generates:
1 | CREATE INDEX by_branch_desc_party ON accounts(branch_id DESC, party_id ASC, surname) |
Note: MySQL doesn't yet support index order (it accepts the syntax but ignores it).
Creating a partial index
1 | add_index(:accounts, [:branch_id, :party_id], unique: true, where: "active") |
generates:
1 | CREATE UNIQUE INDEX index_accounts_on_branch_id_and_party_id ON accounts(branch_id, party_id) WHERE active |
Creating an index with a specific method
1 | add_index(:developers, :name, using: 'btree') |
generates:
1 2 | CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQLCREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL |
Note: only supported by PostgreSQL and MySQL
Creating an index with a specific type
1 | add_index(:developers, :name, type: :fulltext) |
generates:
1 | CREATE FULLTEXT INDEX index_developers_on_name ON developers (name) -- MySQL |
Note: only supported by MySQL. Supported: :fulltext and
:spatial on MyISAM tables.
Please login to continue.