I have a polymorphic table called "Votes", where has votes from Answers and Questions.

Votes

user_id     voteable_id    voteable_type      value

   1             2            Answer            1
   2             2            Answer            1

In this case, the answer with id = 2 has two votes up.

The question is: How to index this table?

First approach:

add_index :votes, [:voteable_id, :voteable_type]

This will not work because duplicate key value will violates unique constraint

Second approach:

 add_index :votes, :voteable_id, 
 add_index :votes, :voteable_type

This one I guess will not have much performance because of the composite queries for id and type at the same time.

Third approach:

add_index :votes, [:user_id, :voteable_id, :voteable_type]

Is this last one a good one? Are three columns to be indexed too much?

Thanks

Edit
Report