优化我的mysql语句！ -RAND()太慢 [英] Optimizing my mysql statement! - RAND() TOO SLOW

问题描述

因此，我有一个表，该表包含超过80,000条记录，该表称为系统.我还有另一个表，如下所示.

So I have a table with over 80,000 records, this one is called system. I also have another table called follows.

我需要我的语句从系统表中随机选择记录，该ID尚未在当前用户ID下的下表中列出.

I need my statement to randomly select records from the system table, where that id is not already listed within the follows table under the current userid.

这就是我所拥有的:

    SELECT system.id, 
           system.username, 
           system.password, 
           system.followed, 
           system.isvalid, 
           follows.userid, 
           follows.systemid
      FROM system
  LEFT JOIN follows ON system.id = follows.systemid
                   AND follows.userid = 2 
      WHERE system.followed = 0 
        AND system.isvalid = 1
        AND follows.systemid IS NULL
   ORDER BY RAND()
      LIMIT 200

现在它可以完美地工作了，除了它甚至需要一整分钟的时间才可以开始使用所选的记录来处理手头的作业.到这个时候，脚本通常会变成灰色，什么也没发生.

Now it wotks perfectly, except that it takes about a whole minute before it can even start processing the job at hand with the records it chosen. By this time the script usually times oout and nothing happens.

有人可以告诉我如何重做吗，所以做同样的想法，但是没有使用兰德的命令吗?这似乎使整个过程变慢了.

Can somebody show me how to rework this, so the same idea is done, but it is not using order by rand? This seems to slow things down a whole bunch.

谢谢！

推荐答案

我不确定是否有简单的解决方案来替换您的查询，这是一篇有关纠正此类问题的文章.

I am not sure there is a simple solution to replace your query, here is an article on correcting this type of issue.

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-从表/

这篇关于优化我的mysql语句！ -RAND()太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！