StackOverflower StackOverflower - 1 year ago 48
MySQL Question

Should I perform equations in my queries, or perform them locally and update the db

I am building a site with a Posts and a Votes table. Each vote points to a post and is either up or down

If I am wanting to sort the posts with a complex equation like this

def hotness(ups, downs, date):
s = ups - downs
order = log(max(abs(s), 1), 10)
sign = 1 if s > 0 else -1 if s < 0 else 0
seconds = epoch_seconds(date) - 1134028003
return round(sign * order + seconds / 45000, 7)

Would it be better to:

  1. Translate that equation to sql, perform a join with the votes, and perform the equation each time I want to fetch and sort a list of posts.

  2. Create a hotness column on each post, and update that each time a vote is cast on a post.

The problem I can see with option 1 is that it would add time/complexity to each query, and the problem I could see with option 2 is that there could be 1000's of votes a second meaning the post would need to constantly be updated.

What is the best way to do this?

Answer Source

What's more important: fast updates, or fast lookups? If you want the former, go with option 1; if you want the latter, go with option 2. Benchmark both approaches and see whether the resulting performance is tolerable.

Incidentally, with option 2 one can maintain the hotness column with a trigger:

    SIGN(ups-downs) * LOG10(GREATEST(ABS(ups-downs), 1))
  + (UNIX_TIMESTAMP( - 1134028003) / 45000
, 7);

Then one need merely do UPDATE posts SET ups = ups + 1 WHERE id = ? or whatever and hotness will automatically be updated.

If neither solution produces acceptable results, you should consider the extent to which you can tradeoff a "live" hotness value for one that is cached and updated only periodically. For example, rather than updating hotness in a trigger/on each vote, schedule an event to perform batch updates once every so often.