Randomly selecting rows with MYSQL

Question

hi

I have a question, to randomly select records from one table do i have to always set a temporary variable in php or what? I basically need some help with selecting random rows with a model in codeigniter and displaying 3 different ones in a view everytime my homepage is viewed.

Anythoughts?

Answer 1

I would do something like:

SELECT * FROM table ORDER BY RAND() LIMIT 1;

This will put the data in a random order and then return only the first row from that random order.

Answer 2

If you don't have a ton of rows, you can simply:

SELECT * FROM myTable ORDER BY RAND() LIMIT 3;

If you have many rows, this will get slow, but for smaller data sets it will work fine.

As Steve Michel mentions in his answer, this method can get very ugly for large tables. His suggestion is a good place to jump off from. If you know the approximate maximum integer PK on the table, you can do something like generating a random number between one and your max PK value, then grab random rows one at a time like:

$q="SELECT * FROM table WHERE id >= {$myRandomValue}";
$row = $db->fetchOne($q); //or whatever CI's interface to grab a single is like

Of course, if you need 3 random rows, you'll have three queries here, but as they're entirely on the PK, they'll be fast(er than randomizing the whole table).

Answer 3

you need a query like this:

SELECT * 
FROM tablename
WHERE somefield='something'
ORDER BY RAND() LIMIT 3

It is taken from the second result of http://www.google.com/search?q=mysql+random and it should work ;)

Answer 4

Ordering a big table by rand() can be very expensive if the table is very large. MySQL will need to build a temporary table and sort it. If you have primary key and you know how many rows are in the table, use LIMIT x,1 to grab a random row, where x is the number of the row you want to get.

Answer 5

I have this piece of code in production to get a random quote. Using MySQL's RAND function was super slow. Even with 100 quotes in the database, I was noticing a lag time on the website. With this, there was no lag at all.

$result = mysql_query('SELECT COUNT(*) FROM quotes');
$count = mysql_fetch_row($result);
$id = rand(1, $count[0]);
$result = mysql_query("SELECT author, quote FROM quotes WHERE id=$id");