Redmine

Hello All,

I encounter performance issue since 5.0.4 release. Could you help me ?

My env :

Environment:
  Redmine version                5.0.4.stable
  Ruby version                   3.0.2-p107 (2021-07-07) [x86_64-linux-gnu]
  Rails version                  6.1.7
  Environment                    production
  Database adapter               Mysql2
  Mailer queue                   ActiveJob::QueueAdapters::AsyncAdapter
  Mailer delivery                smtp
Redmine settings:
  Redmine theme                  Alternate
SCM:
  Subversion                     1.14.1
  Git                            2.34.1
  Filesystem                     
Redmine plugins:
  no plugin installed

Issue

List some issues is really slower than with redmine 3 branch

I've notice a slow request using mysql command.

Slow request :

 SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `projects`.`default_assigned_to_id` AS t2_r14, `projects`.`default_issue_query_id` AS t2_r15 
 FROM `issues` 
 INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` 
 LEFT OUTER JOIN users ON users.id = issues.assigned_to_id 
 WHERE (projects.status <> 9 
 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) 
 AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=FALSE)) 
 AND (issues.tracker_id IN ('8','44','43','40','39')) 
 AND (issues.project_id IN ('47','51','52','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','72','73','74','75','76','78','79','80','81','82','84','85','86','87','88','93','94','95','96','97','98','99','100','101','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','121','122','123','126','127','128','129','131','132','135','136','137','138','139','140','141','142','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','175','176','177','179','180','181','184','185','186'))) 

 ORDER BY users.lastname ASC, users.firstname ASC, users.id ASC, issues.id DESC;

It take 3.35 sec for 53 rows

If I remove the criteria : AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')

It doesn't change the result, but i get the answer in 0.11 sec.

How could we improve that ?

Thansk in advance,

Cédric