CODEWITHSUNDEEP
ruby-on-rails
LearningRoR
LearningRoR

Reputation: 27192

Best way to add_index to database

I have the following two migrations already in my database:

When I created Prices:

class CreatePrices < ActiveRecord::Migration
  def self.up
    create_table :prices do |t|
      t.string :price_name
      t.decimal :price
      t.date :date

      t.timestamps
    end
    # add_index :prices (not added)
  end

  def self.down
    drop_table :prices
  end
end

and when I added a user_id to Prices:

class AddUserIdToPrices < ActiveRecord::Migration
  def self.up
    add_column :prices, :user_id, :integer
  end
  # add_index :user_id (not added)
end

  def self.down
    remove_column :prices, :user_id
  end
end

Is there a way from the command line to add prices and user_id to index? I looked at this question and still was confused on how to go about adding indexes and the parts where I put "not added" seem like they would be error-prone because they were earlier migrations.

My question is, what is the best way for me to add indexing to prices and user_id?

Thank you for the help!

Upvotes: 28

Views: 33110

Answers (3)

Mikhail Nikalyukin
Mikhail Nikalyukin

Reputation: 11967

I think one extra migration fits well:

class AddIndexes < ActiveRecord::Migration

  def self.up
    add_index :prices, :user_id
    add_index :prices, :price
  end

  def self.down
    remove_index :prices, :user_id
    remove_index :prices, :price
  end

end

Or you can use change syntax with newer versions of rails, look at DonamiteIsTnt comment for details:

class AddIndexes < ActiveRecord::Migration

  def change
    add_index :prices, :user_id
    add_index :prices, :price
  end

end

Upvotes: 57

Erik Peterson
Erik Peterson

Reputation: 4311

Once an app is in production, the intent is that migrations will be applied once.

If you're still developing your app, you can always add them as you've noted followed by a rake db:migrate:reset this will wipe your database and re-create it.

Otherwise, create a new migration rails g migration add_user_id_index.

class AddUserIdIndex < ActiveRecord::Migration
  def self.up
    add_index :prices, :user_id
  end

  def self.down
    remove_index :prices, :user_id
  end
end

FWIW, add_index :prices doesn't make sense. Indexes are per-column, not per-table.

You can always manually create indexes by logging into your database.

CREATE INDEX prices__user_id__idx ON prices (user_id);

Upvotes: 7

Marcel Jackwerth
Marcel Jackwerth

Reputation: 54762

Simple solution:

  • create a new migration
  • add the indexes there (they don't need to be in the older migrations)
  • run the migrations

Upvotes: 4

Related Questions