USE INDEX with Active Record finders
Published over 2 years ago

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

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