Search code examples
mysqlalgorithmdatabase-designranking

How can I structure my database to order by a function dependent on time efficiently in MySQL?

I have a table with ID's and KPI's like this

+----+---------------------+------------+--------+
| id | created_at          | page_views | shares |
+----+---------------------+------------+--------+
|  1 | 2015-02-25 07:24:50 |         10 |      3 |
|  2 | 2015-04-22 13:48:46 |         40 |      1 |
|  3 | 2014-09-17 15:26:51 |         28 |      5 |
|  4 | 2014-08-09 16:27:48 |        149 |      2 |
|  5 | 2015-01-21 15:56:30 |          3 |      1 |
+----+---------------------+------------+--------+

I want to make a ranking algorithm along the lines of

SELECT id FROM pages ORDER BY (pages.page_views + pages.shares * 40) / (NOW() - created_at);

But I feel like this isn't scalable with increasing number of KPIs and entrees. Is there a way to structure my table to efficiently rank using KPIs with decreasing score as time goes on?

Solution

  • I would add a pageranking column and update it regularly (daily?) using a batch job.

    This will provide good search performance at the cost of using slightly out-of-date page rankings.