Redmine

When searching issues using the search box in the top-right corner, the response time can become extremely slow if the issues have searchable custom fields. In one of my clients' environments, response times increased from around 5 seconds in Redmine 6.0 to over a minute after upgrading to Redmine 6.1.

This issue is caused by an inefficient join order chosen by MySQL's optimizer. After the change introduced in #43214, MySQL may choose a plan that scans all rows in the custom_values table before filtering by issues.id. On large datasets, this results in a significant performance degradation.

This patch adds a MySQL optimizer hint JOIN_ORDER(issues, custom_values) in the search_result_ranks_and_ids method to force joining the issues table before custom_values, which avoids the inefficient plan and restores the previous performance.