CODEWITHSUNDEEP
mysqlruby-on-railsdatabaseruby-on-rails-3indexing
Andy
Andy

Reputation: 1831

Should I index a date column in Ruby on Rails 3.0

Environment: MySql, Rails 3.0.7

In my user table I have a 'last_activity_date' column which is kept up to date using various after_save hooks scattered throughout the app.

I currently have the following named scope:

scope :active, lambda { where('last_activity_date >= ?', 1.month.ago) }

which is used very frequently. It had not seemed worth indexing that column given it's lack of selectivity and because it is updated fairly frequently (daily for some users, very seldom for others).

As the number of users grows, queries using the 'active' scope are taking longer and longer, though. Will indexing the 'last_activity_date' column be worth it in the long run?

Upvotes: 1

Views: 3685

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

If the queries you have look like your comment:

SELECT users.* 
FROM users
WHERE (last_activity_date >= '2011-05-22 00:26:52')
  AND (users.id >= 0)
ORDER BY users.id ASC
LIMIT 1000

then definitely, yes, the performance of these queries will greatly improve with a simple index on last_activity_date

I guess that users.id is the PRIMARY KEY of the table, so there is already an index on it.

When you don't have an index, the query has to scan the whole users table, to check which rows satisfy the condition last_activity_date >= 1 month ago. With a small numbers of users that goes unnoticable. With thousands (or millions) of users, it will be real slow.

Upvotes: 4

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

One rule of thumb says that you should index every column used in a WHERE clause. So I'd index it.

You can tell how selective it is using a query like this.

SELECT last_activity_date, count(*)
FROM users
GROUP BY last_activity_date

Upvotes: 1

Related Questions