[Gllug] Perl DBI question

[Russell Howe]

On Tue, Oct 03, 2006 at 03:35:06PM +0100, Ryan Cartwright wrote:
> Adrian McMenamin wrote:
> > On Tue, October 3, 2006 2:28 pm, Russell Howe wrote:
> 
> > 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;

Or even more than 2, to batch it up. then you'll end up doing n/factor
selects, instead of n

> 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.

So grab a cursor or something?

> 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.

Indeed (this would be a special case of the above, with an infinite
batch size)

If you can guarantee a fairly small data set, then just query it all and
iterate through the array starting at a random offset. You might even
want to do query a count(*) beforehand as a sanity check if it isn't
performance critical.

The other option would be to do something like (in some weird
pseudocode):

maxid = MAX(id);
rnd = RANDOM(MIN(id), foo);

SELECT * FROM
  (SELECT id, [...] FROM table WHERE id >= rnd)
UNION
  (SELECT id + maxid + 1, [...] FROM table WHERE id < rnd)

> HTH
> Ryan
> [1] I am not an expert in this so do nto claim to know how well it
> optimizes it.

-- 
Russell Howe       | Why be just another cog in the machine,
rhowe at siksai.co.uk | when you can be the spanner in the works?
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug