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.

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.

Answer 2

Indexing can be a tricky, subtle thing, but there are general rules that apply that can make determining which to use a lot easier.

The first thing to remember is that indexes can work in more than one way. An index on A, B, C also works for A, B and simply A, so you can design your indexes to be more versatile if you order them correctly. The phone book is indexed on Last Name, First Name, so you can look up people easily by their last name, or a combination of last name and first name. You cannot, however, look them up directly by their first name. You'd need a separate index for that. The same goes for phone number, which you would have to index as well.

With that in mind, there are many things that will dictate how you create indexes:

• If you have a belongs_to-has_many relationship pairing, you need to have an index on the foreign key used.
• If you order your records, and there is a large number of them that will be paginated, you should add that order column to the end of the index.
• If you have a has_many :through relationship, your join table should have a unique index on both properties involved in the join as a compound key.
• If you fetch a record directly using a unique identifier such as username or email, that should be a unique index.
• If you fetch sets of records from a has_many relationship using a scope, make sure there's an index that includes the has_many foreign key and the scope column in that order.

The goal with indexes is to eliminate the dreaded "table scan" or "file sort" operations that occur when your data is not indexed properly.

In simple terms, look at the queries being generated by your application and ensure that columns referenced in WHERE or HAVING conditions and ORDER BY clauses are represented in that order.