optimizer_hints carries over to every SELECT a relation builds, but update_all and delete_all drop it without any warning:
Post.optimizer_hints("INDEX(posts index_posts_on_author_id)").where(author_id: 1).update_all(title: "x")
# UPDATE `posts` SET `posts`.`title` = 'x' WHERE `posts`.`author_id` = 1
where, order, limit and annotate all make it into the statement, so you expect the hint to make it in as well. The usual reason to reach for a hint is that MySQL picks the wrong plan on a big table, and a bulk UPDATE or DELETE on that same table hits the same bad plan. Right now the only way out is hand-written SQL.
MySQL and pg_hint_plan both read hints on UPDATE and DELETE right after the keyword:
UPDATE /*+ INDEX(posts index_posts_on_author_id) */ `posts` SET ...
DELETE /*+ INDEX(posts index_posts_on_author_id) */ FROM `posts` WHERE ...
I opened #59313 to emit them there. It’s a small Arel change: UpdateStatement and DeleteStatement carry the hints and the visitors print them. When the statement is rewritten into WHERE id IN (SELECT ...), the hints go into the subquery the same way #59386 does it for count. Global scope hints stay outside and the rest move in, so a hint that names a joined table doesn’t raise an “Unresolved name” warning.
The one behavior change I’d like a second opinion on: hints from a default_scope now apply to these statements too. That looks consistent with how where from a default_scope behaves, but it does mean an existing app could start sending hints on writes it didn’t before.
Is this something Rails wants to support? Any feedback on the PR is welcome.