Here is the issue I am facing. I have a User model that has_one profile. The query I am running is to find all users that belong to the opposite sex of the current_user and then I am sorting the results by last_logged_in time of all users.
这是我面临的问题。我有一个has_one个人资料的用户模型。我正在运行的查询是查找属于current_user的异性的所有用户,然后我按所有用户的last_logged_in时间对结果进行排序。
The issue is that last_logged_in is an attribute of the User model, while gender is an attribute of the profile model. Is there a way I can index both last_logged_in and gender? If not, how do I optimize the query for the fastest results?
问题是last_logged_in是User模型的属性,而gender是配置文件模型的属性。有没有办法可以索引last_logged_in和性别?如果没有,我如何优化查询以获得最快的结果?
3 个解决方案
#1
1
An index on gender is unlikely to be effective, unless you're looking for a gender that is very under-represented in the table, so index on last_logged_in and let the opposite gender be filtered out of the result set without an index.
关于性别的索引不太可能有效,除非您正在寻找表中非常不足的性别,因此请在last_logged_in上进行索引,并在没有索引的情况下从结果集中过滤掉相反的性别。
It might be worth it if the columns were on the same table as an index on (gender, last_logged_in) could be used to identify exactly which rows are required, but even then the majority of the performance improvement would come from being able to retrieve the rows in the required sort order by scanning the index.
如果列在同一个表上可能是值得的,因为(gender,last_logged_in)上的索引可以用于确切地确定哪些行是必需的,但即便如此,大多数性能改进都来自于能够检索通过扫描索引按所需排序顺序排列的行。
Stick to indexing the last_logged_in column, and look for an explain plan that demonstrates the index being used to satisfy the sort order.
坚持索引last_logged_in列,并查找演示用于满足排序顺序的索引的解释计划。
#2
1
add_index :users, :last_logged_in
add_index :profiles, :gender
This will speed up finding all opposite-sex users and then sorting them by time. You can't have cross-table indexes.
这将加快找到所有异性用户,然后按时间排序。您不能拥有跨表索引。
#3
1
I am just writing the query for that
我只是为此编写查询
In your User model
在您的用户模型中
def self.get_opposite_sex_users(current_user)
self.joins([:profile]).where("profiles.gender = ?", (current_user.profile.gender == 'M') ? 'F' : 'M').order('users.last_logged_in DESC')
end
In your action you can call it by User.get_opposite_sex_users(current_user)
在您的操作中,您可以通过User.get_opposite_sex_users(current_user)调用它
#1
1
An index on gender is unlikely to be effective, unless you're looking for a gender that is very under-represented in the table, so index on last_logged_in and let the opposite gender be filtered out of the result set without an index.
关于性别的索引不太可能有效,除非您正在寻找表中非常不足的性别,因此请在last_logged_in上进行索引,并在没有索引的情况下从结果集中过滤掉相反的性别。
It might be worth it if the columns were on the same table as an index on (gender, last_logged_in) could be used to identify exactly which rows are required, but even then the majority of the performance improvement would come from being able to retrieve the rows in the required sort order by scanning the index.
如果列在同一个表上可能是值得的,因为(gender,last_logged_in)上的索引可以用于确切地确定哪些行是必需的,但即便如此,大多数性能改进都来自于能够检索通过扫描索引按所需排序顺序排列的行。
Stick to indexing the last_logged_in column, and look for an explain plan that demonstrates the index being used to satisfy the sort order.
坚持索引last_logged_in列,并查找演示用于满足排序顺序的索引的解释计划。
#2
1
add_index :users, :last_logged_in
add_index :profiles, :gender
This will speed up finding all opposite-sex users and then sorting them by time. You can't have cross-table indexes.
这将加快找到所有异性用户,然后按时间排序。您不能拥有跨表索引。
#3
1
I am just writing the query for that
我只是为此编写查询
In your User model
在您的用户模型中
def self.get_opposite_sex_users(current_user)
self.joins([:profile]).where("profiles.gender = ?", (current_user.profile.gender == 'M') ? 'F' : 'M').order('users.last_logged_in DESC')
end
In your action you can call it by User.get_opposite_sex_users(current_user)
在您的操作中,您可以通过User.get_opposite_sex_users(current_user)调用它