Use or Force an Index with Rails

I just found an ActiveRecord hint that is very useful and very undocumented. If you are working with a table who's indexes are sufficiently complex, MySQL will inevitably guess the wrong index at times. To counteract that in SQL, you write:

SELECT * FROM alphas USE INDEX (some_index) WHERE betas = gamas;

Rails does not have a corresponding :use_index for find methods. However, it does have an un-documented :from option.

Alpha.find(:all, :from => "alphas USE INDEX (some_index)", :conditions => "betas = gamas")

Keep this in the back of your mind if you are going to be working with complex database tables any time soon.

Technoblog reader special: $10 off web hosting by FatCow!