USE INDEX with Active Record finders 6

Posted by pratik
on Thursday, August 06

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

1
2

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 :

1
2
3

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

Leave a response

  1. Tom Ward – August 06, 2009 @ 02:09 PM

    Pratik – this could be useful to some people, but in most cases you should trust MySQL to do the right thing. If MySQL isn’t doing what you want it’s most likely that either your indexes are badly chosen or your table statistics need updating.

    In the case above, index_activities_on_created_at is probably redundant. Queries with just created_at clauses will happily make use of index_activities_on_created_at_and_country_id (assuming the created_at column comes first in the index).

    Still a good tip though, for the general case.

  2. Pratik – August 06, 2009 @ 02:13 PM

    @Tom That makes sense and puts things into perspective. Thanks for the explanation.

  3. grosser – August 06, 2009 @ 04:34 PM

    we also needed to use this hack some times, it could easily be made into a plugin/rails patch, i am thinking of
    Product.all(:index=>’my_index’, :order=>:created_at)

  4. Pratik – August 06, 2009 @ 04:47 PM

    Even simpler to just absue named_scopes :

    1
2
3

    named_scope :index, lambda {|index| {:from => "#{quoted_table_name} USE INDEX(#{index})"}}

Product.index('my_index').all(:order=>:created_at)
  5. Matthew – August 07, 2009 @ 10:19 AM

    To help with this query:

    ‘created_at >= ? AND country_id = ?’

    the index needs to be

    INDEX (country_id, created_at)

    and not

    INDEX (created_at, country_id)

    This could be why MySQL’s picking the ‘wrong’ index?

  6. Morten – August 09, 2009 @ 12:23 AM

    To elaborate what Matthew wrote – MySQL cannot use index columns to the right of a range scan column, which is essentially what happens in this case. For the same reason, you probably always want to have dates or range items as the rightmost column. If you cannot force your data model into working like this, tough luck.

Comment