How does RAND() in SQL query actually work

Question

How does the following query actually work?

SELECT * FROM t ORDER BY RAND() LIMIT 1;

Does it sort all records in the source database first and just truncate it to get 1 row then?

Answer 1

This question has more to do with LIMIT query optimization than how does RAND() work.

From manual (bold emphasis mine):

If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result.

If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

Answer 2

Yes. Order by operation is performed before limiting the result.

To get a random record in good performance, you must use another approach.

A solution is : If you have an id that is sequential you can create a random number and fetch that record only by where clause:

Select * from t 
where id>(select * from 
  (select rand()*10000)t1
 ) limit 1;

Where 10000 is the biggest id in your table.

To making the query more dynamic we can use:

SET @m:=rand()*(select max(id) from t); 
SELECT * FROM t WHERE id > @m LIMIT 1;

And also this works:

Select * from t 
where 
  id>(select * from(select rand()*max(id) from t) t1)
limit 1;

But following query is incorrect and shows only approximately 0.5 percent of records in the beginning. And I don't know why:

Select * from t 
where 
  id>rand()*(select max(id) from t) 
limit 1;