USE INDEX with Active Record finders(转)

可以通过强制指定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(http://dev.mysql.com/doc/refman/5.1/en/index-hints.html)

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 :

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])