SQL: Select Random Rows or Records in MySQL

With PHP and MySQL, you may fulfill the same task by selecting all the rows into an array and choosing a random index with php function array_rand or mt_rand (better) but that would be all big overhead to do as you have to first read in the entire table.

The correct way is to get a random row directly with MySQL is:

SELECT * FROM sometable ORDER BY RAND() LIMIT 1

That’s just one random row. If you want more than one, you’d just going to change the LIMIT clause:

SELECT * FROM sometable ORDER BY RAND() LIMIT 4

Now it will pump out 4 independent random rows.

To know the random selecting SQL query in PostgreSQL, Microsoft SQL Server, IBM DB2 or Oracle, Pete is the guy you’d be asking. 🙂

5 thoughts on “SQL: Select Random Rows or Records in MySQL”

  1. Uh, dude… that’s really the most inefficient way possible. Try doing that with 10k rows and it’s going to take in the order of minutes which probably isn’t acceptable.

  2. @person,
    Not that horrible. I tried this with a table with 180,000 rows and it takes 0.5 second to generate a random row.

    I think the better solution is to first have the number (count) of rows of that table and then generate a random number maxed by that count. This can be done almost instantly.

    Otherwise, without the total number of rows, MySQL has to count its way down to the end of the table to get it. That’s where most of the time is wasted.

Comments are closed.

Scroll to Top