Return Random Records in MySQL

Discussion

1. Chris Coyier

   You are just a random-useful-tip generating MACHINE =). This one will be a useful reference especially, just had a reader ask about this recently.
2. david

   Thanks Chris!
3. Stefan Reuter

   I doubt this is a good idea. Try it on a really big table (like the example products table suggests) and you will soon notice the performace problems. The query not only requires to examine all rows (full table scan) but also to sort them.
   You can also use the “explain” keyword to see what MySQL does with your query.
4. david

   For a really big table, it would probably be best to:

   1. Make a query to the database to obtain all ids.
   2. Place them into one array.
   3. Randomly select (using PHP) {x} number of id’s from the array.
   4. Build a string that will be used in another query, e.g. “WHERE id = 2 OR id = 32847 OR id = 94384 OR…”
   5. Execute the query, grab information.
5. Steve

   If you’re worried about a table scan, rather than fetch every ID into PHP for another round trip, surely it would be better to use a subquery…

   SELECT *
   FROM products
   WHERE id IN(
   SELECT id
   FROM products
   ORDER BY RAND()
   LIMIT 4
   )

   If id is indexed I’m pretty sure it just has to read the index.
6. david

   You’re correct Steve.

   A colleague challenged me to find a way to ORDER the records by RAND() and then by title — that’s why my mind was in PHP mode.

   Awesome contribution — thank you!
7. Stefan Reuter

   If id is indexed and you sort by RAND() that won’t help you too much.
   One solution I could think is to do a
   SELECT COUNT(*) FROM products
   generate a random number between 0 and the number of rows and use
   SELECT * FROM products LIMIT 1 OFFSET $ramdom_number
• Darnell

  This is the fastest solution for a large table.
8. Steve

   If id is indexed, I’m pretty sure reading just the index is faster than doing a table scan. The actual indexing won’t help, but the fact that all the data you need is held in the index does.
9. Bijay Rungta

   http://www.carlj.ca/2007/12/16/selecting-random-records-with-sql/

   A similar article for all sql systems..

   I actually confused these two posts today as I had posted a comment on the other Post and didn’t see that here…

   Do check my Comment there…
10. Bijay Rungta

    RAND(), RAND(N)

    Returns a random floating-point value v in the range 0 SELECT RAND();
    -> 0.9233482386203
    mysql> SELECT RAND(20);
    -> 0.15888261251047
    mysql> SELECT RAND(20);
    -> 0.15888261251047
    mysql> SELECT RAND();
    -> 0.63553050033332
    mysql> SELECT RAND();
    -> 0.70100469486881
    mysql> SELECT RAND(20);
    -> 0.15888261251047

    The effect of using a non-constant argument is undefined. As of MySQL 5.0.13, non-constant arguments are disallowed.

    To obtain a random integer R in the range i SELECT * FROM tbl_name ORDER BY RAND();

    ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

    mysql> SELECT * FROM table1, table2 WHERE a=b AND c ORDER BY RAND() LIMIT 1000;

    Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

    RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.

    From http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand
11. Steve

    I tested this myself, and posted my results here:

    http://www.seven.net.nz/2007/random-results-mysql/
12. david

    Steve: Your MAX(id) idea is pretty good but not flawless — what if an ID has been deleted? Your query could return less rows than desired.
13. bhaarat

    Great hidden trick. How about a post that explains how to return multiple results from MySql Stored procedure? I know they’ve added this from 5.0+ or 4.0+, not sure. But have not really seen any examples around.

    Thanks!
14. Aaron Saray

    Hello,

    I would still caution against the order by rand(). Even if its not doing a table scan, it can still be harmful.

    I went into detail about how I optimized my own issue with this (I had a scenario where I had to choose a random user…) in this blog post:
    http://www.102degrees.com/blog/2007/06/27/random-user-generation-optimized/

    For those who don’t want to read all of the posting, here’s what I came up with (of course, optimized for my situation):

    $sql = “select u.userID, u.userName, s.globalPasswordProtect, s.allowRandom
    from Tuser u JOIN
    (select (rand() *
    (select max(userID)
    from Tuser)) as id)
    AS r2
    inner join TuserSettings s
    on u.userID=s.userID
    where u.userID >= r2.id and
    s.allowRandom=1 and s.globalPasswordProtect=0 and u.status=’A’
    order by u.userID ASC
    limit 1”;
15. Lois Lane

    Hi, im quite new in PHP and im currently developing a website for online exam in which i would like to generate set of questions at random which is retrieved from the database do you think the use of order by rand() is efficient to use…. hope somebody could help me with these thanks in advance!
