MySQL select random row - rand() performance [duplicate]

Question

This question already has answers here:

MySQL select 10 random rows from 600K rows fast (29 answers)

Closed 12 years ago.

Is it true that ORDER BY rand() performance is very slow compared to other solutions? If yes, what are better ways to select random row(s) from the database?

My query:

SELECT sName FROM bpoint WHERE placeID=? ORDER BY rand() LIMIT 1; 

• bestprogrammerintheworld: It depends on how much data there is. How big table are we talking about?
• eggyal: It also depends on what, exactly, you mean by "random": do you require every record be selected with equal probability? Or is a perfectly uniform distribution not necessary?
• Biker John: 5-10.000 rows. Equal probability desired.
• eggyal: If you know that an indexed column contains values ranging from i to j, you could filter WHERE column >= FLOOR(i + RAND() * (j – i)) ORDER BY column LIMIT 1. But if there are gaps, you won't obtain a perfectly uniform distribution of probabilities...

Answer 1

Yes, ORDER BY RAND() can be very slow in larger result-sets.

An option is to fetch resultset with this statement (into an array):

SELECT sName FROM bpoint WHERE placeID=?; 

After that - use array_rand($resultset) to get a randomized item from the $resultset query.

• Biker John: So, it is php function array_rand() faster?
• bestprogrammerintheworld: @JamaicaBob - it really depends on how large the resultset is. How many rows do you have in your table bpoint?
• Biker John: approximately 10.000 rows
• bestprogrammerintheworld: @JamaicaBob - ok, I THINK it would be faster with an array then, but I'm not sure. I guess you will just have to test and see difference. How long does the actual query take now?
• Wes: @BikerJohn - Older question, but depending on your programming language, php has mt_rand(). I implemented this by doing a quick count of the number of rows I have, and then doing $random = mt_rand(1,$count) to get a random int in the range, then SELECT * FROM 'table' WHERE ID = $random; Performance numbers jumped significantly. Only works if you have unique IDs though (auto_increment ftw!)