MySQL Popularity Ranking Algorithm
2010/12/24 (156 words)

Calculating the popularity of a page or article is something that usually comes up as a list of requirements for any social website. Essentially you want to display the post popular items/articles in some form of list but have them weighted by how old they are. Thankfully its pretty easy to do MySQL.


The above produces a number which you can then sort on. It is based on the [Hacker News algorithm][1] and works well for items which change hourly. By removing one of the /60 you should get something which ranks based on days rather then hours. A full example is listed below,

select *, ((table.popularity-1)/power(((unix_timestamp(NOW())-unix_timestamp(table.datetime))/60)/60,1.8)) as rank from table order by rank desc

<del>As a live example I added it to the following website, <a href=""></a> which now uses the above ranking to display news items.</del> This website is now offline as I have moved all my focus over to