USE INDEX for enforcing indexes

[braindeaf]

I came across an issue this week which I thought would be an easy fix but alas not. A slow SQL query on a significant data set resulted in query lasting 4.5 minutes. Not fun if you need to process 200,000 records and want to be home by tea time.

https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128

We have three models, regular has_many / through setup.

class Person
  has_many :memberships
  has_many :groups, through: :memberships
end

class Membership
  belongs_to :person
  belongs_to :group
end

class Group
  has_many :memberships
  has_many :people, through: :memberships
end

But the performance hit we were finding was a poor index selection.

SELECT `groups`.* FROM `groups` INNER JOIN `memberships` ON `memberships`.`person_id` = `people`.`id` INNER JOIN `groups` ON `groups`.`id` = `memberships`.`group_id` WHERE `groups`.`name` = 'dave';

But we want to force the index on INNER JOIN memberships`

SELECT `groups`.* FROM `groups` INNER JOIN `memberships` USE INDEX(index_something_or_other) ON `memberships`.`person_id` = `people`.`id` INNER JOIN `groups` ON `groups`.`id` = `memberships`.`group_id` WHERE `groups`.`name` = 'dave';

I found this project which had an interesting take on storing hints into a relation, then applying these hints to the Arel::Table and then at the right point adding USE INDEX somewhere like here, given that the table alias is applied at that point.

https://github.com/winebarrel/arel-mysql-index-hint

module Arel # :nodoc: all
  module Visitors
    class ToSql < Arel::Visitors::Visitor
        def visit_Arel_Table(o, collector)
          if Arel::Nodes::Node === o.name
            visit o.name, collector
          else
            collector << quote_table_name(o.name)
          end

          if o.table_alias
            collector << " " << quote_table_name(o.table_alias)
          end

          collector
        end
      end
  end
end

This solution no longer works, and is not likely to. Given that each ActiveRecord model's arel_table is the same object that is passed into Arel::Arel::Visitors::ToSql manipulating it on a per-relation basis seems impossible, and altering the Arel::Table object is just not sensible, it could retain changes after our scope is finished with. I'd really like to a find a way to tackle this in a more Rails-y way as anytime we encounter this we will end up doing. Long-term this isn't going to cut it.

Person.joins("INNER JOIN `memberships` USE INDEX(index_something_or_other) ON `memberships`.`person_id` = `people`.`id`")
      .joins("INNER JOIN `groups` ON `groups`.`id` = `memberships`.`group_id`")
      .where(groups: { name: 'dave' })

I also thought that optimizer_hints might be a good solution, but they didn't work with MariaDB either. I'd love to hear thoughts on how this might be solved.

All the best