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.