depending on the business requirements for doing a random select anyway, you may be able to create "randomvalue" column on the table and then do a select against that. Have a background thread (or a stored procedure) that then updates all the rows with a random value every 10 minutes (or every hour?). That way the time consuming part is not customer facing.
This depends on why you need a random selection in the first place ofcourse! Cheers, Anthony Richardson On 2/21/08, Raul <[EMAIL PROTECTED]> wrote: > > I saw this workaround on the web: SELECT * FROM table WHERE RAND()>0.9 > ORDER BY RAND() > > Here's the site link: > http://www.petefreitag.com/item/466.cfm > > Raul > > ----- Original Message ----- > From: Ericson Smith > To: rubyonrails-deployment@googlegroups.com > Sent: Wednesday, February 20, 2008 4:36 PM > Subject: [Rails-deploy] Re: A lot of records running slow on Dreamhost - any > suggestions > > Problem is that it has to do the rand first, then the limit afterwards. > > I just tried this on a table with around 4 million records: > select * from galleries order by rand() limit 10; > > I stopped it after 3 minutes. > > This took .001 seconds on the same table: > select * from galleries order by id limit 10; > > On an unindexed field, it took 3.5 seconds: > select * from galleries order by thumb_exists limit 10; > > Order by rand() really sucks. We try not to use it at all. > > - Ericson Smith > http://www.funadvice.com > > On Wed, Feb 20, 2008 at 7:30 PM, Anthony Richardson > <[EMAIL PROTECTED]> wrote: > > > > But the rand() would need to be performed on the entire dataset before > > the limit is applied otherwise it wouldn't be random! > > > > Anthony > > > > > > > > > > On 2/21/08, Raul <[EMAIL PROTECTED]> wrote: > > > > > > @Anthony - Good catch. I forgot about that. > > > > > > But with the LIMIT of 50 it shouldn't be that big of a deal. Maybe the > > > generated SQL is not what we think it is? I guess you could also try > > > replacing this with a find_by_sql statement and see if it performs any > > > better. > > > > > > @records = Record.find_by_sql(SELECT * FROM records WHERE active=1 ORDER > BY > > > rand() LIMIT 50") > > > > > > Raul > > > > > > > > > ----- Original Message ----- > > > From: "Anthony Richardson" <[EMAIL PROTECTED]> > > > To: <rubyonrails-deployment@googlegroups.com> > > > Sent: Wednesday, February 20, 2008 3:32 PM > > > Subject: [Rails-deploy] Re: A lot of records running slow on Dreamhost - > any > > > suggestions > > > > > > > > > > > > > > On 2/21/08, Lee Munroe > <[EMAIL PROTECTED]> wrote: > > > >> @records = Record.find_all_by_active(true, :order => 'rand()', > > > >> :limit => 50) > > > > > > > > you might want to double check, but I recall something about the > > > > rand() function of mySQL being a slow pig. What is the performance > > > > like without the rand() > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Deploying Rails" group. To post to this group, send email to rubyonrails-deployment@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-deployment?hl=en -~----------~----~----~----~------~----~------~--~---
