Need help in optimizing the query in ruby on rails

Question

I have some 30,000 records in my Raw_deals table and some raw_cities table has some 30 records and each deal is linked with some 5-8 cities.

Now i want to fetch any random deal within some specific cities.

List of those cities can be fetched like this:

@raw_cities = RawCity.where('disabled = ?', 0).map(&:id)

Now i need a deal. I wrote a query but its taking too much time.

@raw_deal = RawDeal.order("RAND()").find(:first,:joins=>[:raw_cities], :conditions=>["raw_cities.id IN (?)",@raw_cities])

you may try to random between randam date ranges, but you will always have trouble with performance if you want to apply a random algorithm in a table that is always growing. — Paulo Abreu
– Paulo Abreu, Commented Nov 8, 2011 at 11:00

Frost · Accepted Answer · 2011-11-08 11:01:39Z

3

The order("RAND()") is probably what's slowing your query down, and since you're only looking for one single deal, you can use a combination of limit and offset to simulate a random order.

Try something like this:

@raw_deal = RawDeal.offset(rand(RawDeal.count)).
                    joins(:raw_cities).
                    where(raw_cities: @raw_cities).
                    first

answered Nov 8, 2011 at 11:01

Frost

12k3 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need help in optimizing the query in ruby on rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related