You could count the number of records using 
  SELECT count(*) FROM table

[download]
Then you could pick a random number from 0..$n-1 - call it $rand and using the 2 paramter form of LIMIT select it, eg 
  SELECT * FROM table ORDER BY id LIMIT $rand, 1

[download]
This should be very quick and efficient. You should ORDER BY the primary key probably for maximum speed (where it says id above).

This method has the advantage that it will work with a WHERE clause on the the SELECTs), eg WHERE setup=1 or something like that

This two parameter LIMIT might be MySQL specific I don't know (it doesn't say it is in the manual though)

Update: I tried this on a large MySQL table (with 100ish columns and 500,000ish rows). Leaving out ORDER BY made only a very small difference. The first SQL statement was instant. The second took 8 seconds varying by $rand so maybe it is doing a full table scan here :-(

In reply to Re: Select a random record by ncw
in thread Select a random record by sjaak

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.