Project

General

Profile

Actions

Feature #22594

open

Optimize retrieve_previous_and_next_issue_ids

Added by Jérôme BATAILLE about 8 years ago. Updated about 4 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
Code cleanup/refactoring
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:

Description

On our Redmine instance the retrieve_previous_and_next_issue_ids method is the one that slows the request rendering.

issue_ids = @query.issue_ids(:order => sort_clause, :limit => (limit + 1), :include => [:assigned_to, :tracker, :priority, :category, :fixed_version])

generates a bunch of issues SELECT that are pretty slow :

23.42.45.336 28073:   SQL (0.3ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1226 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.527 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1262 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.532 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1252 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.538 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1261 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.549 28073:   SQL (0.4ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1243 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.553 28073:   SQL (0.1ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1263 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.558 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1227 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.565 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1249 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.569 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1228 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.576 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1260 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.582 28073:   SQL (0.1ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1238 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
23.42.45.587 28073:   SQL (0.2ms)  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`.`is_default` AS t1_r3, `issue_statuses`.`position` AS t1_r4, `issue_statuses`.`default_done_ratio` AS t1_r5, `trackers`.`id` AS t2_r0, `trackers`.`name` AS t2_r1, `trackers`.`is_in_chlog` AS t2_r2, `trackers`.`position` AS t2_r3, `trackers`.`is_in_roadmap` AS t2_r4, `trackers`.`mandatory` AS t2_r5, `trackers`.`fields_bits` AS t2_r6, `projects`.`id` AS t3_r0, `projects`.`name` AS t3_r1, `projects`.`description` AS t3_r2, `projects`.`homepage` AS t3_r3, `projects`.`is_public` AS t3_r4, `projects`.`parent_id` AS t3_r5, `projects`.`created_on` AS t3_r6, `projects`.`updated_on` AS t3_r7, `projects`.`identifier` AS t3_r8, `projects`.`status` AS t3_r9, `projects`.`lft` AS t3_r10, `projects`.`rgt` AS t3_r11, `projects`.`inherit_members` AS t3_r12 FROM `issues` LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` WHERE `issues`.`project_id` = 1235 AND (((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) ))
Actions #1

Updated by Jérôme BATAILLE about 8 years ago

What is slow is the issue display.

The scope parameters :

WHERE  0 ((projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((projects.id IN (11,141,144,433,742,919,1214,1226,1227,1228,1229,1233,1234,1235,1238,1240,1241,1243,1246,1247,1248,1249,1250,1251,1252,1255,1256,1257,1259,1260,1261,1262,1263,1265,1266,1267) AND ((issues.is_private = 0 OR issues.author_id = 1281 OR issues.assigned_to_id IN (1281,1298,3895))))) )
              1 (issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND (issues.tracker_id IN ('2','3')) AND projects.id IN (1226,1262,1263,1252,1261,1235,1227,1249,1228,1243,1260,1238,1244,1258)
       INCLUDES [:project, :assigned_to, :priority, :category, :fixed_version]
       JOINS    [:status, :project]
       ORDER    ["issues.root_id", "issues.lft ASC"]

Actions #2

Updated by Jérôme BATAILLE about 8 years ago

I suppose that what is slow is the Issues and related models loading, but we don't need all the members because here we only need the issues ids.
I tried to replace the includes by joins with no success.

Has someone an idea ? preload ? select ?

Actions #3

Updated by Toshi MARUYAMA about 8 years ago

  • Category set to Code cleanup/refactoring
Actions

Also available in: Atom PDF