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

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

Answers


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.


Need Your Help

Bootstrap 4 - Responsive cards in card-columns

html css twitter-bootstrap twitter-bootstrap-4 bootstrap-4

I'm playing with Bootstrap 4 and I can't find a solution to add responsiveness to cards while in a div with class="card-columns" (this class applies a Masonry-like effect to the cards inside the div

Slow App with Background image

android image background bitmap android-canvas

I am making a small game for android. It is working smoothly when its working on a white background but when i want to put a background image, its becoming slower and slower. First i tried to add i...