mysql - 在 Order By 中使用 MATH 对性能有好处吗？

Question

我只有一个问题:假设我们有一个包含 1.000.000 行的表 (activity_feed)，一个包含请求 feed 的用户和他将看到的事件之间关系的表 (activity_feed_per_user)，以及包含一些内容的表来自事件的统计信息全部由应用程序获取。

如果我必须按排名对结果进行排序(这甚至取决于时间，因此每秒都会变化)，这个查询好用吗？或者根本就不是？

EXPLAIN EXTENDED SELECT feed.activity_id, feed.body, counter.comments, counter.likes, user.username,
(0.25 * (
        (1 / TIMESTAMPDIFF(MINUTE,feed.datetime,now()) ) +
        ( 1 - (1 / (((comments + 1)* 1) + ((likes + 1) * 0.5))) ) +
        activity_type.peso +
        user_affinity.affinity
    )) as ranking
FROM activity_feed_per_user as feed_user
INNER JOIN activity_feed as feed ON feed_user.activity_id = feed.activity_id
INNER JOIN activity_type ON feed.activity_type = activity_type.activity_type_id
INNER JOIN activity_social_counter as counter ON feed_user.activity_id = counter.activity_id
INNER JOIN user_info as user ON user.user_id = feed.user_id
INNER JOIN user_affinity ON feed.user_id = user_affinity.user_related AND user_affinity.user_id = '1'
WHERE feed_user.user_id = '1' 
ORDER BY ranking DESC

这就是解释

1 | SIMPLE | user_affinity | ref | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using temporary; Using filesor
1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 |db.user_affinity.user_related | 1 | 100.00
1 | SIMPLE | feed_user | ref | PRIMARY | PRIMARY | 4 | const | 9 | 100.00 | Using index
1 | SIMPLE | feed | eq_refvPRIMARY,activity_type,user_idvPRIMARY | 4 | db.feed_user.activity_id | 1 | 100.00 | Using where
1 | SIMPLE | activity_type | eq_ref | PRIMARY | PRIMARY | 1 | db.feed.activity_type | 1 | 100.00
1 | SIMPLE | counter | eq_ref | PRIMARY | PRIMARY | 4 | db.feed_user.activity_id | 1 | 100.00

Answer 1

ORDER BY 对没有索引的值必须查看查询生成的每一行，因此您的建议可能会损害返回巨大结果集的查询的性能。