How to apply a ranking algorithm using PHP/MySQL?

I'm working on an algorithm that gives a score to some messages posted on a website. This score will be used to rank all the messages. If, for one message, the score is high, this message will be ranked above the other messages (that have a lower score) and vice versa. The parameters for this algorithm are the number of upvotes/downvotes and the number of seconds gone by since the message was posted.

I want to display the millions of messages on a webpage using PHP and MySQL. Of course I will use a paging system. Since one of the parameters for the algorithm is the number of seconds gone by since the message was posted, this score will change over the time. But I will need to update it. The only way for me to update the score of each message is to update it automatically with PHP when the client asks for the messages to be displayed, then call them with a MySQL request like that : SELECT * FROM messages ORDER BY score.

But because there are millions of messages, it would take a lot of time to update everything each time someone wants to see some messages.

How do I implement this in PHP ?

So basically, I'm asking how to rank messages (using a score) without having to calculate the score of each message before I call them (because it would take a lot of time) + because I'm gonna be using a paging system, only 20 or 30 messages will be selected at a time from the database.

Thank you very much

Solution

Create a field which holds miliseconds since 1970 when a post is created. then use the following:

select milisecondsSince1970 as t,votes as v,* from messages order by (v-a*(t-t0))

where:

"a" is your personal factor for giving desired weight to "t".
t0 is miliseocnds from an exact date which you consider it as start date rather than 1970.

This solution just works for you question as the criterion of score is "time". In other huge calculations , a periodic update on scores is suggestd.