ORDER BY RAND() function taking long time to execute in mysql

Question

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

see this article http://explainextended.com/2009/03/01/selecting-random-rows/ — Girish, Oct 11 '14 at 10:48
Did you time the query without `ORDER BY` on a "cold" server? 0.0009 sec sounds like getting the result from the cache. — Sergey Kalinichenko, Oct 11 '14 at 10:48

score 1 · Accepted Answer · edited May 23 '17 at 11:58

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

score 1 · Answer 2 · answered Oct 11 '14 at 11:09

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

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

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

edited May 23 '17 at 12:22

Community

1
1

answered Oct 11 '14 at 10:50

andreas

1
1

chiastic-security · Answer 4 · 2014-10-11T10:59:03.433

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).

ORDER BY RAND() function taking long time to execute in mysql

4 Answers4

Linked