Redmine

Having any custom fields searchable majorly impacts search performance.

This would be helped by using something like Elasticsearch - See #10897 and #9180

For example, with just a few customfields being made searchable, it took 30 seconds for me to search for a single phrase.
Inspecting the database query the slow part is the COUNT:

(23445.6ms)

EXPLAIN SELECT COUNT(DISTINCT "issues"."id") FROM "issues" LEFT OUTER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT OUTER JOIN "journals" ON "journals"."journalized_id" = "issues"."id" AND (journals.private_notes = 'f' OR (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking'))) AND "journals"."journalized_type" = 'Issue' WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND (((LOWER(subject) LIKE '%remotetransportexception%') OR (LOWER(issues.description) LIKE '%remotetransportexception%') OR (LOWER(journals.notes) LIKE '%remotetransportexception%') OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 1) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 1) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 1))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 4) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 4) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 4))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 11) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 11) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 11))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 12) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 12) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 12))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 13) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 13) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 13))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 17) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 17) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 17))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 18) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 18) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 18))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 19) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 19) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 19))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 21) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 21) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 21))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 26) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 26) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 26))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 27) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 27) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 27))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 31) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 31) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 31))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 32) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 32) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 32))) OR issues.id IN (SELECT customized_id FROM custom_values WHERE customized_type='Issue' AND customized_id=issues.id AND LOWER(value) LIKE '%remotetransportexception%' AND custom_values.custom_field_id = 25) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = 25) AND issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = 25)))));