Hi Myke,

That's an exciting question.

The short answer is: No, ORDER BY Rand() can not be indexed as such.

However, there might be another way to get a random result without ORDER BY RAND(). You didn't post an actual example, so I try to give you a skeleton that you might be able to apply to your case:

instead of

select id, columns
  from your_table
 order by rand()
 limit 1;

try to select a random ID that is indexed and (approximately) evenly distributed:

select id, columns
  from your_table
 where id >= rand() * (select max(id) from your_table) - 1
 order by id
 limit 1

Note the following:

• The upper bound of the ID is fetched from the table itself, but that's very fast in all databases. In MySQL it should turn out to be 'optimized away' in the execution plan. (Oracle would use a min()/max() scan that is as efficient as an INDEX UNIQUE SCAN).
• it's no problem if the ID doesn't start at 0 or has "holes" (missing ID's) because the generated random ID is used as starting point only.
• the index range scan starts at the randomly generated number, but stops after the first record it matches (LIMIT 1).
• You cannot fetch more than one random record in one shot. if you would increase the LIMIT, you would just receive the subsequent IDs. Edit: but you can use CTEs to fetch more than one in one shot: on github
• just for paranoia, I subtracted 1 from the random number for that case that floating point arithmetic (rounding) leads to a random ID slightly higher than max(id)--but that shifts the probabilities a little bit.
• unless your ID range is from 0 - max(id), without duplicates or missing IDs, you don't receive a real random value. However, you said that this is just used to provide a wrong impression to the uses, so I guess that's OK for that case.