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.
Note:
The 38* in this case is the same number that we are using to LIMIT.