traubisoda's avatar

Voting/point distribution system performance

Hi there! I'm working on a project, where users can upload images and videos to their profile, and other users can distribute points on them. I'd like to create detailed statistics about the point distributions (like which items are the most popular in a given time interval based on points).

So I came up with the following idea:

I store every distribution in a DB table (mySQL), where I store the voting user's id, the related item's id, and the amount of points given with time stamps.

My concerns:

I expect 300-500 users, who each will upload ~20 media items, and ~20k visitors, who will vote on these items. As visitors browse the website, point related data will be displayed very frequently, i.e. every time someone browses a user's media items, I have to display the sum of the points distributed on the given media, I display a user's top voters on it's profile page. Which seems like a huge database load for me. I don't know much about database performance, so I have no clue if my solution is decent or it will be a performance killer in production.

Should I use any "advanced" techniques, like caching and periodically update the point count on each item, or would that be an overkill? I would really appreciate any tip or advice on this topic!

Thanks in advance! :)

0 likes
2 replies
ohffs's avatar
ohffs
Best Answer
Level 50

I use Redis a lot for this kind of thing. Jeffrey has started a series about it luckily - worth checking out, especially the 'Trending Articles with Sorted Sets' one which might give you some ideas for your situation.

Please or to participate in this conversation.