Topic: A lot of records running slow on Dreamhost - any suggestions

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

Re: A lot of records running slow on Dreamhost - any suggestions

Hey,
I'm still fresh too, but I believe a little tweak to help would be provide an actual sql statement.

But there's probably more ways, like only selecting the fields you want to display using the :select option of find.

Personal Site josephhsu.com
Working with Rails profile
Twitter jhsu

Re: A lot of records running slow on Dreamhost - any suggestions

Thanks for the quick reply.
How do you write an actual sql statement in Ruby and how does that compare to what I already have?

Yeah, I've tidied by code up quite a bit (it was actually a lot messier to what you see above at first). I've added :select to state the two fields I want to pull out but doesn't make much difference.

Re: A lot of records running slow on Dreamhost - any suggestions

http://www.mysqlperformanceblog.com/200 … imization/

ORDER BY rand() Is pretty slow, but so are ORDER BY ... LIMIT .. statements.

Make sure you have the proper columns as indexes. And that should help your application a lot.

Another thing that could improve the speed of the search is to use a subquery to select the random 50 by ID only.

SELECT id, col1, col2, ... , colN FROM tab WHERE conditions ORDER BY RAND() LIMIT m

To a query of the form:

SELECT id, col1, col2, ... , colN FROM tab WHERE id IN (SELECT id FROM tab WHERE conditions ORDER BY RAND() LIMIT m)


Doing a limit in a subquery for an IN () requires 5.1 MySQL I think though. It doesn't work for 5.0 I know, but I think they added that feature in 5.1.

oh, i found a work around for that problem though:

SELECT 
tbl1.*
FROM (SELECT id FROM tbl1 ORDER BY RANDOM() LIMIT m) AS A
JOIN tbl1 ON tbl1 = A.id

should produce a similar effect as a subquery with a limit

SQLite3 supports subqueries with limits though.

No idea about PostgreSQL, I don't use that one.

Last edited by mr_shine (2008-02-25 14:22:27)

Re: A lot of records running slow on Dreamhost - any suggestions

Thanks I'll take a look at that