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

Question

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>

Answer 1

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