16. Mysql 5 Tutorials

    These method is nice, easier then trying to do random sorting using php. I’d use this to randomize tags like they do on tagtill.com
17. GregPK

    I gave the thing a few minutes of though and came up with the following hybrid of counting rows and protecting against deleted ID’s.
    Note that the db() function return a instance of the database object derived from the AdoDB abstraction layer base. Some docs:
    http://phplens.com/adodb/reference.functions.getone.html
    http://phplens.com/adodb/reference.functions.getrow.html
    But this can be changed to any DB philosophy.

    while ($randomRecord === FALSE)
    {
        $randomRecord = db()->GetRow("SELECT * FROM quotes WHERE id = " . rand(0,$tableCount-1);
        if (count($randomRecord) === 0)
            $randomRecord = FALSE;
    }
    

    Anyone see any holes?
18. GregPK

    Hmm posted bad code – here’s the version it should have been:

    $randomRecord = FALSE;
    $tableCount = db()->GetOne("SELECT COUNT(id) FROM quotes");
    while ($randomRecord === FALSE)
    {
        $randomRecord = db()->GetRow("SELECT * FROM quotes WHERE id = " . rand(0,$tableCount-1);
        if (count($randomRecord) === 0)
            $randomRecord = FALSE;
    }
    
• SQL Rookie

  I do not think this is a good idea. If you have 100 records created an the deleated the first 98, your remaining ids are 98 and 99. Your count is 2 and your random numbers 0 and 1…

  fetching the maximum index is the better way:

  $tableCount = db()->GetOne(“SELECT MAX(id) FROM quotes”);
19. Newerton

    “The great part about selecting random records from your MySQL tables is that it’s really easy.”

    The bad part about it is that it’s really wrong ;-)
• Tofudisan

  Why would it be wrong to select random records?

  For instance we have a page on our website where we want to display 4 random floor plans from our library of over 1200 floor plans. Pretty much any “featured products” website section is likely random driven in some manner.
20. David Walsh

    @Newerton: Care to share why?
21. Tord

    Hi all just come across this discusion and was wondering about how to select a small randomly generated sub set of rows from a table.

    Since there seems to be performance problems with order by rand() the most solutions seems to do a sub set of rows from a generated start value i.e

    select * from test_table where id >= “rand_nr” Limit 10

    this will how ever only give you a sub set of ten rows starting from id greater then or equal to the random number generated.

    Does anyone have a solution where all ten (or any number greater then 1) rows are randomly selected??
    i.e gives id 25, 705, 1003, 2, 2306 … and so on.

    Thanks in advance. /Tord
22. Mullanaphy!

    For everyday end user stuff chances are RAND() will be perfectly fine. If you’re working with large tables then not so much. So most people reading this site I think would benefit using RAND(). How many people are really working on sites with large tables?

    At my job (social networking site) the RAND() isn’t an option, so the randomization is done in PHP.

    For example say we want 5 random videos out of the most recent videos. What I setup was grabbing the latest 1000 videos and store them into an array, which is Memcached for ~4 hours through a cron job. Then frontend just do a shuffle on the array and grab the first five rows.

    So people worrying about large tables, chances are they’ll have caches setup anyway.

    P.S. some of your CSS suggestions started making me wonder. Also for my next personal project might give MooTools a whirl. Request.JSON intrigues me.
23. Roy

    Ah you saved me a lot of time !
    ANOTHER VERY useful tip from you, thanks a lot :)
24. parvez

    I am also fencing this problem:
    i want fetch data from database in descending order with randomly
    so can’t get result
25. Mullanaphy!

    Parvez, resort what you grab:

    SELECT t.* FROM (
    SELECT id,firstname,lastname
    FROM your_table
    ORDER BY RAND()
    LIMIT 5
    ) as t
    ORDER BY t.firstname DESC
26. Graham

    Fantastic! What a tight piece of code! Thanks
27. sarang patel

    IT can also be done by ‘order by random_field_name’ on table
    We just have to pass random fields in query.
28. partha

    how i can select random number of rows from a table without rand() on mysql??
29. Jon

    How about this for a single random record?

    select * from `table` where id>=(select RAND()*max(id) from `table`) limit 0,1
    

    It isn’t completely fair since a deleted record gives the previous id twice the chance of being selected.
• Darnell

  @Jon using a JOIN statement is way faster on a large table. See example

  SELECT *
  FROM table T
  JOIN (SELECT FLOOR(MAX(id)*RAND()) AS ID FROM table) AS X ON T.ID >= X.ID
  LIMIT 1;

  This solution is just as fast or faster than @Stefan Reuter’s solution.
