When to add what indexes in a table in Rails

• Always index foreign keys
• Always index columns you will order by
• All unique fields (to ensure uniqueness at a database level. Example migration: add_index :users, :email, unique: true)
• If you order by two things, or search by two things, for example: order by [a, b] or find where( a and b ), then you need a double index:

Concrete example:

If you have:

default_scope :order => 'photos.created_at DESC, photos.version DESC'

You should add:

add_index :photos, [:created_at, :version]

Note: An index takes up extra space on the disk and makes it slower to create and update each record, because it has to rebuild each index.

Credit:

https://tomafro.net/2009/08/using-indexes-in-rails-choosing-additional-indexes, rails - created_at when user for ordering, Should you add an Index to the table?, and the answers above.

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.

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.