Fetch a random row from the database without using triggers, and quickly.

Posted by colinta on March 18, 2010
database, SQL

First: the don'ts.

So what to do? Let's look at a half-decent PHP implementation (this is gonna use an Orm that wraps PDO, so don't be surprised if you don't recognize the classes and methods).

$ids = Query::fetchOnly('SELECT id FROM tips'); // that's right, all of 'em!

$random_id = $ids[ array_rand($ids) ];

$row = Query::fetchOne('SELECT * FROM tips WHERE id = ?', $random_id);

This is actually pretty fast – to a point (100,000s of rows => problem), but the idea is sound.  Select a random id, and fetch that row.  How can we do this using MySQL?

Hmm... humm. AH! OFFSET! That's pretty much the same thing, right?

$count = Query::fetchOnly('SELECT COUNT(*) FROM tips');

$random_offset = rand(0, $count) - 1;

$row = Query::fetchOne('SELECT * FROM tips LIMIT 1 OFFSET '. (int)$random_offset);

We are essentially doing the same process we did above - fetching by a random offset - but instead of a php array, we're looking at the entire result set.

And that should do it!

This doesn't tackle the problem of selecting more than one row, and if you want to join on a has_many or many_to_many relationship, you'll need to take a few more steps (use a subquery!).

Posted in: Database Administration, Programming

2 responses to "Fetch a random row from the database without using triggers, and quickly."

Ivy (3/18/2010 @ 7:51pm)

Wow, that looks super easy. (wink) Don't expect a normal human to understand a word of what you just wrote. My head just exploded.