Here is my modified act_as_searchable.rb (we are still using Redmine 1.4.x but the fix can easily be applied to Redmine 2.x).
Our MySQL version is 5.5.
Using LOWER in LIKE queries is just a waste of time, unless you changed the coalescence of the searched columns, the comparison will be case insensitive. So these should be removed. (Lines 88 and 97).
The main culprit though is the IN subquery for the custom_values table. The WHERE clause also match the customized_id with the parent query id. This is not necessary when using IN. So we removed it from the WHERE clause. However MySQL makes it a DEPENDENT SUBQUERY and will not cache the results. This means that the query will be repeated for every issue. A quick hack is to wrap the subquery in another subquery to make it part of a FROM clause. This, MySQL will create a temporary table for the subquery and reuse it for every issues. (line 97)
Our database contains almost 3000 issues and more than 50000 rows in the custom_values tables. These fixes made a s