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 1That’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 4Now 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. 🙂
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.
@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.
Order by rand performing badly with big tables.
There is another way to produce random rows using only a query and without order by rand().
It involves User Defined Variables.
See http://www.mysqldiary.com/how-to-produce-random-rows-from-a-table/
Great!
In order to find random rows from a table, don’t use ORDER BY RAND() because it forces MySQL to do a full file sort and only then to retrieve the limit rows number required. In order to avoid this full file sort, use the RAND() function only at the where clause. It will stop as soon as it reaches to the required number of rows.
See
http://www.rndblog.com/how-to-select-random-rows-in-mysql/