Popularity Algorithm - SQL / Django

Question

I've been looking into popularity algorithms used on sites such as Reddit, Digg and even Stackoverflow.

Reddit algorithm:

t = (time of entry post) - (Dec 8, 2005)
x = upvotes - downvotes

y = {1 if x > 0, 0 if x = 0, -1 if x < 0)
z = {1 if x < 0, otherwise x}

log(z) + (y * t)/45000

---

I have always performed simple ordering within SQL, I'm wondering how I should deal with such ordering.

Should it be used to define a table, or could I build an SQL with the ordering within the formula (without hindering performance)?

I am also wondering, if it is possible to use multiple ordering algorithms in different occasions, without incurring into performance problems.

---

I'm using Django and PostgreSQL.

Help would be much appreciated! ^^

Ruff

Answer 1

You should cache your popularity rating in an own column and update it when the underlying values change. You should also setup a database index on that column. If you then also cache the result of your most common queries, you took the most effective measures for the performance of your popularity queries.

Cerracchio

@Cerracchio I had to read your answer a second time. Thanks stefan, I guess this is the way to go. Ordering or setting through SQL would just be a no no o way too complicated. :) – Ruff