best way to select random element from database

[dodo]

i've using ORDER BY RAND() LIMIT 0, 1 to select random element from sql but i just recently realize it's really NOT VERY RANDOM.. it seems to give the elements with lower id number a higher precentage to show up. i want something truly random, does anyone know another way to do it? thanks

[dodo]

ok i just tried the suggestion from here and used

SELECT id, image, MD5( RAND( ) ) AS myRandom
FROM banner ORDER BY myRandom LIMIT 0, 1

it seems to work a little better. but if someone has something different, please share.

[Sxooter]

The real problem here is building something that doesn't die when you put a ton of records in that table.

If you do it with limit, offset, then the query has to build the whole return set up to that limit and throw it away. Well, at least the key value in the where clause. I.e. it has to assign a random number to EVERY ROW in the table, sort those random numbers, and THEN it can return the row.

If you know the exact size of the table (select count(*) from table can be slow, but it will give you the answer) then you can do something like:

$random = rand(0,size);
select * from table limit 1 offset $random;

That's postgresql limit offset syntax, I'm not sure how it translates into mysql's comma pair thing, I think

select * from table limit $random,1;

Notice there's no order by in this one, so it's usually much faster on large tables.

[dodo]

i tried your way tho it requires an extra select statement, it might make it more randomized. but i'm don't know yet. so far it seems to be pretty much the same as the previous select statement.

[Sxooter]

Compare the two on a table with 100,000 rows in it.

Also, if you're going to be doing this a lot, then you can cheat a bit, and run a cron job to run the select count(*) into tablecount.tbname from table; to get the count. then it will be much faster than the random way.

Of course, if you're ever only going to have 100 rows to go through, then they're equivalent.