RAND() 查询和性能 [英] RAND() Query and Performance

问题描述

我正在尝试准备性能查询.我希望从下面的查询中删除 RAND() 并将其替换为性能更好的替代方案.有人有什么建议吗?

I'm trying to prepare a query for performance. I'm hoping to remove the RAND() from the query below and replace it with a better performing alternative. Does anybody have any suggestions?

SELECT video.*, 
       video.wins / video.loses AS win_loss_ratio
  FROM video
 WHERE video.videoid NOT IN (SELECT vidlog.videoid
                               FROM video AS vid, 
                                    video_log AS vidlog
                              WHERE vid.videoid = vidlog.videoid)
   AND video.round = 0
ORDER BY RAND(), win_loss_ratio DESC 
LIMIT 0, 2

谢谢！

推荐答案

在调用此查询的语言中，不要使用 RAND()，而是在 LIMIT 的偏移量中使用随机数.虽然这可能有两个问题您需要知道数据库中有多少项目，2. 只为其中一个项目产生一些随机的东西.另一种选择是完全删除 LIMIT 和 RAND() 并在返回查询时只选择随机项目(我认为这会更慢)

Instead of using the RAND() use a random number in the offset for the LIMIT in the language that is calling this query. That could have two problems though a. you need to know how many items are in the database, 2. only yield something random for one of the items though. Other option is to drop the LIMIT and RAND() altogether and just select random items when the query is returned (I would imagine this to be slower though)

您需要如此高的性能是否有真正的理由?对于这种查询，我很确定使用 RAND() 就足够了.

Is there a real reason why you need to have this very high in performance? I'm pretty sure using RAND() will be sufficient enough for this kind of query.

这篇关于RAND() 查询和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！