1

I've read about how indexes applied to foreign key fields are important for application speed performance, but I've also read contradictory statements about how to set these up. In the structure shown in my model and migration files below:

class Owner < ApplicationRecord
  has_many :dogs
end

class Dog < ApplicationRecord
  belongs_to :owner
end

create_table :owners do |t|
  t.string :full_name
  t.string :address
  t.timestamps
end

create_table :dogs do |t|
  t.string :name
  t.string :age
  t.string :breed
  t.references :owner, foreign_key: true
  t.timestamps
end

On the line where I declare my foreign key field:

t.references :owner, foreign_key: true

If I leave it like this, will Rails automatically create a database index for me, or do I need to add one manually by modifying the line to display the code below?

t.references :owner, foreign_key: true, index: true

Or if I do need to add the index manually, do I instead need to declare it with the add_index method in a separate block of code? In which case, what is the point of index: true?

I'm using Rails 5.1.4

C. Ball
  • 643
  • 1
  • 8
  • 18

1 Answers1

4

yes, if you use references the index is already created. To test this out:

  1. rails g model Foo

  2. rails g model Bar foo:references and look at the generated migration:

    class CreateBars < ActiveRecord::Migration[5.1]
  def change
    create_table :bars do |t|
      t.references :foo, foreign_key: true

      t.timestamps
    end
  end
end

  3. rake db:migrate

  4. Look at db/schema.rb:

    ActiveRecord::Schema.define(version: 20180201075841) do

  create_table "bars", force: :cascade do |t|
    t.integer "foo_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["foo_id"], name: "index_bars_on_foo_id"
  end

  create_table "foos", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

You can see that it has the line t.index ["foo_id"], name: "index_bars_on_foo_id" which shows that it's been indexed.

Don't know what the point of index: true is if that's the default behavior, but just throwing out an idea here, maybe the option is there so you can disable it

max pleaner
  • 26,189
  • 9
  • 66
  • 118
  • Thank you very much. Do you know how I would go about adding an index to a model that's already been created by a generate statement, but was generated without specifying a references field? Would it happen automatically if I just type a line like this: " t.references :foo, foreign_key: true "? – C. Ball Feb 01 '18 at 08:53
  • @C.Ball see https://stackoverflow.com/questions/15881749/rails-adding-an-index-after-adding-column – max pleaner Feb 01 '18 at 17:05