ORDER BY RAND（）函数需要很长时间才能在mysql中执行[复制]

问题描述：

This question already has an answer here:

How can i optimize MySQL's ORDER BY RAND() function? 8 answers
MySQL select 10 random rows from 600K rows fast 24 answers

I have to use RAND function in MYSQL query. If I use this function in sql query then this take near about 0.7962 sec. But if I use it without then this work fine with 0.0009 sec. How I can make sql query faster with RAND function.

MY QUERY

  SELECT 
         posts.ID,
         posts.post_content, 
         posts.post_title, 
         posts.post_date, 
         posts.post_name 
    FROM posts 
         WHERE posts.post_type = 'post' 
         AND posts.post_status = 'publish' 
    ORDER BY RAND() LIMIT 0, 24

</div>

此问题已经存在这里有一个答案： p>

如何优化MySQL的ORDER BY RAND（）函数？ 8 答案 span> li>
MySQL快速从600K行中选择10个随机行
24答案\ r span> li> ul> div>
我必须在MYSQL查询中使用RAND函数代码>。如果我在sql查询中使用此函数，那么这需要大约0.7962秒。但如果我没有使用它，那么这项工作可以很好地用0.0009秒。如何使用RAND函数更快地进行SQL查询。 p>
MY QUERY p>
SELECT posts.ID， posts.post_content， posts.post_title， posts。 post_date， posts.post_name FROM posts WHERE posts.post_type ='post' AND posts.post_status ='publish' ORDER BY RAND（）LIMIT 0,24 code> pre> div>

答

I go the solution.

 SELECT p1.ID, p1.post_content, p1.post_title, p1.post_date, p1.post_name
     FROM posts as p1 JOIN
          (SELECT CEIL(RAND() *
                 (SELECT MAX(ID)
                    FROM posts)) AS id)
          AS p2
  WHERE p1.ID >= p2.id
  ORDER BY p1.ID ASC
  LIMIT 0, 24

This is faster than my query.

MySQL select 10 random rows from 600K rows fast

Here is the solution.

Thanks

答

rand() has some performance "issues" - there are some suggestions discussed here: How can i optimize MySQL's ORDER BY RAND() function?

答

This is very slow because you're allocating a random value for every row in the table, then sorting the entire table, then throwing most of it away. You'd be much better off:

Retrieving all post IDs;
Randomly selecting 25 of them in php;
Querying the database for those rows.

This will run in linear time; at the moment it's O(n log n).

This is by far the best solution I've seen that allows for an uneven distribution of IDs. You can do it faster if your IDs are contiguous (in other words, if you'll never ever delete any rows).

答

see this link : http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

ORDER BY RAND（）函数需要很长时间才能在mysql中执行[复制]

相关推荐