30. ApeBroker

    This page:
    http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand has a really good solution in case you need more complex WHERE clauses.
    It involves a subquery generating a few (as few as the number of returned rows). Check it out!
31. Ghasem

    for select 50 last records by random:
    SELECT T1.* from (select * from tabl_name order by id DESC limit 50) as T1 order by RANDOM()
32. peristilo

    I’ve been spending a lot of time trying to figure out how to make my random query work, but now I have the right answer. Thanks so much!
33. Karma Dice

    You sir, are awesome!

    I knew it but, somehow it slipped out of my mind since the dawn of ORMs…
    Thank you, you have saved my day!
34. ashish

    It’s good idea , i think it might be problem sometime when large data the value might be repeat, so it needs be care
35. Ritvik

    “select * from question where level=? ORDER BY RAND() LIMIT 1000”
36. Ritvik

    it isnt working…
    dump is being made
    and values are being reteriving from dump using above select quary
    but still random questions are not coming…
    help me out
37. Sean

    Thanks for that David, I’ve been using php to set a random number as the condition and then retrieve a row. That way I can make it a constant random number (oxymoron) for say something like a daily quote using crc32. Recently it looks like crc32 isn’t working as it used to on later versions of php.

    Is there a way to keep a constant random number for a day using mysql rand()?
38. Trevor Mills

    HI David,

    I came up against this exact issue tonight on a table containing ~25K rows. I needed to get 4 random rows from the table. I took a look the various solutions through the comments here, as well as some others through the web, and none were satisfying.

    To make things a little more tricky, within my query I’m doing some complex fetching of meta data. The DB schema is the WordPress schema (wp_posts, wp_postmeta, etc). I’m running a complex query to fetch desired posts + certain postmeta all within 1 SQL call. These complexities, combined with ORDER BY RAND() resulted in the query taking > 6 seconds to execute. Yikes!

    I ended up getting it to reasonable performance ( 0.06 seconds for the query ) by doing the following ORDER BY clause:

    ORDER BY MD5( CONCAT( ID, CURRENT_TIMESTAMP ) ) LIMIT 4
    

    I like the simplicity of this approach. What do you think?
• Tom

  Excellent solution! Very elegant, and reseeds the randomness automatically. Thank you VERY much for sharing!
• LukasS

  Great code, implemented in my table with over 1mln records (and growing fast) and works like a charm :). Thank you!
39. Jafar

    Great idea,
    But can we reset randomizer, I mean seed the random row generator?
40. David

    Nice reminder, I keep forgetting the random function. Annoying that it takes so long on large data sets, need to find a way to speed that up.
41. Michele D'Urzo

    Hi all,
    my recipe is a blend of PHP and MySQL.
    In the other solutions proposed I see some assumptions that not always can be satisfied:
    1) Exists an integer key (autonumber)
    2) The keys are in a sequence starting from 1
    3) There are no “holes” in the sequence
    My solution works also if you have an alfanumeric key such as ISBN for books, determining how many records are in the queried set and then fetching randomly the Tth tuple in the resultset.

    $result = mysql_query( "select * from books" ) ;
    mysql_data_seek ( $result , rand( 1, mysql_num_rows( $result ) ) ) ;
    $book = mysql_fetch_assoc( $result ) ;
    

    Greetings.
    Michele
42. Curamba

    thanks for the above post… it helps me a lot to randomly select 40 records…

    select * from tbl_questions_latest where rand()*3 order by md5(concat(idrec,current_timestamp)) limit 40;
43. Rajan Bhadauria

    Hello,

    I have 2 tables name lesson and other one is questions. Questions Table stores data wrt to lesson_id. Lesson table stores data wrt to course.
    and I need to fetch question with equal % of lesson_id with in a course.

    Structure is as
    Course Table
    id,course_name

    Lesson Table
    id,course_id,lesson_name
    1 | 1 | lesson A
    2 | 1 | lesson B
    2 | 1 | lesson C

    Question Table is
    id,lesson_id, question
    1 | 1 | Que A1
    2 | 1 | Que B1
    3 | 1 | Que C1
    4 | 1 | Que D1
    5 | 2 | Que A2
    6 | 2 | Que B2
    7 | 2 | Que C2
    8 | 3 | Que A3
    9 | 3 | Que A3

    and I need to fetch equal % questions based on lesson from question table with provided course id = 1.

    if limit is 3 then out put will be
    sn|lesson_id|question|course_id
    1 | 1 | Que A3 | 1
    2 | 2 | Que A3 | 1
    3 | 3 | Que A3 | 1
44. kudafoolhu

    Any updated ways to get random results? I created a view to get random results. It might be slow, any thoughts?