Hi,

I Recently migrated from Redmine 2.0.3 to latest stable release 2.6.0. All went smooth and working fine. But now issues is Search became extremely slow.

Most of search queries taking more than 13Sec. to finish.

Below is one snapshot from mysql slow query log.

Time: 141222 8:19:26¶

User@Host: redmine[redmine] @ localhost [] Id: 4¶

Query_time: 13.278949 Lock_time: 0.000320 Rows_sent: 1 Rows_examined: 6920584¶

SET timestamp=1419236366;
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 = 0 OR (1=0)) 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 ((projects.is_public = 1 AND ((issues.is_private = 0 OR issues.author_id = 48 OR issues.assigned_to_id IN (48,12,26,60)))) OR (projects.id IN (5,7,13,29,31,50) AND ((issues.is_private = 0 OR issues.author_id = 48 OR issues.assigned_to_id IN (48,12,26,60)))) OR (projects.id IN (19,20) AND ((issues.author_id = 48 OR issues.assigned_to_id IN (48,12,26,60)))) OR (projects.id IN (21,25,26,50) AND ((issues.is_private = 0 OR issues.author_id = 48 OR issues.assigned_to_id IN (48,12,26,60))))))) AND (issues.project_id IN (5)) AND (((LOWER(subject) LIKE '%inox%') OR (LOWER(issues.description) LIKE '%inox%') OR (LOWER(journals.notes) LIKE '%inox%') OR issues.id IN (SELECT cfs.customized_id FROM custom_values cfs WHERE cfs.customized_type='Issue' AND cfs.customized_id=issues.id AND LOWER(cfs.value) LIKE '%inox%' AND cfs.custom_field_id IN (3,4,6,7,8,9,10,11,12,14,19,20,22,24,29,30,31,45,52,70,73,74,75,76,82,85,88,90,91,92,102,103,105,106,107,108,109,110,111,112,113,115,116,118,119,124,126,128,129,203,218,224,227,231,232,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,266,267,268,269,270,271,272,275,276,278,279,287,288) AND ((1=1) AND (issues.tracker_id IN (SELECT tracker_id FROM custom_fields_trackers WHERE custom_field_id = cfs.custom_field_id)) AND (EXISTS (SELECT 1 FROM custom_fields ifa WHERE ifa.is_for_all = 1 AND ifa.id = cfs.custom_field_id) OR issues.project_id IN (SELECT project_id FROM custom_fields_projects WHERE custom_field_id = cfs.custom_field_id))))));

And please find the attachment of Explain output for the above mention query.

Due to this I am stuck and only solution left with revert back to old Database unless some one provide me the solution.

Thanks,