Select random row that exists in MySQL

Question

Possible Duplicate:
How to request a random row in SQL?

Currently Using:

$randomQuery = mysql_fetch_row(mysql_query("SELECT * FROM `table` WHERE `id` >= RAND() * (SELECT MAX(`id`) FROM `table`) LIMIT 1"));

Table Structure:

id:
 2 
 4
 5

I want to make sure it's selecting an existing row. For example, it shouldn't be able to use 1 or 3 in its randomizing function. Is there a way to do this in MySQL?

This is biased depending of the holes but I don't see how this could select a non existing row. — Denys Séguret, Jun 22 '12 at 12:02
Any solution using `RAND()` will be slow because it can't do an index. For faster, properly indexed solution, see my answer here: http://stackoverflow.com/questions/10677767/fastest-random-selection-where-column-x-is-y-null/10677869#10677869 — Spudley, Jun 22 '12 at 19:32

score 8 · Accepted Answer · answered Jun 22 '12 at 12:02

8

I think

SELECT * FROM table ORDER BY RAND() LIMIT 1

would do the trick.

See:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

answered Jun 22 '12 at 12:02

McIntosh

2,051
1
22
34

Does this take into account deleted rows? – Aaron Jun 22 '12 at 12:04
deleted rows - i think - are removed from the storage engine, so they will not be included in the result. If you only have a deleted flag in the row instead of `DELETE`ing the row completely you need to add a `WHERE deleted=0` to your statement – McIntosh Jun 22 '12 at 12:44

score 4 · Answer 2 · answered Jun 22 '12 at 12:00

4

SELECT * FROM table ORDER BY RAND() LIMIT 1

answered Jun 22 '12 at 12:00

sp00m

47,968
31
142
252

this will work, but will be slow because it won't use an index. – Spudley Jun 22 '12 at 19:31

score 1 · Answer 3 · answered Jun 22 '12 at 12:06

1

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

http://akinas.com/pages/en/blog/mysql_random_row/

answered Jun 22 '12 at 12:06

Darren

68,902
24
138
144

score 0 · Answer 4 · answered Jun 22 '12 at 12:14

Your initial query does take care of the holes as it simply compares the id of a row to the random value you compute and doesn't try to fetch a row having a random id.

Its problem is that it gives you a non uniform probability : Any row following a big hole has a bigger probability of being selected.

That's the reason why it's suggested to use the mysql trick ORDER BY RAND() (see other answers) as the randomization will be quasi uniform.

Select random row that exists in MySQL

4 Answers4