Redmine

Environment: Redmine 7.0.2, Rails 8.1.4, Ruby 4.0.9, MariaDB 11.8.8, adapter mysql2.
Data: ~112k issues, ~449k custom_values, ~6.3k enabled_modules, 9 searchable issue custom fields.

Problem

Searching text inside a project with many issues takes 36-131 s (almost all in ActiveRecord), so it times out behind a reverse proxy. Global search ("all projects") and small projects are fast (< 1.5 s).

Analysis

1. The fix of #43651 (JOIN_ORDER(issues, custom_values) in search_result_ranks_and_ids) is only applied when ActiveRecord::Base.connection.supports_optimizer_hints? is true. Rails returns false for any MariaDB (!mariadb? && database_version >= "5.7.7"), so on MariaDB the hint is never added. Besides, MariaDB only supports new-style optimizer hints since 12.0.
2. In search_scope, the following line produces issues.project_id IN ('123'), because Rails quotes numeric bind values as strings on MySQL adapters:
   

   scope = scope.where("#{searchable_options[:project_key]} IN (?)", projects.map(&:id))
   

   
   With the quoted value, MariaDB 11.8 does not propagate the constant to projects.id. The plan starts with a full scan of enabled_modules and repeats the issues/custom_values lookup for every project.
3. The same query with IN (123), unquoted, starts with projects (const) and runs in 0.4 s instead of 131 s.

Workaround applied locally (lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb, search_scope):

scope = scope.where("#{searchable_options[:project_key]} IN (#{projects.map { |p| p.id.to_i }.join(",")})")

With this change the same search takes about 0.5 s and returns the same results.

Passing integer ids unquoted, or an equivalent fix for MariaDB, would avoid the regression on MariaDB, where the optimizer-hint approach cannot be used. I can provide the full EXPLAIN output if useful.

Related to #43651.