Hi guys,
I’m quite new to Ruby on Rails and I am deploying my first app.
I have over 50,000 records and each record has a true or false active
state. On the homepage I find 50 of the active records and order them
randomly.
def index
@records = Record.find_all_by_active(true, :order => 'rand()',
:limit => 50)
end
The problem is that my app is running pretty slow and I’m assuming it’s
because there’s a lot of records to index through.
I have heard about different types of caching but I’m not sure if this
is applicable here as I want the records that are displayed to change on
each visit.
I’m running on Dreamhost & Apache (using Fast CGI).
Would appreciate any suggestions or links.
Thanks
Did you create an index on the ‘active’ boolean column? This would help
a
lot if it’s a database performance bottleneck. You could also use page
or
fragment caching to speed things up further but make sure it has a
proper
index.
Raul
----- Original Message -----
From: “Lee M.” [email protected]
To: [email protected]
Sent: Wednesday, February 20, 2008 6:57 AM
Subject: [Rails-deploy] A lot of records running slow on Dreamhost - any
suggestions
On 2/21/08, Lee M. [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()
@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 R.” [email protected]
To: [email protected]
Sent: Wednesday, February 20, 2008 3:32 PM
Subject: [Rails-deploy] Re: A lot of records running slow on Dreamhost -
any
suggestions
Ugh! You sir, are correct! What a drag…
Again, great catch.
Raul
----- Original Message -----
From: “Anthony R.” [email protected]
To: [email protected]
Sent: Wednesday, February 20, 2008 4:30 PM
Subject: [Rails-deploy] Re: A lot of records running slow on Dreamhost -
any
suggestions
But the rand() would need to be performed on the entire dataset before
the limit is applied otherwise it wouldn’t be random!
Anthony
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.
On Wed, Feb 20, 2008 at 7:30 PM, Anthony R. <
I saw this workaround on the web: SELECT * FROM table WHERE RAND()>0.9
ORDER BY RAND()
Here’s the site link: SQL to Select a random row from a database table
Raul
----- Original Message -----
From: Ericson S.
To: [email protected]
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.
On Wed, Feb 20, 2008 at 7:30 PM, Anthony R.
[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 R.” [email protected]
To: [email protected]
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 M. [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()
On 21.2.2008, at 4.05, Anthony R. wrote:
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!
Here’s Jan Kneschke’s take on the subject:
//jarkko
–
Jarkko L.
http://www.railsecommerce.com
http://odesign.fi
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 R.