Use specific mysql index with rails

You can facilitate arel like so to use an index:

 class Issue
   def self.use_index(index)
     # update: OP fixed my mistake
     from("#{self.table_name} USE INDEX(#{index})")
   end
 end

 # then
 Issue.use_index("bla").where(some_condition: true)

Add use_index to ActiveRecord::Relation.

There was some discussion for multiple years about adding this to Rails core, however, it looks like the PR and branch got abandoned.

If you are aware of what database you're using and the limitations of this, you can easily add this to your own codebase so it can be used.

Very similar to @krichard's solution except more generalized for all models to use instead of just Issue.

config/initializers/active_record_relation.rb

class ActiveRecord::Relation
  # Allow passing index hints to MySQL in case the query planner gets confused.
  #
  # Example:
  #   Message.first.events.use_index( :index_events_on_eventable_type_and_eventable_id )
  #   #=> Event Load (0.5ms)  SELECT `events`.* FROM `events` USE INDEX (index_events_on_eventable_type_and_eventable_id) WHERE `events`.`eventable_id` = 123 AND `events`.`eventable_type` = 'Message'
  #
  # MySQL documentation:
  #    https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
  #
  # See https://github.com/rails/rails/pull/30514
  #
  def use_index( index_name )
    self.from( "#{ self.quoted_table_name } USE INDEX ( #{ index_name } )" )
  end
end

This will allow you to use something like:

issue.articles.includes( :category ).use_index( :index_articles_on_issue_id_and_category_id )

And the resulting SQL will include:

FROM articles USE INDEX( index_articles_on_issue_id_and_category_id )