On 1/7/10 3:10 PM, "Matt Neimeyer" <matt@stripped> wrote:
> I'm trying to select names at random from a table that contains the
> name and the frequency at which it is actually used in society. The
> table is defined as follows:
> CREATE TABLE `MaleNames` (
> `Name_ID` int(11) NOT NULL auto_increment,
> `Name` char(50) default NULL,
> `Frequency` decimal(5,3) default NULL,
> PRIMARY KEY (`Name_ID`)
> Some examples:
> 1, Aaron, 0.240
> 3, Abe, 0.006
> 13, Adrian, 0.069
> What's the best way to select names at random from this but still take
> into account frequency of use?
after reading the source, i'd avoid using MySQL's RAND(). you can probably
easily get better quality pseudorandom number in your app's environment.
to get a random row, "LIMIT 1, r", where offset r is a random number between
0 and (tablelength - 1), should work.
if you must use RAND(), FLOOR(tablelength*RAND()) will work for r.
if you want a random row from a constrained subset of rows based on
frequency, e.g. "WHERE Frequency > 0.001", you can include that condition in
the query and repeat it until you get a non-empty response.
but if the constrained subset is only a small fraction of the table's rows
then it might be more efficient to compute its size (with COUNT() in another
query or a subquery) and use that in generating the offset r.