USE INDEX with Active Record finders(转)

最新推荐文章于 2021-01-20 22:28:12 发布

okela1985851010

最新推荐文章于 2021-01-20 22:28:12 发布

阅读量71

点赞数

分类专栏： rails 文章标签： Rails ActiveRecord

本文链接：https://blog.csdn.net/okela1985851010/article/details/83766882

版权

rails 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

可以通过强制指定index的方法优化find
MySQL doesn’t always pick the right index for your queries. Hence, sometimes you must tell it which index to use. Consider the example :

Activity.all(:conditions => ['created_at >= ? AND country_id = ?', 10.days.ago, 79])
Running EXPLAIN on the above query :

EXPLAIN SELECT * FROM `activities` WHERE (created_at >= '2009-07-27 12:58:44' AND country_id = 79);

Possible keys : index_activities_on_created_at,index_activities_on_created_at_and_country_id
Using the key : index_activities_on_created_at
As you can see, even though the table has index on both the fields involved in the query – index_activities_on_created_at_and_country_id, MySQL still uses index_activities_on_created_at. You can explicitly ask MySQL to use the index you want by supplying USE INDEX([url]http://dev.mysql.com/doc/refman/5.1/en/index-hints.html[/url])

SELECT * FROM `activities` USE INDEX(index_activities_on_created_at_and_country_id)
WHERE (created_at >= '2009-07-27 12:58:44' AND country_id = 79);
Active Record does not have any finder option to specify the index hint. Hence the solution is to exploit the :from option :

[color=red]from = "#{quoted_table_name} USE INDEX(index_activities_on_created_at_and_country_id)"
Activity.all(:from => from,
:conditions => ['created_at >= ? AND country_id = ?', 10.days.ago, 79])[/color]