A while ago, I were searching for a way to produce random rows from a table in MySQL. I found several solutions but none of them satisfied me. Of course, I could use  a combined logic of MySQL and a programming language. For example by producing random numbers in PHP and using them in the MySQL query in a IN clouse.  However, I was looking for a pure query solution. The easy way that MySQL provides is by using ORDER BY RAND(). This trick works fine for very small tables. However, if you have a big table, this solution is a bad choice.  It has a big performance  issue as it orders all the table rows prior to selecting a few of them.

My solution to this problem is using User Defined Variables. Let’s say we want X random rows from a table myTable.  

SELECT myTable.* FROM (SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b ,  myTable WHERE b.num=myTable.id;

I will try to explain some of the parts:

1. FLOOR (RAND() * (SELECT count(*) FROM myTable)) num  – produce an integer value between 0 to the max rows this table has
2. (SELECT @num:=0) a – initialize the User Defined Variable a to zero
3. (SELECT FLOOR (RAND() * (SELECTcount(*) FROM myTable)) num ,@num:=@num+1 from (SELECT @num:=0) a , myTable LIMIT X) b – produces X random integer numbers

If you need it with range, say X random rows from a table myTable with a range from S to T you can write it like this:

SELECT myTable.* FROM (SELECT FLOOR (S + RAND() * (T – S)) num ,@num:=@num+1 FROM (SELECT @num:=0) a , myTable LIMIT X) b ,  myTable WHERE b.num=myTable.id;

Some caution: because this query uses User Defined Variable (@num) which are connection specific, you can NOT use this MySQL query with a system that uses connection pooling or persistent connections.

Comments are welcomed,
Hazan Ilan.