Popularity Algorithm - SQL / Django

Question

Hi folks,

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! ^^

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.