Popularity Algorithm - SQL / Django

Question

I searched for popularity algorithms used on sites like 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 done simple ordering in SQL, I wonder how I should deal with such ordering.

Should it be used to define a table or can I build SQL with ordering in a formula (without any difficulty)?

I also wonder if in different cases you can use several sequencing algorithms without affecting performance issues.

---

I am using Django and PostgreSQL.

Help would be greatly appreciated! ^^

Answer 1

You should cache your popularity rating in a separate column and update it when the base values ​​change. You must also configure the database index in this column. If you also cache the result of your most common queries, you have taken the most effective measures to fulfill your popularity queries.