|
|
Posted by "Satyam" on 06/04/05 01:25
""Richard Lynch"" <ceo@l-i-e.com> wrote in message
news:42333.67.184.124.249.1117826333.squirrel@www.l-i-e.com...
>
> How do you generate a list of 50 random unique IDs in the first place?
>
By using the SQL function to create those randoms
> How to you guarantee that all those IDs are in the database?
>
No need for that. When you do 'order by rand()' SQL understand that you
are adding an extra, unnamed column which contains the value of the function
rand(). Since rand() is, of course, random, the records are ordered in
arbitrary ways. Then you take the first 50 or whatever of those randomly
sorted records. The problem is that to do so, it will have to read
sequentially all of the table to assign each record a random number, and
then sort those. It works for short tables, it is a killer for big ones.
Once upon a time, on a table with an autoincrement field, I first fetched
the minimum and maximum current value of that autoincrement field. Then I
did individual one-record selects (limit 1) with the key equal or greater to
a set of randoms created within those minimum and maximum value. Since two
randoms could produce the same record, I checked the key of each resulting
record against an array which had the primary keys as key. When the count()
of that array reached the number of records I wanted, I was done. This was
for a very little subset of a really large table and in those circumstances
it was faster than the other way.
satyam
> What if a record gets deleted?
>
> I suppose *ONE* option to try would be to just get the full result set,
> and then use PHP's mt_rand() to skip forward/backward a random number of
> records, using something like:
>
> //untested code
> $count = mysql_num_rows($result);
> $ids = array();
> while (count($ids) < 50 && count($ids) < $count){
> $random = mt_rand(0, $count - 1);
> mysql_data_seek($result, $random);
> $id = $result['id'];
> //Note use of $id as key, so duplicates are ignored.
> $ids[$id] = $id;
> }
>
> This will perform MISERABLY when your number of records is closer to 50.
>
> It would be okay only if all searches result in tons and tons of records.
>
> One does have to wonder:
> If the user has put in multiple search criteria, why order randomly? Why
> not put best matches first?
>
> --
> Like Music?
> http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|