David Tucker wrote:
[OP]
I have a MySQL database with about a million records. I'd like to use the
SQL command "order by RAND()" but my ISP won't let me: whenever the server
gets spidered, Google overloads their MySQL server because of all the
overhead of that command. I can't just cloak the spiders because I need them
to find the random pages.
So...what I've been doing for small sets of records is to use PHP to
generate a bunch of random record ID's, then I construct a long SQL
statement to find all the matching records. This works, but if I want to
generate a big index page to list a hundred or a thousand records, it could
get pretty clunky.
Anyone have any better suggestions? :)
[/OP]
Well, not knowing what the random records are used for and not really
knowing how random they need to be ;) I might suggest just selecting a
random number and then select all the records after it until you have the
amount you need. Probably not the best solution, but might work for you.
loose thought:
add a field to the table e.g. 'randomizer'.
create a cronjob that (re)fill the fields with random numbers
how ever often you need (+ how often the server can handle it)
then when you need to do a random select use 'ORDER BY randomizer'
(don't forget to index!)
not sure how well that would fare on a million records, but at least
it gives you the opportunity of offloading the heavy processing to sometime
in the middle of the night (for most of the servers users) to minimize
impact on the site/system.
--Codefox
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
