Advanced ActiveRecord joins with index hints

rubyonrails-talk
1

:person_bald: his really tough one hit me today. I have three models, something like this…

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

I am used to doing things like Person.joins(:groups).where(groups: { name: 'dave' }). The SQL used by this query will be something like.

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';

In our case there is an issue with the index being used for the join and we want to direct the query optimizer to use a specific index for the first join table.

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';

This is currently impossible in Rails. But I am stuck on how I could possibly even think about patching this in to help us along. I did look at .optimizer_hints which looked promising but MariaDB doesn’t seem to support them in the way I want to use them, so dead end :frowning_face:

Anyone had any success or joy working with this?

RobL

2

Have you tried using the has_many through flag? Here are the docs for it.

3

I can see that my example wasn’t 100% accurate, but the question is concerning how you can add indexing hints. Usually you would add the instruction directly after the table name something like <table_name> USE INDEX(blah). If I could inject this into the query just prior to execution that would do it but I’d like a better solution.

4

Not as nice but you could do the joins yourself and then provide a custom join for that:

groups = Group.where name: 'dave'
memberships = Membership.joins(:group).merge groups
mem_join_with_custom_index = 'INNER JOIN `memberships` USE INDEX(index_something_or_other) ON `memberships`.`person_id` = `people`.`id`'
persons = Person.joins(mem_join_with_custom_index).merge memberships

Obviously you could package this sort of thing up with scopes, methods or maybe even a relationship extension to make it have a better feel. Was just making it overly explicit to break it down.

1 Like
5

It’s not entirely Rails-y (or ActiveRecord-ey?), but you can do:

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' })
1 Like
6

You’re right Lorin. The thing I dislike the most is where you have to ditch the Rails way to try and force a solution. So your solution works great, but it’s not sustainable if we need to re-use the same thing everywhere.

I used inspiration from an abandoned repo. Which is kind of what I want but I don’t 100% like it either…swings and roundabouts and see-saws.

# based on the shelved https://github.com/mirakui/activerecord-mysql-index-hint
# This can temporarily set a table_index on a model's Arel::Table which allows us
# to hack `visit_Arel_Table` to inject the USE INDEX at the right point

ActiveRecord::Base.extend(Module.new do
  def with_index(index)
    arel_table.table_index = index
    yield
  ensure
    arel_table.table_index = nil
  end
end)

Arel::Table.prepend(Module.new do
  attr_accessor :table_index
end)

Arel::Visitors::MySQL.prepend(Module.new do
  def visit_Arel_Table(o, collector)
    s = "" << quote_table_name(o.name)
    s << " USE INDEX (#{o.table_index})" if o.table_index
    s << " " << quote_table_name(o.table_alias) if o.table_alias
    collector << s
  end
end)

Membership.with_index('some_index') do
  Person.joins(:groups).to_sql
end
7

Well, @Lorin_Thwaits I had to quit and go with the less Rails-y and more it actually works this time as you suggested. I love ActiveRecord but there are clearly things it can’t do and I mean that’s fine we get so much good stuff for free. But when it comes to shoe-horning things in that meet the needs of our application, I want to offer something that is easier to maintain and is more Rails-y and sometimes it’s just asking too much.

Here’s hoping. USE INDEX for enforcing indexes · rails/rails · Discussion #52277 (github.com)

8

I do have a cooool trick up my sleeve … there are some dope Arel-centric stylings I do in a gem I maintain called The Brick. This thing walks the full AREL tree in order to parse table alias names chosen during complex JOINs. Conceivably it could be further enhanced to apply hints at specific JOIN points.

Will keep you posted about my findings…

(btw, have you tried the Trilogy adapter in your setup? Just curious. Have been involved with some of that effort.)

9

Oh, I’ve heard of The Brick, perhaps it was from a LinkedIn post at some point. I will take a look. I’ve not looked at Trilogy either but happy to take a closer look too :slight_smile:

Much appreciated.

10

For some history, this issue was opened many years ago. It was finally addressed in this PR, but by then it was felt that the optimizer hints were the modern way to go, so the PR was never merged.

11

Could the optimizer_hints() method in Active Record be changed to place the MariaDB compatible hints in the right spot, in a future version, or via a gem? In PostgreSQL using the pg_hint_plan extension, the hints are expressed as comments in front of the SQL query text.