CODEWITHSUNDEEP
mysqlruby-on-rails
Mini
Mini

Reputation: 1170

Rails select top n records per group (memory leak)

I have this method that using find_by_sql which is return 10 latest records of each source 

def latest_results
    Entry.find_by_sql(["
    select x.id,x.created_at,x.updated_at,x.source_id,x.`data`,x.`uuid`,x.source_entry_id
    from
      (select t.*,
        (@num:=if(@group = `source_id`, @num +1, if(@group := `source_id`, 1, 1))) row_number
          from (
      select d.id,d.created_at,d.updated_at,d.source_id,d.`data`,d.`uuid`,d.source_entry_id
      from `streams` a
      JOIN `stream_filters` b
      on b.stream_id=a.id
      JOIN `filter_results` c
      on c.filter_id=b.id
      JOIN `entries` d
      on d.id=c.entry_id
      where a.id=?
        ) t
      order by `source_id`,created_at desc
      ) as x
      where x.row_number <= 10
      ORDER BY x.created_at DESC
    ",self.id])
  end

It's working properly on local environment with limited records. I have t2.micro which has 2 Gib memory to serving the application. Now this query running out my whole memory and app get frizzing. any suggestion how can I do it better ? I want to solve this without increasing the size of machine.

Upvotes: 1

Views: 889

Answers (1)

dre-hh
dre-hh

Reputation: 8044

I had a similar problem once. The solution with mysql variables seems neat at the first place, though it is hard to optimize. It seems that is doing a full table scan in your case.

I would recommend to fetch the sources you want to display first. And then run a second query with multiple top 10 selects, one per source, all combined with a union.

The union top 10 select will have some repetive statements which you can easily autogenerate with ruby. 

# pseudo code
sources = Entry.group(:source).limit(n)
sql = sources.map do |source|
   "select * from entries where source = #{source} order by created_at limit 10"
end.join("\nunion all\n")

Entry.find_by_sql(sql)

Upvotes: 1

Related Questions