• An alternative to ORDER BY RAND() for MySQL

    The issue with RAND() is it will be inefficient on large tables because each row needs to have the random number calculated before the resultset can be ordered. This post looks at an alternative which requires two queries but will be much more efficient for large tables.

    The alternative suggested in this post uses COUNT(*) first to get the number of records in the table and then picks the record by using MySQL’s LIMIT syntax.

    Using a programming language

    Doing this outside MySQL is done in a similar way by getting the count from the table first, working out a random offset and then running a second query to get the record. This example uses PHP:

    1
    
     

    Conclusion
    In above example only uses a small table and in this instance would be easier to simply use “ORDER BY RAND()” but ordering in this way is inefficient and performance will start to suffer as the table grows. Instead it can be more efficient to run a couple of queries instead to work out an offset based on the number of records in the table, and then use MySQL’s LIMIT syntax to pull that record out of the table as shown in above.

Comments on this post

Leave a Reply

  • Security Code :


    3 + eight =