我应该在Ruby on Rails 3.0中索引日期列(Should I index a date column in Ruby on Rails 3.0)

我应该在Ruby on Rails 3.0中索引日期列(Should I index a date column in Ruby on Rails 3.0)

环境：MySql，Rails 3.0.7

在我的用户表中，我有一个'last_activity_date'列，它使用分散在整个应用程序中的各种after_save挂钩保持最新。

我目前有以下命名范围：

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

经常使用。 鉴于它缺乏选择性并且因为它经常更新（对于一些用户来说是每天，对其他用户来说很少），因此似乎没有值得索引该列。

随着用户数量的增长，使用“活动”范围的查询花费的时间越来越长。 从长远来看，将'last_activity_date'列编入索引是否值得？

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?

最满意答案

如果您的查询看起来像您的评论：

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

那么肯定的是，这些查询的性能将通过last_activity_date上的简单索引得到极大改善

我猜那个users.id是表的PRIMARY KEY ，因此它上面已有索引。

---

如果没有索引，则查询必须扫描整个users表，以检查哪些行满足条件last_activity_date >= 1 month ago 。 少数用户不可观察。 拥有数千（或数百万）用户，这将是非常缓慢的。

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.