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.

Notify of
Inline Feedbacks
View all comments