List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:May 19 2008 5:38pm
Subject:RE: improving random record selection
View as plain text  
>-----Original Message-----
>From: Rob Wultsch [mailto:wultsch@stripped]
>Sent: Monday, May 19, 2008 11:20 AM
>To: Jerry Schwartz
>Cc: Scott Haneda; mysql@stripped
>Subject: Re: improving random record selection
>
>On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz
><jschwartz@stripped> wrote:
>> I might not understand what this is doing, but I think it will
>preferentially sample the ids that are at the end of a gap.
>
>What don't you understand about the query or the way I described it?
[JS] I was being cautious, I didn't have the wit or time to go over it in
detail.
>
>> You say you want a "flat" distribution; by that I think you mean that
>the probability of selecting any given record is the same. If you have
>gaps in your data, I can't think of any way to do that other than be
>assigning a unique and sequential ID to each record. If you ever delete
>a record, you'd have to renumber the remaining ones. Then you'd pick off
>a random value for this unique ID.
>
>There are alternatives. (generating a random number for each row for
>example, take a look at the original conversation). Having to keep the
>sequence holeless would be a pain in the back side, but could be done
>with a trigger running something like I describe in the thread ->
>http://lists.mysql.com/mysql/212838 .
[JS] I think this would work:

SET @rand_rec_num = (SELECT CAST(FLOOR(RAND() * COUNT(*) + 1)
    AS UNSIGNED) FROM bunya_map);

PREPARE get_rand_rec FROM "SELECT * FROM bunya_map LIMIT ?, 1";

EXECUTE get_rand_rec USING @rand_rec_num;

I suppose this could be put into a user function, if you only need a single
value passed back.


>
>--
>Rob Wultsch
>wultsch@stripped
>wultsch (aim)



Thread
improving random record selectionScott Haneda17 May
  • Re: improving random record selectionRob Wultsch18 May
    • RE: improving random record selectionJerry Schwartz19 May
      • Re: improving random record selectionRob Wultsch19 May
        • RE: improving random record selectionJerry Schwartz19 May
  • RE: improving random record selectionJerry Schwartz19 May