How to index polymorphic table in rails with not unique "association_type" and "association_id"

Question

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

Answer 1

your first approach is the right one

add_index :votes, [:voteable_id, :voteable_type]

as long as you don't add :unique => true it will not be unique. And you shouldn’t make it unique, as this would result in only one vote per votable would be possible. As Tashos states in the comments.

The third approach would work too, and here you can use a unique constraint, so every user can only vote once per votable.