CODEWITHSUNDEEP
mysqlsqlruby-on-railsdatabase
Miiller
Miiller

Reputation: 1063

Database design of performance focused reputation

Background:

I am trying to optimize a full Ajax driven Forum in RoR. As RoR is already not the optimal platform for a full ajax site, i am trying to optimize my sql requests and storage.

Issue:

The reputation for posts is based on simple likes/dislikes going from 0-100%, while primary only the last 100 votes should count PLUS 10% of the reputation of all other posts who refer/answer to that post. Now what is the most efficient way to store that value in my database for a fast read?

Tried solutions for Post.reputation:

a) reading all joins seperately on each request. That would be reading huge join tables and counting the joins. Does that create a big server load since it loads many entries or isn't that a problem since it is basically only 1 table?

b) not using joins at all, but storing the reputation sums in actual (+1 on like, +0.1 on sub-like) and potential (+1 on like or dislike, +0.1 on sub-like or sub-dislike). Then Post.reputation would be actual/potential. At the same time i would have to still use joins for users_posts to limit 1 vote per post. In my eyes this would be the solution with the best performance, but is there a way in implementing the 100 vote count limit with additional variables? Because it seems i pretty much dropped the information about the order of the votes, which would be important for that.

c) basically storing all joins as in a) but additionally storing the reputation value in the database for the DB read and calculating+writing it whenever a refering join is added. Is it a foul way of storing the same information multiple times in the DB?

Question:

Which solution would be the smartest storing that information in my database and accessing it quickly/often?

Upvotes: 0

Views: 120

Answers (1)

Deepak Kumar
Deepak Kumar

Reputation: 972

The best approach will be (c). Many times, in RDBMS, we do store redundant information as cache to increase performance.

Additional notes:

  • Ensure that the join table has an index on [post_id, id]. This will speedup selecting the 100th record from the join table.
  • Good place to do the updates is callback of the model of the join table. This will ensure that updates are within a transaction.
  • In Post's has_many definition specify :order to the criteria (most likely, id desc) that gives the newest user_post first. This will simplify other queries.

Let me know if you need some schematic code.

Upvotes: 1

Related Questions