By Tony


2009-12-01 00:27:24 8 Comments

I've read about a few alternatives to MySQL's ORDER BY RAND() function, but most of the alternatives apply only to where on a single random result is needed.

Does anyone have any idea how to optimize a query that returns multiple random results, such as this:

   SELECT u.id, 
          p.photo 
     FROM users u, profiles p 
    WHERE p.memberid = u.id 
      AND p.photo != '' 
      AND (u.ownership=1 OR u.stamp=1) 
 ORDER BY RAND() 
    LIMIT 18 

7 comments

@Adlaran 2016-11-24 07:18:56

It is not the fastest, but faster then common ORDER BY RAND() way:

ORDER BY RAND() is not so slow, when you use it to find only indexed column. You can take all your ids in one query like this:

SELECT id
FROM testTable
ORDER BY RAND();

to get a sequence of random ids, and JOIN the result to another query with other SELECT or WHERE parameters:

SELECT t.*
FROM testTable t
JOIN
    (SELECT id
    FROM `testTable`
    ORDER BY RAND()) AS z ON z.id= t.id   
WHERE t.isVisible = 1
LIMIT 100; 

in your case it would be:

SELECT u.id, p.photo 
FROM users u, profiles p 
JOIN
    (SELECT id
    FROM users
    ORDER BY RAND()) AS z ON z.id = u.id   
WHERE p.memberid = u.id 
  AND p.photo != '' 
  AND (u.ownership=1 OR u.stamp=1) 
LIMIT 18 

It's very blunt method and it can be not proper with very big tables, but still it's faster than common RAND(). I got 20 times faster execution time searching 3000 random rows in almost 400000.

@tonio 2016-11-03 09:47:19

Order by rand() is very slow on large tables,

I found the following workaround in a php script:

Select min(id) as min, max(id) as max from table;

Then do random in php

$rand = rand($min, $max);

Then

'Select * from table where id>'.$rand.' limit 1';

Seems to be quite fast....

@Neoaptt 2016-03-15 14:20:51

UPDATE 2016

This solution works best using an indexed column.

Here is a simple example of and optimized query bench marked with 100,000 rows.

OPTIMIZED: 300ms

SELECT 
    g.*
FROM
    table g
        JOIN
    (SELECT 
        id
    FROM
        table
    WHERE
        RAND() < (SELECT 
                ((4 / COUNT(*)) * 10)
            FROM
                table)
    ORDER BY RAND()
    LIMIT 4) AS z ON z.id= g.id

note about limit ammount: limit 4 and 4/count(*). The 4s need to be the same number. Changing how many you return doesn't effect the speed that much. Benchmark at limit 4 and limit 1000 are the same. Limit 10,000 took it up to 600ms

note about join: Randomizing just the id is faster than randomizing a whole row. Since it has to copy the entire row into memory then randomize it. The join can be any table that is linked to the subquery Its to prevent tablescans.

note where clause: The where count limits down the ammount of results that are being randomized. It takes a percentage of the results and sorts them rather than the whole table.

note sub query: The if doing joins and extra where clause conditions you need to put them both in the subquery and the subsubquery. To have an accurate count and pull back correct data.

UNOPTIMIZED: 1200ms

SELECT 
    g.*
FROM
    table g
ORDER BY RAND()
LIMIT 4

PROS

4x faster than order by rand(). This solution can work with any table with a indexed column.

CONS

It is a bit complex with complex queries. Need to maintain 2 code bases in the subqueries

@Cayde 6 2016-03-15 20:20:59

Very nice. I am going to be sure to use this.

@Mike Purcell 2017-01-27 21:33:57

Pulling a range of random ids could be even more useful if you take those ids and throw them into a caching layer for 10 seconds, then let the app select randomly from the ids in the caching layer.

@PethÅ‘ Jonatán 2015-02-15 18:35:41

Create a column or join to a select with random numbers (generated in for example php) and order by this column.

@Greg 2018-09-08 19:02:54

This is similar to XKCD's getRandomNumber. This will yield the same "random" results over and over, which is usually not what they're looking for.

@lawrenceshen 2014-09-17 10:04:59

The solution I am using is also posted in the link below: How can i optimize MySQL's ORDER BY RAND() function?

I am assuming your users table is going to be larger than your profiles table, if not then it's 1 to 1 cardinality.

If so, I would first do a random selection on user table before joining with profile table.

First do selection:

SELECT *
FROM users
WHERE users.ownership = 1 OR users.stamp = 1

Then from this pool, pick out random rows through calculated probability. If your table has M rows and you want to pick out N random rows, the probability of random selection should be N/M. Hence:

