Redmine

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.

1. Time: 141222 8:19:26
2. User@Host: redmine[redmine] @ localhost [] Id: 4
3. Query_time: 13.278949 Lock_time: 0.000320 Rows_sent: 1 Rows_examined: 6920584
   SET timestamp=1419236366;
   SELECT COUNT 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 LIKE '%inox%') OR (LOWER LIKE '%inox%') OR (LOWER 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 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,