How do I optimize my RAND() function for large tables?

Question

I need to get 20 random images from a table My current query is

SELECT * FROM foo ORDER BY RAND() LIMIT 20;

This question might help but his query is much more complicated, I understand the answers since im a beginner How can i optimize MySQL's ORDER BY RAND() function?

That's because you problem _is complex_. That is: despite problem has very short description, it's not the thing that can be easily solved in mysql — Alma Do, Apr 16 '14 at 10:11
Ok I will do my research and update my answer with the solution specific for a simple query like this — user3531149, Apr 16 '14 at 10:13
[Try this solution](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — N.B., Apr 16 '14 at 10:17

score 3 · Accepted Answer · answered Apr 16 '14 at 10:17

The performance problem with your query is not the rand() per se but the order by on a large number of rows. If you know that foo has a certain number of rows, say 1,000,000, then something like this will run much faster:

SELECT *
FROM foo
WHERE RAND() < 0.00001
ORDER BY RAND()
LIMIT 20;

The where clause reduces the number of rows for the order by to about 100. And, you would be very confident that the number would be at least 20. You can automate this calculation as well:

SELECT *
FROM foo cross join
     (SELECT count(*) as cnt FROM foo) const
WHERE RAND() < 100.0/cnt
ORDER BY RAND()
LIMIT 20;

How do I optimize my RAND() function for large tables?

1 Answers1