Defect #18696

MySQL Queries are Slow

Added by Prasad Wani over 6 years ago. Updated over 5 years ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution: Affected version:2.6.0

Description

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,

Screen Shot 2014-12-22 at 2.01.37 pm.png - Explain output for the above mention query. (115 KB) Prasad Wani, 2014-12-22 09:31


Related issues

Related to Redmine - Feature #24553: missing index on table custom_values Closed

History

#1 Updated by Dietmar H about 6 years ago

Was it ever considered / discussed to use the the Full-Text Search Functions of MySQL?
The performance advantage seems to be considerable, see this analysis.

#3 Updated by Toshi MARUYAMA over 5 years ago

  • Priority changed from Urgent to Normal

#4 Updated by Toshi MARUYAMA over 4 years ago

  • Related to Feature #24553: missing index on table custom_values added

Also available in: Atom PDF