Sql流行度算法加权得分

Question

我正在实现一个算法，根据他的喜欢和不喜欢，返回当前的热门帖子。

为此，我将每个帖子的所有喜欢(1)和不喜欢(-1)相加以得到他的分数，但每个喜欢/不喜欢都是加权的:最新的，最重的。例如，当一个用户点赞一个帖子时，他的点赞权重为1。1天后，它的权重为0.95(如果是不喜欢，则为-0.95)，2天后，权重为0.90，以此类推……21 d后最低为0.01。(PS:这些都是近似值)

我的表格是这样制作的:

Posts表

id | Title                 | user_id | ...
-------------------------------------------
1  | Random post           | 10      | ...
2  | Another post          | 36      | ...
n  | ...                   | n       | ...

<<p> 喜欢表/em>

id | vote | post_id | user_id | created
----------------------------------------
1  | 1    | 2       | 10      | 2014-08-18 15:34:20
2  | -1   | 1       | 24      | 2014-08-15 18:54:12
3  | 1    | 2       | 54      | 2014-08-17 21:12:48 

这是我目前使用的SQL查询，它做的工作

SELECT Post.*, Like.*, 
SUM(Like.vote * 
    (1 - IF((TIMESTAMPDIFF(MINUTE, Like.created, NOW()) / 60 / 24) / 21 > 0.99, 0.99, (TIMESTAMPDIFF(MINUTE, Like.created, NOW()) / 60 / 24) / 21))
   ) AS score 
FROM posts Post 
LEFT JOIN likes Like ON (Post.id = Like.post_id) 
GROUP BY Post.id
ORDER BY score DESC

PS:我直接使用TIMESTAMPDIFF与MINUTE而不是DAY，因为我正在计算自己的日子，否则它会返回给我一个整数，我想要一个浮点值，以便逐渐衰减，而不是每天。因此，TIMESTAMPDIFF(MINUTE, Like.created, NOW())/60/24只给出了自类似创建以来经过的天数和小数部分。

以下是我的问题:

1. 看看IF(expr1, expr2, expr3)部分:这是必要的，以便为类似的重量设置最小值，所以它不会低于0.01，变成负的(所以类似的，甚至更老的仍然有一点重量)。但我计算的是2倍同样的东西:exp 1等于exp 2。有没有办法避免这个重复的表达式?
2. 我打算缓存这个查询并每5分钟更新一次，因为我认为它将在一个大的Post和Like表上相当沉重。缓存真的有必要吗?我的目标是在一个有5万个条目的表上运行这个查询，并且对于每个200个相关的喜欢(这使得一个10万个条目的Like表)。
3. 我应该在Like表为post_id创建索引吗?

谢谢你!

EDIT:想象一个Post可以有多个标签，每个标签可以属于多个帖子。如果我想得到一个标签或多个标签的热门帖子，我不能缓存每个查询;因为有很多可能的查询。

查询是否仍然可行?

编辑最终解决方案:我终于做了一些测试。我创建了一个表的帖子，有30万个条目，喜欢25万个条目。在没有索引的情况下，查询非常长(超时> 10mn)，但是在Post上有索引。id(primary)， Like.id(primary)和Like。Post_id用了~0.5s

所以我没有缓存数据，也没有使用更新每5mn。如果表继续增长，这仍然是可能的解决方案(超过15是不可接受的)。

Answer 1

2:我打算缓存这个查询并每5分钟更新一次，因为我认为它对一个大的Post和Like表来说会很重。缓存真的有必要吗?我的目标是在一个有50 000个条目的表上运行这个查询，并且对于每个200个相关的喜欢(这使得一个10 000 000个条目的喜欢表)。

10000和50000在当前硬件上被认为是很小的。使用这些表大小，您可能不需要任何缓存，除非查询将每秒运行几次。无论如何，在决定使用缓存之前，我会做一个性能测试。

3:我应该为post_id在Like表中创建索引吗?对于创建?

我将创建一个索引(post_id, created, vote)。这样，查询就可以从索引中获得所有信息，而不需要读取表。

编辑(回应评论):

一个额外的索引会稍微减慢插入/更新的速度。最后，您选择的路径将决定您在CPU/RAM/磁盘I/O方面所需的特性。如果您有足够的内存用于DB，以便您期望整个Like表都缓存在RAM中，那么您最好只在post_id上创建索引。

就总负载而言，您需要考虑insert和select之间的比率以及有或没有索引的插入和选择的相对成本。我的直觉是总负荷会随着指数的下降而降低。

关于并发(同时选择和插入)的问题。发生的情况取决于隔离级别。一般建议是使插入/更新尽可能短。如果你不做insert开始和commit之间不必要的事情，你应该没事。