How to use Rails to efficiently find only recent rows in a large table

Question

I have a 10,000+ row table that I am trying to query in Rails 4. Let's call it events and there are many, many years of past events still in the database. I need to grab only the events with a date of today or in the future. I could do something like:

@events = Event.where('date >= ?', todays_date)

but this still takes a long time as it has to go through every record. Is there a simple way to speed this up? I've even considered that starting from the most recent rows, moving backwards, and limiting to say 1,000 total would be "good enough", although it could potentially leave out an event created a long time ago but with a date in the near future. I'm not even sure how to go about starting from the last row, however. And I feel like there has to be a better solution anyhow.

Any ideas? Thank you in advance!

I have not. I've heard of indexes but I've never used them. I guess I should do some research on that. Any good primers you recommend outside of just Googling "rails index"? — danielsellergren, Jul 09 '14 at 15:27

score 4 · Accepted Answer · edited May 23 '17 at 10:32

4

One thing you can do to speed up the search is to add index to the created_at field (or to the custom date field you are using). Index makes the searching process faster in the database.

You can refer this answer to add index.

edited May 23 '17 at 10:32

Community

1
1

answered Jul 09 '14 at 15:26

Yedhu Krishnan

1,225
15
31

Sounds like indexing should do the job. A perfect case of just needing a point in the right direction in order to learn something very helpful. Thank you very much. – danielsellergren Jul 09 '14 at 15:33
You are welcome. Let me know if you need any further help on adding indices. – Yedhu Krishnan Jul 09 '14 at 15:34

score 3 · Answer 2 · answered Jul 09 '14 at 15:28

3

It sounds like adding an index to your date column could help with efficiency here.

Migration code below:

class AddIndexToEvents < ActiveRecord::Migration
  def change
    add_index :events, :date
  end
end

answered Jul 09 '14 at 15:28

hp4k

356
2
9

How to use Rails to efficiently find only recent rows in a large table

2 Answers2