I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this weight is calculated by a few different factors.
- The number of connections a user has
- The number of connections a users friend has
Essentially a users weight is affected by their network and the weight of each of their friends. I need to average out the weight score of a users network and then use that to update a users self weight score. I would like it to be a living thing where the weight can change at any time if the network average changes for a user (but performance wise this might not be a good idea).
I was thinking once or twice per day maybe via a scheduled trigger, a users weighting score could be calculated. My question is, how can I best implement something like this keeping in mind if I have 100,000 users and or millions in future, the performance of updating that many rows. Am I thinking about this all wrong?
Thank you in advance.
Best Answer
How about using a scheduled job to take a snap-shot of your users' weight stats and influencing factors into a different (could be a temp-) table, then using those to calculate the new weight (into a different column of that same table). And then update your user table with the complete set of calculated weight stats.
You'd avoid all kinds of conflicts (from updating with modified data depending on order of execution) and execution time would not be an issue either (though it should not be too bad anway, from what you described), since you could run this as a low priority background process.