SELECT *
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Where N is $limitCount and M is the subquery that calculates the table row count. However, since we are working on probability, it is possible to have LESS than $limitCount of rows returned. Therefore we should multiply N by a factor to increase the random pool size.

i.e:

SELECT*
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

I usually set $factor = 2. You can set the factor to a lower value to further reduce the random pool size (e.g. 1.5).

At this point, we would have already limited a M size table down to roughly 2N size. From here we can do a JOIN then LIMIT.

SELECT * 
FROM
(
       SELECT *
        FROM
        (
            SELECT *
            FROM users
            WHERE users.ownership = 1 OR users.stamp = 1
        ) as U
        WHERE 
            rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)
) as randUser
JOIN profiles
ON randUser.id = profiles.memberid AND profiles.photo != ''
LIMIT $limitCount

On a large table, this query will outperform a normal ORDER by RAND() query.

Hope this helps!

@Joe T 2013-01-16 00:32:03

I ran into this today and was trying to use 'DISTINCT' along with JOINs, but was getting duplicates I assume because the RAND was making each JOINed row distinct. I muddled around a bit and found a solution that works, like this:

SELECT DISTINCT t.id, 
                t.photo 
       FROM (SELECT  u.id, 
                     p.photo,
                     RAND() as rand
                FROM users u, profiles p 
                 WHERE p.memberid = u.id 
                  AND p.photo != '' 
                  AND (u.ownership=1 OR u.stamp=1)
                ORDER BY rand) t
       LIMIT 18

@Ricardo Souza 2013-11-28 10:26:03

This seems the exact same thing MySql does when you use ORDER BY RAND().

@Joe T 2013-11-28 17:53:21

i tested it and if you have a rand value in your result set (as is done in OMG Ponies' solutions), DISTINCT becomes negated. So this was how I got around that.

@OMG Ponies 2009-12-01 02:51:36

Here's an alternative, but it is still based on using RAND():

  SELECT u.id, 
         p.photo,
         ROUND(RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

This is slightly more complex, but gave a better distribution of random_ind values:

  SELECT u.id, 
         p.photo,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) - 1 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

@Tony 2009-12-02 23:27:36

Outstanding OMG. Thank you so much. While my db is not huge, I already noticed a slight performance increase, and feel much more comfortable moving forward.

@MattBianco 2011-08-09 08:20:02

This answer helped me a lot, as most other "solutions" I've found rules out complex WHERE clauses. Thanks!

@Jürgen Paul 2012-02-20 02:28:13

@OMG can you please explain your code?

@zerkms 2012-07-12 03:25:42

How multiplying RAND() to a constant value can give better distribution?

@OMG Ponies 2012-07-12 03:44:44

@zerkms: I can't speak for MySQL's optimizer

@zerkms 2012-07-12 03:57:51

@OMG Ponies: Yep, but you advised that :-) So my question is: why ORDER BY RAND() is worse than ORDER BY RAND() * const?

@GordonM 2013-03-04 15:10:01

I just tried selecting 10 random records on an InnoDB table of slightly over half a million records, and I didn't see any significant performance gains over just using order by rand().

@Sebastian 2015-01-20 07:48:39

Still needs to create a RAND() value for each row, copy the whole data to a temp table and sort that.

@spencer7593 2015-06-29 00:35:23

These forms do not provide any optimization over ORDER BY RAND(). I just ran tests on a one million row table, to compare performance. Averaging the results of 5 runs (discarding the first run), a straight ORDER BY RAND() was actually 11.0% faster. (avg. 2.70 sec vs. 3.04 sec.).

@Neoaptt 2016-03-31 14:16:53

This doesn't speed up the selection, it makes it slower. Also, the fetch becomes slower because you have to fetch the random number along with the data you wanted. It takes up more RAM because it keeps the randomized numbers after the order by

Related Questions

Sponsored Content

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

12 Answered Questions

[SOLVED] When should I use cross apply over inner join?

7 Answered Questions

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 571509 View
  • 1530 Score
  • 7 Answer
  • Tags:   linq sql-order-by

6 Answered Questions

[SOLVED] How do I add indices to MySQL tables?

1 Answered Questions

Alternative to using order by rand ()?

  • 2019-03-02 08:00:12
  • Bobby Pratama
  • 43 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql sql

15 Answered Questions

[SOLVED] Select n random rows from SQL Server table

  • 2009-05-11 16:19:11
  • John M Gant
  • 340715 View
  • 293 Score
  • 15 Answer
  • Tags:   sql sql-server random

4 Answered Questions

[SOLVED] When does MySQL ORDER BY RAND() function order?

Sponsored Content