Re:Nondeterministic Functions in MySQL (i.e. Rand) Can Surpr...
by nine_k on Thursday December 06, 2018 @04:28PM
(#18622433)
I think that something like
select * from the_table
offset rand(select count(*) from the_table)
limit 1;
would do the trick with least bias, though not necessarily very fast.
Re:Nondeterministic Functions in MySQL (i.e. Rand) Can Surpr...
by mjevans on Thursday December 06, 2018 @04:42PM
(#18622561)
The offered example query is could return no results in the presence of record gaps / deleted records; also the OFFSET value should probably be computed within a sub-query, not exposed.
A corrected version based on being >= or <= the random number (picking the nearest) COULD work (in fact, that's the way my suggest final fallback works), but would still be biased.
That was the reason I suggested taking a limited number of stabs at random numbers within the allowed range; my example still has some slight bias (a little in the 'best case' fallback, and a lot in the absolute fallback), but it tries to deliver a truly random result first, many times.
If the intent is actually to select one, and only one, random row from the table than this Stack Overflow entry has a result that is in most cases in the direction I was thinking (LIMIT 1, and have some way of selecting a random entry, remembering that the ID field might be monotonic, but isn't assured to be continuous (have no gaps))
https://stackoverflow.com/questions/19412/how-to-request-a-r...
Unfortunately it seems that it depends on language extensions that are not portable.
EDIT:
It unfortunately also appears that the easiest query scales poorly to large tables as it works by assigning EACH row a random ID and then limiting it to one of those by some order (probably lowest assigned row).
A more effective approach might be a function or procedure based around a pre-recorded 'maximum' ID for the column (assuming it's vaguely monotonic and not a GUID), picking a random result between base and that ID, Verifying it exists, and if it doesn't trying again up to N times before using a fallback procedure. (E.G. after 100 random stabs that fail selecting ~500 records in the range of lastRND +/- 250 and using the ORDER BY rand() or equivalent on that sub-set). If even /that/ fails pick the next or previous record by which is closest.