On Thu, June 2, 2005 4:31 pm, Brian Dunning said:
> I am using a routine to find 50 random records in a large MySQL
> database (about a million records) where I generate a list of 50
> random unique ID's, and then use MySQL's "in" command to find them. I
> can't use "order by rand()" due to its performance hit.
>
> But I have to take it one more step: I want to first limit my found
> set to those matching a different search criteria, and then find 50
> of those.
If your search criteria is on an indexed field, you may find that the
order by rand() is much much much less of a problem once you winnow your
record set down to the search criteria.
Or, more precisely, if your search parameters get you a small result set,
the order by rand() will be "fast" because that is done AFTER the smaller
result set is determined.
If your search parameters get you a HUGE set, order by rand() not so fast.
> Anyone? Can this be done all within MySQL, or is it going to require
> some humongo PHP arrays?
How do you generate a list of 50 random unique IDs in the first place?
How to you guarantee that all those IDs are in the database?
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
