Comments on Ecstatic Thinking: Selecting random row from a table

I made a gem for selecting random records that per...

2011-08-17T15:34:11.403+02:00

I made a gem for selecting random records that performs much better than order by rand():

https://github.com/spilliton/randumb

the best solution is forget this and make money fi...

2011-03-30T15:19:50.122+02:00

the best solution is forget this and make money first with order rand()

@bensonk: the other solution doesn't assume that I...

2008-12-01T21:11:00.000+01:00

@bensonk: the other solution doesn't assume that IDs are densely packed. It uses offset (limit), not ID number, so it chooses n-th found row, not a row with ID equal to n. So if you have IDs like 1, 2, 4, 8, and get 3 at random, this method will take 3rd row, which has ID = 4.

Neither of these are good solutions. The "ORDER B...

2008-12-01T20:15:00.000+01:00

Neither of these are good solutions. The "ORDER BY RAND()" version is horribly inefficient, and the "find (rand Modelname.count)" version assumes that you have densely packed IDs from 1 to Modelname.count(). There's got to be a better way.

Some database servers probably optimize this kind ...

2007-12-14T22:22:00.000+01:00

Some database servers probably optimize this kind of stuff. MySQL just isn't one of them.
Here are some real numbers to get things clear. I've created table "numbers" and populated it with 100k rows. This query:

select * from numbers order by rand() limit 1;

takes 0.40s to complete. The other query:

select * from numbers limit 12345,1;

where 12345 is your

"performance killer"?
For sure it's not a good ...

2007-12-14T21:31:00.000+01:00

"performance killer"?
For sure it's not a good practice, but I'm wondering what is real life difference?

Wouldn't query optimizator take care of it?