|
1
|
describe
|
|
2
|
SELECT
|
|
3
|
`issues`.`id` AS t0_r0,
|
|
4
|
`issues`.`tracker_id` AS t0_r1,
|
|
5
|
`issues`.`project_id` AS t0_r2,
|
|
6
|
`issues`.`subject` AS t0_r3,
|
|
7
|
`issues`.`description` AS t0_r4,
|
|
8
|
`issues`.`due_date` AS t0_r5,
|
|
9
|
`issues`.`category_id` AS t0_r6,
|
|
10
|
`issues`.`status_id` AS t0_r7,
|
|
11
|
`issues`.`assigned_to_id` AS t0_r8,
|
|
12
|
`issues`.`priority_id` AS t0_r9,
|
|
13
|
`issues`.`fixed_version_id` AS t0_r10,
|
|
14
|
`issues`.`author_id` AS t0_r11,
|
|
15
|
`issues`.`lock_version` AS t0_r12,
|
|
16
|
`issues`.`created_on` AS t0_r13,
|
|
17
|
`issues`.`updated_on` AS t0_r14,
|
|
18
|
`issues`.`start_date` AS t0_r15,
|
|
19
|
`issues`.`done_ratio` AS t0_r16,
|
|
20
|
`issues`.`estimated_hours` AS t0_r17,
|
|
21
|
`issues`.`parent_id` AS t0_r18,
|
|
22
|
`issues`.`root_id` AS t0_r19,
|
|
23
|
`issues`.`lft` AS t0_r20,
|
|
24
|
`issues`.`rgt` AS t0_r21,
|
|
25
|
`issues`.`is_private` AS t0_r22,
|
|
26
|
`issues`.`closed_on` AS t0_r23,
|
|
27
|
`projects`.`id` AS t1_r0,
|
|
28
|
`projects`.`name` AS t1_r1,
|
|
29
|
`projects`.`description` AS t1_r2,
|
|
30
|
`projects`.`homepage` AS t1_r3,
|
|
31
|
`projects`.`is_public` AS t1_r4,
|
|
32
|
`projects`.`parent_id` AS t1_r5,
|
|
33
|
`projects`.`created_on` AS t1_r6,
|
|
34
|
`projects`.`updated_on` AS t1_r7,
|
|
35
|
`projects`.`identifier` AS t1_r8,
|
|
36
|
`projects`.`status` AS t1_r9,
|
|
37
|
`projects`.`lft` AS t1_r10,
|
|
38
|
`projects`.`rgt` AS t1_r11,
|
|
39
|
`projects`.`inherit_members` AS t1_r12,
|
|
40
|
`projects`.`hipchat_auth_token` AS t1_r13,
|
|
41
|
`projects`.`hipchat_room_name` AS t1_r14,
|
|
42
|
`projects`.`hipchat_notify` AS t1_r15,
|
|
43
|
`issue_statuses`.`id` AS t2_r0,
|
|
44
|
`issue_statuses`.`name` AS t2_r1,
|
|
45
|
`issue_statuses`.`is_closed` AS t2_r2,
|
|
46
|
`issue_statuses`.`is_default` AS t2_r3,
|
|
47
|
`issue_statuses`.`position` AS t2_r4,
|
|
48
|
`issue_statuses`.`default_done_ratio` AS t2_r5,
|
|
49
|
`users`.`id` AS t3_r0,
|
|
50
|
`users`.`login` AS t3_r1,
|
|
51
|
`users`.`hashed_password` AS t3_r2,
|
|
52
|
`users`.`firstname` AS t3_r3,
|
|
53
|
`users`.`lastname` AS t3_r4,
|
|
54
|
`users`.`mail` AS t3_r5,
|
|
55
|
`users`.`admin` AS t3_r6,
|
|
56
|
`users`.`status` AS t3_r7,
|
|
57
|
`users`.`last_login_on` AS t3_r8,
|
|
58
|
`users`.`language` AS t3_r9,
|
|
59
|
`users`.`auth_source_id` AS t3_r10,
|
|
60
|
`users`.`created_on` AS t3_r11,
|
|
61
|
`users`.`updated_on` AS t3_r12,
|
|
62
|
`users`.`type` AS t3_r13,
|
|
63
|
`users`.`identity_url` AS t3_r14,
|
|
64
|
`users`.`mail_notification` AS t3_r15,
|
|
65
|
`users`.`salt` AS t3_r16,
|
|
66
|
`users`.`must_change_passwd` AS t3_r17,
|
|
67
|
`users`.`phone` AS t3_r18,
|
|
68
|
`users`.`address` AS t3_r19,
|
|
69
|
`users`.`skype` AS t3_r20,
|
|
70
|
`users`.`birthday` AS t3_r21,
|
|
71
|
`users`.`job_title` AS t3_r22,
|
|
72
|
`users`.`company` AS t3_r23,
|
|
73
|
`users`.`middlename` AS t3_r24,
|
|
74
|
`users`.`gender` AS t3_r25,
|
|
75
|
`users`.`twitter` AS t3_r26,
|
|
76
|
`users`.`facebook` AS t3_r27,
|
|
77
|
`users`.`linkedin` AS t3_r28,
|
|
78
|
`users`.`background` AS t3_r29,
|
|
79
|
`users`.`appearance_date` AS t3_r30,
|
|
80
|
`users`.`department_id` AS t3_r31,
|
|
81
|
`users`.`reminder_notification` AS t3_r32,
|
|
82
|
`trackers`.`id` AS t4_r0,
|
|
83
|
`trackers`.`name` AS t4_r1,
|
|
84
|
`trackers`.`is_in_chlog` AS t4_r2,
|
|
85
|
`trackers`.`position` AS t4_r3,
|
|
86
|
`trackers`.`is_in_roadmap` AS t4_r4,
|
|
87
|
`trackers`.`fields_bits` AS t4_r5,
|
|
88
|
`enumerations`.`id` AS t5_r0,
|
|
89
|
`enumerations`.`name` AS t5_r1,
|
|
90
|
`enumerations`.`position` AS t5_r2,
|
|
91
|
`enumerations`.`is_default` AS t5_r3,
|
|
92
|
`enumerations`.`type` AS t5_r4,
|
|
93
|
`enumerations`.`active` AS t5_r5,
|
|
94
|
`enumerations`.`project_id` AS t5_r6,
|
|
95
|
`enumerations`.`parent_id` AS t5_r7,
|
|
96
|
`enumerations`.`position_name` AS t5_r8,
|
|
97
|
`issue_categories`.`id` AS t6_r0,
|
|
98
|
`issue_categories`.`project_id` AS t6_r1,
|
|
99
|
`issue_categories`.`name` AS t6_r2,
|
|
100
|
`issue_categories`.`assigned_to_id` AS t6_r3,
|
|
101
|
`versions`.`id` AS t7_r0,
|
|
102
|
`versions`.`project_id` AS t7_r1,
|
|
103
|
`versions`.`name` AS t7_r2,
|
|
104
|
`versions`.`description` AS t7_r3,
|
|
105
|
`versions`.`effective_date` AS t7_r4,
|
|
106
|
`versions`.`created_on` AS t7_r5,
|
|
107
|
`versions`.`updated_on` AS t7_r6,
|
|
108
|
`versions`.`wiki_page_title` AS t7_r7,
|
|
109
|
`versions`.`status` AS t7_r8,
|
|
110
|
`versions`.`sharing` AS t7_r9,
|
|
111
|
`issue_reads`.`id` AS t8_r0,
|
|
112
|
`issue_reads`.`user_id` AS t8_r1,
|
|
113
|
`issue_reads`.`issue_id` AS t8_r2,
|
|
114
|
`issue_reads`.`read_date` AS t8_r3,
|
|
115
|
`issue_reads`.`created_at` AS t8_r4,
|
|
116
|
`issue_reads`.`updated_at` AS t8_r5 FROM `issues`
|
|
117
|
INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id`
|
|
118
|
INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id`
|
|
119
|
LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id`
|
|
120
|
LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id`
|
|
121
|
LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority')
|
|
122
|
LEFT OUTER JOIN `issue_categories` ON `issue_categories`.`id` = `issues`.`category_id`
|
|
123
|
LEFT OUTER JOIN `versions` ON `versions`.`id` = `issues`.`fixed_version_id`
|
|
124
|
LEFT OUTER JOIN `issue_reads` ON `issue_reads`.`issue_id` = `issues`.`id` AND issue_reads.user_id = issues.assigned_to_id
|
|
125
|
LEFT JOIN issue_reads uis_ir ON uis_ir.issue_id = issues.id and uis_ir.user_id = issues.assigned_to_id
|
|
126
|
WHERE
|
|
127
|
(projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND
|
|
128
|
((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)))
|
|
129
|
ORDER BY issues.id DESC
|
|
130
|
LIMIT 50
|
|
131
|
OFFSET 0;
|
|
132
|
|
|
133
|
|
|
134
|
## BEFORE INDEXES
|
|
135
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+
|
|
136
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
|
137
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+
|
|
138
|
| 1 | PRIMARY | issues | ALL | issues_project_id,index_issues_on_status_id | NULL | NULL | NULL | 56755 | Using where; Using temporary; Using filesort |
|
|
139
|
| 1 | PRIMARY | issue_statuses | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.status_id | 1 | |
|
|
140
|
| 1 | PRIMARY | users | eq_ref | PRIMARY,index_users_on_id_and_type | PRIMARY | 4 | redmineit.issues.assigned_to_id | 1 | |
|
|
141
|
| 1 | PRIMARY | trackers | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.tracker_id | 1 | |
|
|
142
|
| 1 | PRIMARY | enumerations | eq_ref | PRIMARY,index_enumerations_on_id_and_type | PRIMARY | 4 | redmineit.issues.priority_id | 1 | |
|
|
143
|
| 1 | PRIMARY | projects | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.project_id | 1 | Using where |
|
|
144
|
| 1 | PRIMARY | issue_categories | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.category_id | 1 | |
|
|
145
|
| 1 | PRIMARY | versions | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.fixed_version_id | 1 | |
|
|
146
|
| 1 | PRIMARY | issue_reads | ALL | NULL | NULL | NULL | NULL | 57280 | |
|
|
147
|
| 1 | PRIMARY | uis_ir | ALL | NULL | NULL | NULL | NULL | 57280 | |
|
|
148
|
| 3 | DEPENDENT SUBQUERY | issue_statuses | unique_subquery | PRIMARY,index_issue_statuses_on_is_closed | PRIMARY | 4 | func | 1 | Using where |
|
|
149
|
| 2 | DEPENDENT SUBQUERY | em | index_subquery | enabled_modules_project_id | enabled_modules_project_id | 5 | func | 3 | Using where |
|
|
150
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------+-------+----------------------------------------------+
|
|
151
|
12 rows in set (0.01 sec)
|
|
152
|
|
|
153
|
## AFTER INDEXES
|
|
154
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+
|
|
155
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
|
156
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+
|
|
157
|
| 1 | PRIMARY | projects | ALL | PRIMARY | NULL | NULL | NULL | 33 | Using where; Using temporary; Using filesort |
|
|
158
|
| 1 | PRIMARY | issues | ref | issues_project_id,index_issues_on_status_id | issues_project_id | 4 | redmineit.projects.id | 596 | Using where |
|
|
159
|
| 1 | PRIMARY | users | eq_ref | PRIMARY,index_users_on_id_and_type | PRIMARY | 4 | redmineit.issues.assigned_to_id | 1 | |
|
|
160
|
| 1 | PRIMARY | trackers | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.tracker_id | 1 | |
|
|
161
|
| 1 | PRIMARY | enumerations | eq_ref | PRIMARY,index_enumerations_on_id_and_type | PRIMARY | 4 | redmineit.issues.priority_id | 1 | |
|
|
162
|
| 1 | PRIMARY | issue_statuses | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.status_id | 1 | |
|
|
163
|
| 1 | PRIMARY | issue_categories | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.category_id | 1 | |
|
|
164
|
| 1 | PRIMARY | versions | eq_ref | PRIMARY | PRIMARY | 4 | redmineit.issues.fixed_version_id | 1 | |
|
|
165
|
| 1 | PRIMARY | issue_reads | ref | ir_issues_users | ir_issues_users | 10 | redmineit.issues.id,redmineit.issues.assigned_to_id | 1 | |
|
|
166
|
| 1 | PRIMARY | uis_ir | ref | ir_issues_users | ir_issues_users | 10 | redmineit.issues.id,redmineit.issues.assigned_to_id | 1 | Using index |
|
|
167
|
| 3 | DEPENDENT SUBQUERY | issue_statuses | unique_subquery | PRIMARY,index_issue_statuses_on_is_closed | PRIMARY | 4 | func | 1 | Using where |
|
|
168
|
| 2 | DEPENDENT SUBQUERY | em | index_subquery | enabled_modules_project_id,em_name | enabled_modules_project_id | 5 | func | 3 | Using where |
|
|
169
|
+----+--------------------+------------------+-----------------+---------------------------------------------+----------------------------+---------+-----------------------------------------------------+------+----------------------------------------------+
|
|
170
|
12 rows in set (0.00 sec)
|