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)
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:
CREATE TRIGGER foo BEFORE UPDATE ON posts FOR EACH ROW SET NEW.hotness = ROUND( SIGN(ups-downs) * LOG10(GREATEST(ABS(ups-downs), 1)) + (UNIX_TIMESTAMP(NEW.date) - 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.