List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 12 1999 7:40pm
Subject:question: fetching a random row
View as plain text  
>>>>> "Kyle" == Kyle Cronan <root@stripped> writes:

Kyle> Hi everyone,
Kyle> I'm working on a flash card program which stores its cards in a MySQL
Kyle> database of a few thousand cards.  The routines accessing this database
Kyle> are written in C.  I was wondering if there is a way for me to execute a
Kyle> select query on all entries, call mysql_use_result, and then fetch one of
Kyle> the rows at random.  Obviously I can't do a fetch of all the rows and then
Kyle> seek to one of those, as that would take up way too much memory.
Kyle> Any thoughts?  I tried running down the linked list of rows myself (once
Kyle> to get the number of records and again to seek to one at random-yuck) but
Kyle> that didn't work.
Kyle> I admit I just started working with the MySQL API last night... I was
Kyle> using PostgreSQL and this is _unbelievably faster_ and just took a couple
Kyle> of hours to port.  There's just this one issue...

Kyle> Well, thanks for your help,
Kyle> Kyle Cronan
Kyle> <kyle@stripped>

If you would call myqsql_store_result(), you can jump to any row with
mysql_row_seek().

The only other (easy) option is to first do a select with COUNT(*) and 
then get a row on random with 'LIMIT random_rownr,1'

Regards,
Monty

PS: I must some day come up with some nice syntax for this maybe:
    SELECT * from ... ORDER BY RANDOM LIMIT ... ?
Thread
question: fetching a random rowKyle Cronan12 Jun
  • question: fetching a random rowMichael Widenius12 Jun
Re: question: fetching a random rowPaul DuBois12 Jun