|
|
Posted by Jochem Maas on 10/11/50 11:14
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
>
Navigation:
[Reply to this message]
|