Defect #19855

Slow time to run querys

Added by Bruno Fonseca over 7 years ago. Updated over 7 years ago.

Status:Needs feedbackStart date:
Priority:HighDue date:
Assignee:-% Done:

0%

Category:Performance
Target version:-
Resolution: Affected version:

Description

Hi,

I'm actualy using redmine version 2.5 in production.

Before analizyng many logs of my database and observer the slow performance of Redmine in production, capture the query :

SELECT `changesets`.`id` AS t0_r0, `changesets`.`repository_id` AS t0_r1, `changesets`.`revision` AS t0_r2, `changesets`.`committer` AS t0_r3, `changese ts`.`committed_on` AS t0_r4, `changesets`.`comments` AS t0_r5, `changesets`.`commit_date` AS t0_r6, `changesets`.`scmid` AS t0_r7, `changesets`.`user_id ` AS t0_r8, `repositories`.`id` AS t1_r0, `repositories`.`project_id` AS t1_r1, `repositories`.`url` AS t1_r2, `repositories`.`login` AS t1_r3, `reposit ories`.`password` AS t1_r4, `repositories`.`root_url` AS t1_r5, `repositories`.`type` AS t1_r6, `repositories`.`path_encoding` AS t1_r7, `repositories`. `log_encoding` AS t1_r8, `repositories`.`extra_info` AS t1_r9, `repositories`.`identifier` AS t1_r10, `repositories`.`is_default` AS t1_r11, `repositori es`.`created_on` AS t1_r12, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `pr ojects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifie r` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12 FROM `changese ts` LEFT OUTER JOIN `repositories` ON `repositories`.`id` = `changesets`.`repository_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `repositories`. `project_id` INNER JOIN `changesets_issues` ON `changesets`.`id` = `changesets_issues`.`changeset_id` WHERE `changesets_issues`.`issue_id` = 61608 AND ( ((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='repository')) AND (projects.id IN (1,3,7,10,11,23 ,24,25,26,27,28,33,34,35,36,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,56,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84,98) OR projects. id IN (1,2,3,7,8,10,11,12,14,17,18,19,22,23,24,26,27,28,33,34,35,36,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,56,57,58,59,60,62,64,65,66,67,68,69, 71,78,79,80,81,82,83,84,97,98,100,105,106,108,110,112,113,116,118,121,122,123,125,126,127,128,129,132,133,134,135,136,139,143,144,145,147,148,149,150,15 2,153,154,155,156,157,158,159,160,161,163,164,165,167,168) OR projects.id IN (23,45,52,53,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84,97,10 0,105,106,110,112,113,116,118,121,127,128,135) OR projects.id IN (23,45,52,53,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84) OR projects.id I N (23,45,52,53,57,58,59,60,62,68,78) OR projects.id IN (64,65,66,67,69,71,79,80,81,82,83,84) OR projects.id IN (112) OR projects.id IN (151)))) ORDER BY changesets.committed_on ASC, changesets.id ASC;

The section of code with:

AND ( ((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='repository')) AND (projects.id IN (1,3,7,10,11,23 ,24,25,26,27,28,33,34,35,36,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,56,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84,98) OR projects. id IN (1,2,3,7,8,10,11,12,14,17,18,19,22,23,24,26,27,28,33,34,35,36,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,56,57,58,59,60,62,64,65,66,67,68,69, 71,78,79,80,81,82,83,84,97,98,100,105,106,108,110,112,113,116,118,121,122,123,125,126,127,128,129,132,133,134,135,136,139,143,144,145,147,148,149,150,15 2,153,154,155,156,157,158,159,160,161,163,164,165,167,168) OR projects.id IN (23,45,52,53,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84,97,10 0,105,106,110,112,113,116,118,121,127,128,135) OR projects.id IN (23,45,52,53,57,58,59,60,62,64,65,66,67,68,69,71,78,79,80,81,82,83,84) OR projects.id I N (23,45,52,53,57,58,59,60,62,68,78) OR projects.id IN (64,65,66,67,69,71,79,80,81,82,83,84) OR projects.id IN (112) OR projects.id IN (151))))

is the actualy slow performance of redmine.

How can I improve this query by removing this block, knowing that it allocated to the project model in line 86 to 87?

Thank

History

#1 Updated by Toshi MARUYAMA over 7 years ago

  • Status changed from New to Needs feedback
  • Affected version deleted (2.6.0)

Also available in: Atom PDF