Redmine

We have more than 30k issues, some of them use custom fields.
We found that searching by custom fields is slow, takes about 20 seconds the search.
It is slow both searching using API, or searching using GUI.
The custom field in this case is of type integer.

The problem seems to be related to the SQL query that is being done during search.

The query looks like this:

Select * from issues, ...
  AND issues.id IN (SELECT issues.id FROM   issues LEFT OUTER JOIN custom_values
         ...
      )

The code for the subquery is at models/issue_custom_field.rb

There seem to be performance problems with "IN" subqueries in MySQL (https://stackoverflow.com/a/9627668/366209).
It looks like the subquery cannot be cached in MySQL, only the whole query can. (And that doesn't even help, because SQL cache is flushed when a ticket is created anyway)
Ironically the suquery alone runs in less than a second, and the external query alone is fast too.

So maybe the suquery could be reworked to use JOIN instead of IN, that's what they suggest in Stack Overflow. That worked in my tests, when I ran modified SQL queries manually.
I wish I could change the code, but it seems a little complicated for me right now.

Our Redmine was installed from a Bitnami's virtual machine template.

Environment:
  Redmine version                3.3.2.stable
  Ruby version                   2.1.10-p492 (2016-04-01) [x86_64-linux]
  Rails version                  4.2.7.1
  Environment                    production
  Database adapter               Mysql2

MySQL Version: 5.6.35