0

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.

1
+50

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.

| improve this answer | |
  • Would you just continue to update the temp table on a daily basis for this approach? Would the update query for the non-temp table then cause any kind of performance delays going from the temp table to actual users table? And if so, how would this approach work if the database was shared across multiple database instances? If for example I have 10 million users (best case scenario) split across 4 tables at 2.5 million each, how would I go about implementing a temp table solution with multiple tables? – Dwayne Charrington Mar 12 '14 at 2:16
  • With a temp table approach you would normally use a stored procedure. I.e. the table would be created by the procedure to be used during its run and then dropped again. In your scenario, you would ideally pull the relevant data from all 4 tables into a single temp table (in the context of your stored procedure, that covers the whole process), compute new weights on the data in that temp table, and only at the end of your procedure you'd update from that temp table to your 4 live tables (then drop it). About temp tables see for example: linuxplanet.com/linuxplanet/tutorials/6889/1 – Sascha Rambeaud Mar 12 '14 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.