[Gllug] Perl DBI question

[Ryan Cartwright]

Adrian McMenamin wrote:
> On Tue, October 3, 2006 2:28 pm, Russell Howe wrote:
> 
>> Also, note that RANDOM() sometimes isn't all that random depending on
>> your RDBMS. I'd check the coverage of results for your particular
>> application.
>>
> I tried SELECT item1, item2 ORDER BY RAND() LIMIT 1 which seems to work
> well on Firefox on linux (there are only three items in the database at
> the moment so that's not much to base this on!)

Russell's comment on the randomness of RANDOM() not withstanding, I
should have mentioned that according to some[1] MySQL optimizes this
badly (using a temporary table and a filesort) and it can be a bit slow
on larger tables. There's more information and suggestions on selecting
random records in the comments here:

http://dev.mysql.com/doc/refman/5.0/en/select.html

> Now, I want to do this.
> 
> If the random event gives me item N, the next one I want is item N + 1
> (however as N is the index N + 1 means the next item not necessarily the
> arthmetic sum) unless N + 1 > EOF in which case I want the first item.

I can think of two ways
1. dump the IDs into an array before doing the random select. Then you
can do some array stuff in Perl to grab the ID following the one you
have selected.
2. once you have the Random ID use the following SQL to grab that record
and the next one then just use the latter of the two rows returned:

SELECT item1, item2 WHERE ID>=PrevRandId ORDER BY IDfield LIMIT 2;

So PrevRandId is the ID of the random row you grabbed earlier and
IDfield is a unique index for the table. The second method would of
course not wrap around the record list so if the random ID returned is
the last then the second select statement would return the one row not
two. The first method has the downside that the data could change
between grabbing the list of IDs and doing the random select.

Also if you are going to the bother of grabbing the IDs into an array
anyway (and the possibility of the data changing is not a big issue) you
may as well do the random stuff on the Perl array and then use a single
select to grab the row(s) your want.

HTH
Ryan
[1] I am not an expert in this so do nto claim to know how well it
optimizes it.
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug