Here is the Reddit's hot algorithm:
cpdef double _hot(long ups, long downs, double date): """The hot formula. Should match the equivalent function in postgres.""" s = score(ups, downs) order = log10(max(abs(s), 1)) if s > 0: sign = 1 elif s < 0: sign = -1 else: sign = 0 seconds = date - 1134028003 return round(order + sign * seconds / 45000, 7)
My brain says hotness of a post should be calculated on every request because old posts should have a low hotness score.
I implemented this function in MySql but as you might guess, it's slow. Very slow even indexes are there.
And then I think to update the hotness field of the post on every upvote or downvote of the post. Let's assume we have a post just posted now and has 100 upvote and 1 downvote and it's hotness is 78 (example). But it doesn't get any votes for 3 days. After 3 days a new post appears and gets 100 upvotes and 1 downvote and has 78 score. If I'm updating hotness score on votes, the 3 days old post and the new post will be show together. But in theory, the 3 days old post shouldn't even show up in front page.
What is the most viable method to implement for this situation?