I have applied some more thought to your problem. I have never used MySql, only Oracle and Informix, and not even anywhere approaching DBA expertise. So take my suggestions with a hefty grain of salt . . .

As I see it, there are two objectives; the first being able to select a random record, the second being able to do this in a relatively non-"anti-social" manner. You do not want to incur the wrath of your DBA, Sysadmin, IS manager, and so on. The most efficient way to retreive any sing record from a table is to use the "WHERE" clause on a primary key (or an alternate key where an index has been set up). Even a DBMS with an atrocious optimiser will do this without any difficulty.

One method would be to add another column - say "sequence_number", make it NOT NULL and UNIQUE, and set it as an alternate key. However, you will have to make sure that as records are added, this column gets the next number in sequence. Plus, where records are deleted, what then happens . . . the maintenance hassle is too nasty to contemplate.

Some DBMS's allow a special data type of "ROWID" or "SERIAL", which may allow you to have the system look after this for you. I know there is a ROWID with Informix, but I cannot find the right manual to explain if that is a proper sequence of integers. Besides, that is not ANSI standard SQL.

Another option may be to use a cursor - but this is stretching my limited data base knowledge. Something to look at though.

Ken

In reply to Re: Select a random record by Maclir
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.