`optimizer_hints` precedence

rubyonrails-core
1

Summary

Currently, when using optimizer hints in ActiveRecord, each call to optimizer_hints appends the hint to the end of the array.

As per MySQL docs:

Duplicate hints: For a hint such as /*+ MRR(idx1) MRR(idx1) */, MySQL uses the first hint and issues a warning about the duplicate hint.

This means that in Rails, the earlier hint takes precedence, which is inconsistent with other limiting behaviors in ActiveRecord.

Also, this is different from how other limiting/scoping methods (such as limit) behave, where the last-applied value wins.

Example (current behavior):

Issue.all.optimizer_hints("MAX_EXECUTION_TIME(555)").optimizer_hints("MAX_EXECUTION_TIME(222)").to_sql
# => "SELECT /*+ MAX_EXECUTION_TIME(555) MAX_EXECUTION_TIME(222) */ `issues`.* FROM `issues`"

Issue.all.limit(1).limit(2).to_sql
# => "SELECT `issues`.* FROM `issues` LIMIT 2"

This is also problematic for models using optimizer_hints in the default_scope because the default optimizer hint will always win, even if somebody attempts to overwrite it later on.

My current solution used the private api and updated the optimizer_hints_values directly. This isn’t ideal becuase private api can change without any notice.

Proposed Solutions

  1. Last-applied wins: Make optimizer hints behave like limit, so the last hint wins. This could be done by adding new optimizer hints to the front of the array, or by reversing the array before generating the SQL statement.
  2. Public API for removal: Provide a public API to remove an optimizer hint (similarly to what reorder does).

The second option would probably easier to roll out since it’s a non-breaking change.

2

Make optimizer hints behave like limit , so the last hint wins.

I’m afraid this is not an option as long as optimizer_hints is generic and can be used for a variety of legit hints combination, such as: Model.all.optimizer_hints("MAX_EXECUTION_TIME(555)").optimizer_hints("INDEX (table_name idx1)") where max_execution_time may be applied as a default scope but index hint is applied later in the code.

Similar issue happens with the unscope/reorder type of thing, developers may only need to unscope one of the hints and not everything.

Perhaps an API needs to allow developers to specify a “named” hint such as .optimizer_hint(idx_hint: "INDEX(tbl_name, idx1)" where idx_hint: would serve two purposes:

  • Ensure hint uniqueness. Any subsequent call to optimizer_hint with the same key would override the existing one but hints with different keys would concatenate
  • Ability to explicitly unscope specific hint, i.e. unscope_optimizer_hint(:idx_hint)
3

I meant “make the last one of a kind win” so in your example we’d still apply both hints. But in this case:

Model.all.optimizer_hints("MAX_EXECUTION_TIME(555)").optimizer_hints("INDEX (table_name idx1)").optimizer_hints("MAX_EXECUTION_TIME(666)") we’d only apply INDEX(...) and MAX_EXECUTION_TIME(666).

But I agree that named hints would also solve this issue.

4

make the last one of a kind win

That’s certainly would the desired behavior. But I think the challenge here is that it would obligate Rails to know the specifics of MySQL hints to do at least very basic parsing to tell that MAX_EXECUTION_TIME and INDEX what uniquely identifies a hint kind. The implementation itself won’t be complex so not a huge concern but it’s more the fact that Rails will now be forced to maintain support for something that is specific to one particular database. Again while not the biggest feature to support but I believe it shares the same concerns as to why optimizer_hints feature was made generic and not specific such as index_hint or .max_execution_time