SELECT count(*) FROM table
[download]

  SELECT * FROM table ORDER BY id LIMIT $rand, 1
[download]

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 :-(

Order by is only going to add overhead. If he wants a random record it will be faster to return them in their stored order.

Looking through my Informix SQL reference manual, there is no reference to the "LIMIT" parameter.

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

Ken,

Thanks for the idea, this is what I will use. The database does not exist yet and I am free to add as much columns as I like (I am the DBA). I will add a column in the table containing the serial_nr. Then, with a SELECT COUNT(*) FROM table and a SELECT record FROM table WHERE table.serial_nr = random_nr. If the record should appear to be deleted (not likely), then I re-select a record.

greetings,

sjaak

Add a column to your table, I'm gonna call mine FOO in this example. Index FOO. You could do this with any type of value (integer, varchar, float/real, etc..). Assign every record currently in the table a "random" value within a range (say you decided that foo was a CHAR(10) you could assign it values from 'aaaaaaaaaa' to 'zzzzzzzzzz'). Whenevere you insert a new row into the DB assign it a random value in FOO as well.

Now whenever you want a random record just generate a random seed (using the same range and algorithm you used to seed the table originally) then select * from BAR where FOO >= '$seed'. Don't fetch back all the rows (you could use LIMIT if your dialect of SQL supports it). Fetch them rows from the query back one-at-a-time while FOO has a constant value. If you got back only 1 row, then you have your match. If you got back multiple rows then pick randomly among them.

If you do not pick an apropriate size for FOO and a valid randomizing function and your table grows much larger than expected this could become inefficient. But it should work good in some circumstances.

The key to lhoward's approach is to index FOO. This is a critical detail: without an index, you're tablescanning again.

UPDATE: on second thought, if you DB supports DISTINCT and ROWNUM you could do something like this:

select distinct fname from jonathan.sc_name where fname like '%D%' and
+ rownum = 1;
[download]