Skip to content

ORDER BY RAND() – Faster Alternative

MySQL’s ORDER BY RAND() function can be so useful for returning random items from a table, in fact, we have used it a million times over the years.

The problem comes when your database tables start getting really big.

We found a very nice alternative to using it and thought it useful to post here for everyone else to use and/or provide feedback on.

Say you have a SQL query as follows: (slow on big tables)

SELECT id, title, desc FROM your_table ORDER BY RAND() LIMIT 38

Try this alternative instead: (much faster!)

SELECT id, title, desc FROM your_table ORDER BY 38*(UNIX_TIMESTAMP() ^ id) & 0xffff LIMIT 38

Mix the bites from the id and then take the lowest 16 only.

The 38* in this case is the same number that we are using to LIMIT.

See also  Approaches to software reuse
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x