The easiest way to generate random records in Mysql is to use the ORDER BY RAND().
SELECT col1 FROM tbl ORDER BY RAND() LIMIT 1;This can work fine for small tables. However, for big table, it will have a serious performance problem as in order to generate the list of random rows, because Mysql need to assign random number to each row and then sort them.
Even if you only want random 1 records from 200k+ rows, Mysql need to sort all the records and then extract it.
The better way and let your mysql perform faster in random records is to use RAND in the WHERE not in the ORDER BY. You need to calculate the total rows in your table. And use it in the WHERE and ask only for RAND numbers that smallest (or equal).
For example, suppose you have a table with 200K rows and you need only 100 random rows from the table. The fragment of the result set from the total rows is: 100 / 200k = 0.0005.
The query will look like
SELECT * FROM table WHERE RAND() <= 0.0005;In order to get exactly 100 row in the result set, we can increase the number and limit the query:
For example:
SELECT * FROM table WHERE RAND() <= 0.0006 LIMIT 100;