Working procedure of RAND() in MySQL?

Question

i'm trying to select 5 rows randomly from my table using RAND() in mysql, and i've set the LIMIT as 5 but why sometimes it will return less than 5 rows?

Here is my Query:

SELECT id from MAIL where RAND() limit 5;

MAIL is my table name which contains more than 20 Records.

Possible duplicate of [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — Alex K., Jun 19 '18 at 12:48

score 0 · Answer 1 · answered Jun 19 '18 at 12:52

In your version of the use of rand() function, MySQL evaluates its result in a Boolean context because it is directly used in the where clause, not in the order by clause.

If rand() is evaluated as false, then the record is left out of the resultset. It is possible that in a particular query more than 15 records are eliminated as a result of this approach resulting in less than 5 records being returned.

Your should use the order by rand() clause to overcome this issue.

Working procedure of RAND() in MySQL?

1 Answers1