MySQL: rand()

jmarco

select <field> from <table> order by rand() limit <int>;

I have a problem that when I use this in the shell I appear to get an always random listing of fields, but when run on the browser in a php script I almost always get the field of the first record in that table.

Currently I'm limiting the search to 6, and, like I mentioned above, I'm getting the field of the first record, and almost always as the first listing when run in the browser. Is there a patch to stop that from happening, and to make it more random?

barand

You could try seeding the random number generator with [man]srand[/man]

Sxooter

Originally posted by barand
You could try seeding the random number generator with [man]srand[/man]

that rand() is not a php function, but a mysql function, which is well known for it's non-random (but very fast, as per MySQL's focus) behaviour.

Better way, if the table isn't really big, is to do a

select count() from table;
$somevar=countfromabove;
seed with srand
$offset = random(0,$somevar)
select from table limit $offset, 1;

csn

Originally posted by Sxooter
that rand() is not a php function, but a mysql function, which is well known for it's non-random (but very fast, as per MySQL's focus) behaviour.

Better way, if the table isn't really big, is to do a

select count() from table;
$somevar=countfromabove;
seed with srand
$offset = random(0,$somevar)
select from table limit $offset, 1;

What if the table is on the big side? 😉

Sxooter

If the table is on the big side you want to avoid using count(*) as it is a sequential scan in most databases.

In a real database you can create a seperate table to hold the count value and create a trigger that updates that value on inserts/deletes and such, then select from there.

On the other hand, using the rand() function in a sql query on a large table is also very slow, so it might still be much faster to run a select count(*) query first anyway.

On Postgresql, due to its MVCC nature, count(*) is as slow as any other flavor of aggregate, i.e. not optimized for the simple case with no where clause.

If the rows are <10,000 a select count(*) should be plenty fast by itself.