Random MySQL row while avoiding holes

Question

I have a big MySQL users table and need to get six random rows from it (I'm using PHP). The table has an index column that is auto incremented. The only problem is that some rows are marked as inactive, because some users have disabled their accounts or whatever. That means I can't just count the rows and then grab a random number from that range because some of them will be inactive.

How can I efficiently get a random row without using RAND() and without trying to query an inactive user?

Thanks!

Answer 1

WHERE `inactive` = 0 LIMIT random_offset, 1

where random_offset is precalculated in PHP (as random from 1 to COUNT of active users). And the result consists of 6 UNIONed queries.

Answer 2

If you wish to avoid the very slow order by RAND() method, here are various alternatives, with various options to manage holes

http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql