# Advanced ActiveRecord joins with index hints

**URL:** <https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128>\
**Category:** rubyonrails-talk\
**Tags:** activerecord\
**Created:** [July 1, 2024, 3:37pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128 "2024-07-01T15:37:02Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![braindeaf](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/braindeaf/32/9731_2.png) [@braindeaf](https://discuss.rubyonrails.org/u/braindeaf)\
**Post date:** [July 1, 2024, 3:37pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/1 "2024-07-01T15:37:03Z")

</div>

🧑‍🦲 his really tough one hit me today. I have three models, something like this…

```ruby
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.

```ruby
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.

```ruby
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 ☹

Anyone had any success or joy working with this?

RobL

---

<div class="post-metadata">

**Author:** ![PauloVilarinho](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/paulovilarinho/32/12133_2.png) [@PauloVilarinho](https://discuss.rubyonrails.org/u/PauloVilarinho)\
**Post date:** [July 1, 2024, 5:27pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/2 "2024-07-01T17:27:39Z")

</div>

Have you tried using the has\_many `through` flag? [Here are](https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association) the docs for it.

---

<div class="post-metadata">

**Author:** ![braindeaf](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/braindeaf/32/9731_2.png) [@braindeaf](https://discuss.rubyonrails.org/u/braindeaf)\
**Post date:** [July 1, 2024, 6:26pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/3 "2024-07-01T18:26:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![e\_a](https://avatars.discourse-cdn.com/v4/letter/e/9fc348/32.png) [@e\_a](https://discuss.rubyonrails.org/u/e_a)\
**Post date:** [July 1, 2024, 8:00pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/4 "2024-07-01T20:00:15Z")

</div>

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

```ruby
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.

---

<div class="post-metadata">

**Author:** ![Lorin\_Thwaits](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/lorin_thwaits/32/9491_2.png) [@Lorin\_Thwaits](https://discuss.rubyonrails.org/u/Lorin_Thwaits)\
**Post date:** [July 2, 2024, 9:00am UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/5 "2024-07-02T09:00:15Z")

</div>

> [@braindeaf](#):
>
> 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.

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

```ruby
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' })

```

---

<div class="post-metadata">

**Author:** ![braindeaf](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/braindeaf/32/9731_2.png) [@braindeaf](https://discuss.rubyonrails.org/u/braindeaf)\
**Post date:** [July 2, 2024, 9:08am UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/6 "2024-07-02T09:08:23Z")

</div>

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.

```ruby
# 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

```

---

<div class="post-metadata">

**Author:** ![braindeaf](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/braindeaf/32/9731_2.png) [@braindeaf](https://discuss.rubyonrails.org/u/braindeaf)\
**Post date:** [July 5, 2024, 11:47am UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/7 "2024-07-05T11:47:48Z")

</div>

> [@braindeaf](#):
>
> `visit_Arel_Table`

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)](https://github.com/rails/rails/discussions/52277)

---

<div class="post-metadata">

**Author:** ![Lorin\_Thwaits](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/lorin_thwaits/32/9491_2.png) [@Lorin\_Thwaits](https://discuss.rubyonrails.org/u/Lorin_Thwaits)\
**Post date:** [July 5, 2024, 1:00pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/8 "2024-07-05T13:00:53Z")

</div>

> [@braindeaf](#):
>
> I want to offer something that is easier to maintain and is more Rails-y

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](https://github.com/lorint/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.)

---

<div class="post-metadata">

**Author:** ![braindeaf](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/braindeaf/32/9731_2.png) [@braindeaf](https://discuss.rubyonrails.org/u/braindeaf)\
**Post date:** [July 5, 2024, 1:17pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/9 "2024-07-05T13:17:29Z")

</div>

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 🙂

Much appreciated.

---

<div class="post-metadata">

**Author:** ![lcreid](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/lcreid/32/10856_2.png) [@lcreid](https://discuss.rubyonrails.org/u/lcreid)\
**Post date:** [August 21, 2024, 3:13am UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/10 "2024-08-21T03:13:02Z")

</div>

For some history, [this issue](https://github.com/rails/rails/issues/27110) was opened many years ago. It was finally addressed in [this PR](https://github.com/rails/rails/pull/42046), but by then it was felt that the optimizer hints were the modern way to go, so the PR was never merged.

---

<div class="post-metadata">

**Author:** ![andatki](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/andatki/32/10310_2.png) [@andatki](https://discuss.rubyonrails.org/u/andatki)\
**Post date:** [September 10, 2024, 9:01pm UTC](https://discuss.rubyonrails.org/t/advanced-activerecord-joins-with-index-hints/86128/11 "2024-09-10T21:01:19Z")

</div>

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.

> **[Optimizer Hints](https://mariadb.com/kb/en/optimizer-hints/)**
>
> Optimizer hints
> There are some options available in SELECT to affect the execution plan. These are known as optimizer hints.
> HIGH PRIORITY
> HIGH\_PRIORITY gives the statement a...