Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Popularity Algorithm - SQL / Django

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

like image 291
RadiantHex Avatar asked Mar 22 '10 23:03

RadiantHex


1 Answers

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.

like image 142
stefanw Avatar answered Oct 21 '22 09:10

stefanw