When to add what indexes in a table in Rails

Question

I have a question about Rails database.

• Should I add "index" to all the foreign keys like "xxx_id"?
• Should I add "index" to the automatically created "id" column?

• Should I add "index(unique)" to the automatically created "id" column?

• If I add index to two foreign keys at once (add_index (:users, [:category, :state_id]), what happens? How is this different from adding the index for each key?

  class CreateUsers < ActiveRecord::Migration
    def self.up
      create_table :users do |t|
        t.string :name
        t.integer :category_id 
        t.integer :state_id
        t.string :email
        t.boolean :activated
        t.timestamps
      end
    # Do I need this? Is it meaningless to add the index to the primary key?
    # If so, do I need :unique => true ?
    add_index :users, :id 
    # I don't think I need ":unique => true here", right?
    add_index :users, :category_id # Should I need this?
    add_index :users, :state_id # Should I need this?
    # Are the above the same as the following?
    add_index (:users, [:category, :state_id])
    end
  end
  

---

Great answer so far. Additional question.

• I should add "index with unique" for xxx_id, right?

Answer 1

Should I add "index" to all the foreign keys like "xxx_id"?

It would be better, because it accelerates the search in sorting in this column. And Foreign keys are something searched for a lot.

Since Version 5 of rails the index will be created automatically, for more information see here.

Should I add "index" to the automatically created "id" column?

No, this is already done by rails

Should I add "index(unique)" to the automatically created "id" column?

No, same as above

If I add index to two foreign keys at once (add_index (:users, [:category_id, :state_id]), what happens? How is this different from adding the index for each key?

Then the index is a combined index of the two columns. That doesn't make any sense, unless you want all entries for one category_id AND one state_id (It should be category_id not category) at the same time.

An Index like this would speed the following request up:

# rails 2
User.find(:all, :conditions => { :state_id => some_id, :category_id => some_other_id })

# rails 3
User.where(:state_id => some_id, :category_id => some_other_id)

Where

add_index :users, :category_id
add_index :users, :state_id

will speed up these requests:

# rails 2+3
User.find_by_category_id(some_id)
User.find_by_state_id(some_other_id)

# or
# rails 2
User.find(:all, :conditions => {:category_id => some_id})
User.find(:all, :conditions => {:state_id => some_other_id})

# rails 3
User.where(:category_id => some_id)
User.where(:state_id => some_other_id)

I should add "index with unique" for xxx_id, right?

No, because if you do this, only one user can be in one category, but the meaning of category is that you can put more many user into one category. In your User model you have something like this belongs_to :category and in your Category model something like has_many :users. If you have a has_many relationship the foreign_key field must not be unique!

For more detailed information on this you should take a look at tadman's great answer.