Randomly select a MySQL record

This is a reminder to myself as I have this problem every now and then. I thought I share this with you.

Ok the problem is to random(ish)ly select a record from a MySQL database in an efficient way without any scripting or programming. There are several more or less simple ways described everywhere on the web, one being


It does the job. Very slowly though, it can take MySQL several seconds or even minutes to complete the query. After fiddling around for some time, I came up with this:

set @randomId=FLOOR(RAND()*(SELECT MAX(pk_field) FROM table));
PREPARE randomStmt FROM "SELECT * FROM table WHERE pk_field > ? LIMIT 1";
EXECUTE randomStmt USING @randomId

… where pk_field is a numeric primary key. Ok the query goes like this:

  1. Get the highest value for pk_field from table_name, multiply with a random number between 0…1, cut off decimals and store in variable randomId.
  2. Prepare a statement where we can insert randomId and retrieve the desired record with.
  3. Take the query, insert randomId and execute.

This runs very fast even with a large dataset. I hear your objections already… :)

“This is stupid. Why didn’t you use “COUNT(pk_field)” to find the end of the number range? Even then this approach would still be stupid BTW.” Because it is slow. I currently have a table with >1.8 million records and COUNT(pk_field) takes >1 seconds to complete. Not good. MAX(pk_field) is good enough in most cases. YMMV though.

“The query is not truly random and prefers records at the end of a large pk_field range gap!” True. That’s what I meant with “YMMV” :) My solution is not truly random and it is biased, especially when there are large gaps in pk_field. It works for me though and is random enough when there are no large pk_field gaps.