I have a database doing a hackernews/reddit-esque approach to ranking.
I have a database of posts with the following columns (the time column could be INT or TIMESTAMP, whichever proves more performant).
id: INT
points: INT
time: TIMESTAMP/INT
I want to order by "score", where score is calculated like this:
Score = (P-1) / (T+2)^G
where, P = points of an item T = time since submission (in hours) G = Gravity, a constant (let's say 1.8)
So I compose a SQL query:
SELECT * FROM post ORDER BY (points - 1) / POW(((TIMESTAMPDIFF(HOURS, NOW(), time) + 2, 1.8)
Great, this works, but it's super slow because any indexes I might put will be ignored, since the values they index are part of a calculation.
I can put the points - 1 part of the query in a generated column, but I can't see a way to finagle the elapsed time component into something that's indexable. Am I just out of luck here?