John Main

Yeah I know, there are loads of suggestions for this sort of thing on the net but I spent ages searching around for the code and many of the solutions were seriously obfuscated. So here is a direct replacement for an ‘order by rand() statement:

SELECT * FROM my_table ORDER BY RAND() LIMIT 10

Can be directly replaced with:

SELECT my_table.*, FLOOR(1 + RAND() * max_pseudo_table.max_id) AS random_index
FROM my_table, (SELECT MAX(id) - 1 as max_id FROM my_table) max_pseudo_table
ORDER BY random_index LIMIT 30

And with the right indices you will save a world of processor effort 🙂