View Issue Details

IDProjectCategoryView StatusLast Update
0021038mantisbtdb mysqlpublic2016-07-04 14:58
Reporterbezl Assigned Toatrol  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionduplicate 
Product Version1.2.15 
Summary0021038: Improvement in the database - "My View" page is very slow.
Description

Hi everyone,

We have 102000 issues and some pages now load very slow - around 8-10 seconds!

I have added logging parameters to config.ini.php like it was adviced in issue 0020900 ($g_show_queries_count = $g_show_queries_list = ON;)
and here're results for "My View" page:

141 total queries executed.
135 unique queries executed.
1 0.0004 database_api.php:105 db_connect()
SET NAMES UTF8
2 0.0002 plugin_api.php:806 plugin_register_installed()
SELECT basename, priority, protected FROM mantis_plugin_table WHERE enabled=1 ORDER BY priority DESC
3 0.0002 config_api.php:124 config_get()
SELECT config_id, user_id, project_id, type, value, access_reqd FROM mantis_config_table
4 0.0005 authentication_api.php:779 auth_is_cookie_valid()
SELECT FROM mantis_user_table WHERE cookie_string='7babfe3744520ebbfb84e05076c0d07a14ab28014e299cbd5feb875f8571a065'
5 0.0002 user_pref_api.php:171 user_pref_cache_row()
SELECT
FROM mantis_user_pref_table WHERE user_id=4 AND project_id=0
6 0.0004 tokens_api.php:263 token_purge_expired()
DELETE FROM mantis_tokens_table WHERE 1464883259 > expiry
7 0.0005 tokens_api.php:78 token_get()
SELECT FROM mantis_tokens_table WHERE type=5 AND owner=4
8 0.0001 tokens_api.php:78 token_get()
SELECT
FROM mantis_tokens_table WHERE type=5 AND owner=4
9 0.0004 tokens_api.php:43 token_exists()
SELECT id FROM mantis_tokens_table WHERE id=17
10 0.0002 tokens_api.php:224 token_update()
UPDATE mantis_tokens_table SET value='a:5:{s:7:"profile";b:0;s:13:"relationships";b:1;s:6:"filter";b:1;s:12:"bugnotestats";b:1;s:10:"monitoring";b:1;}', expiry=1496419259 WHERE id=17
11 0.0001 category_api.php:477 category_get_all_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE project_id=0 ORDER BY c.name
12 0.0002 project_hierarchy_api.php:187 project_hierarchy_cache()
SELECT DISTINCT p.id, ph.parent_id, p.name, p.inherit_global, ph.inherit_parent FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 ORDER BY p.name
13 0.0002 project_api.php:123 project_cache_array_rows()
SELECT
FROM mantis_project_table WHERE id IN (31,170,147,86,148,95,40,146,94,124,34,157,2,158,141,162,156,28,1,89,142,82,47,48,143,152,83,155,135,130,32,103,7,33,119)
14 0.0002 user_api.php:946 user_get_accessible_subprojects()
SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
15 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=145
16 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=138
17 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=154
18 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=144
19 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=77
20 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=93
21 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=149
22 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=159
23 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=166
24 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=164
25 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=115
26 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=175
27 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=113
28 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=150
29 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=167
30 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=79
31 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=78
32 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=90
33 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=153
34 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=172
35 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=151
36 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=81
37 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=171
38 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=168
39 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=96
40 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=118
41 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=80
42 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=129
43 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=160
44 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=112
45 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=92
46 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=137
47 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=161
48 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=165
49 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=88
50 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=49
51 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=52
52 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=85
53 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=58
54 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=169
55 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=101
56 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=114
57 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=50
58 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=59
59 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=55
60 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=117
61 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=84
62 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=75
63 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=56
64 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=51
65 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=102
66 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=53
67 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=54
68 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=98
69 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=76
70 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=87
71 0.0002 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=63
72 0.0002 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=100
73 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=60
74 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=116
75 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=61
76 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=62
77 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=57
78 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=139
79 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=140
80 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=131
81 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=132
82 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=133
83 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=134
84 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=104
85 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=111
86 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=136
87 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=106
88 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=110
89 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=108
90 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=174
91 0.0003 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=120
92 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=163
93 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=97
94 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=173
95 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=122
96 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=99
97 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=176
98 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=177
99 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=179
100 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=178
101 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=180
102 0.0001 project_api.php:83 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=123
103 0.0001 project_api.php:83 project_cache_row()
SELECT FROM mantis_project_table WHERE id=121
104 0.0004 tokens_api.php:78 token_get()
SELECT
FROM mantis_tokens_table WHERE type=3 AND owner=4
105 0.0005 bug_api.php:644 bug_cache_array_rows()
SELECT FROM mantis_bug_table WHERE id IN (100491,92034,50327,102745,99854)
106 0.0306 user_api.php:1041 user_get_assigned_open_bug_count()
SELECT COUNT(
) FROM mantis_bug_table WHERE project_id IN (31,170,147,86,148,95,40,146,94,124,34,157,2,158,141,162,156,28,1,89,142,82,47,48,143,152,83,155,135,130,32,103,7,33,119,145,138,154,144,77,93,164,115,113,150,167,79,78,90,81,168,96,118,80,129,149,159,166,175,153,172,151,171,160,112,92,137,161,165,88,49,52,85,58,169,101,114,50,59,55,117,84,75,56,51,102,53,54,98,76,87,63,100,60,116,61,62,57,139,140,131,132,133,134,104,111,136,106,110,108,174,120,163,97,173,122,99,123,121,176,177,179,178,180) AND status<'80' AND handler_id=4
107 0.0005 filter_api.php:4564 filter_db_get_filter()
SELECT FROM mantis_filters_table WHERE id=6
108 0.0050 filter_api.php:4610 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
109 0.0002 custom_field_api.php:779 custom_field_get_ids()
SELECT FROM mantis_custom_field_table ORDER BY name ASC
110 0.0005 custom_field_api.php:749 custom_field_get_linked_ids()
SELECT DISTINCT cft.id FROM mantis_custom_field_table cft JOIN mantis_custom_field_project_table cfpt ON cfpt.field_id = cft.id JOIN mantis_project_table pt ON pt.id = cfpt.project_id AND pt.enabled = 1 LEFT JOIN mantis_project_user_list_table pult ON pult.project_id = cfpt.project_id AND pult.user_id = 4 , mantis_user_table ut WHERE ut.id = 4 AND ( pt.view_state = 10 OR pult.user_id = ut.id OR ( pult.user_id IS NULL AND ut.access_level >=90 ) )
111 0.0298 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) )
112 0.0296 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
113 0.0006 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (101297, 89458, 100823, 92034, 90550, 89117, 96190, 90509, 88407, 88888) GROUP BY bug_id
114 0.0001 category_api.php:523 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (899,2,3,952,21,549)
115 0.0082 file_api.php:84 file_bug_attachment_count()
SELECT bug_id, COUNT(bug_id) AS attachments FROM mantis_bug_file_table GROUP BY bug_id
116 0.0006 filter_api.php:4610 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
117 0.1820 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=0 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) )
118 0.3498 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=0 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
119 0.0009 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (79965, 102663, 102923, 101966, 102867, 102365, 96116, 102300, 102324, 102891) GROUP BY bug_id
120 0.0004 category_api.php:523 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (422,795,682,1201)
121 0.0003 filter_api.php:4610 filter_db_get_project_current()
SELECT FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
122 0.1577 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.reporter_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) )
123 0.2662 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.reporter_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
124 0.0016 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (99854, 102790, 90048, 102622, 101010, 102750, 73845, 100736, 102136, 12867) GROUP BY bug_id
125 0.0001 category_api.php:523 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (887,911,58)
126 0.0003 filter_api.php:4610 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
127 0.5619 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status=80 ) )
128 1.2858 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status=80 ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
129 0.0007 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (101075, 102151, 102928, 102925, 100491, 99975, 102608, 102621, 102922, 102549) GROUP BY bug_id
130 0.0001 category_api.php:523 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (1199,96)
131 0.0002 filter_api.php:4610 filter_db_get_project_current()
SELECT FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
132 0.5398 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1
133 2.1551 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
134 0.0011 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (79965, 102663, 101075, 102929, 102666, 102151, 96152, 102921, 102928, 102492) GROUP BY bug_id
135 0.0004 category_api.php:523 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (1073,984,276)
136 0.0003 filter_api.php:4610 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
137 0.0054 filter_api.php:1009 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_bug_monitor_table user_monitor ON user_monitor.bug_id = mantis_bug_table.id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) AND ( user_monitor.user_id=4 ) )
138 0.0064 filter_api.php:2052 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_bug_monitor_table user_monitor ON user_monitor.bug_id = mantis_bug_table.id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) AND ( user_monitor.user_id=4 ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
139 0.0015 filter_api.php:2079 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (34803, 50327, 102231, 78489, 101495, 76336, 18880, 100086, 46821, 35724) GROUP BY bug_id
140 0.0001 category_api.php:523 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (1043,542,990)
141 0.0006 user_api.php:1146 user_update_last_visit()
UPDATE mantis_user_table SET last_visit= 1464883265 WHERE id=4
5.6398 SQL Queries Total Time
6.1405 Page Request Total Time

TagsNo tags attached.

Relationships

duplicate of 0021044 closedcproensa my view page, $t_hide_status_default consitency 
related to 0020900 closedatrol Improvement in the database 

Activities

atrol

atrol

2016-06-03 01:42

developer   ~0053254

bezl,

you should ppgrade to 1.2.19 as there have also been performance improvements in it, e.g. 0009876

Also search the web for "mysql performance tunning".
Maybe even simple steps (e.g. optimize table operations) could improve performance.

After those two steps it would be fine if you post your new results.

The additional information listed below may also be useful:

  • Exact version of PHP, Database, Web server, Browser and Operating System
  • Relevant customizations (e.g. changes in config_inc.php, etc)
  • Installed plugins or custom functions ?
  • Was the MantisBT source code modified in any way ?
bezl

bezl

2016-06-03 02:03

reporter   ~0053255

Thank you, Atrol!
We will proceed with the upgrade to 1.2.19 and then I'll get back to you with all the new information.
Due date: 10 jun 2016

atrol

atrol

2016-06-14 02:23

developer   ~0053371

bezl,

You did not provide any feedback; I am therefore resolving this issue as "no change required".

Feel free to reopen the issue at a later time and provide the requested information.

bezl

bezl

2016-06-14 04:01

reporter   ~0053375

Thank you, Atrol.
We haven't yet updated to 1.2.19 - I'm still waiting for it from our admins...

bezl

bezl

2016-06-20 05:45

reporter   ~0053426

We have updated to 1.2.19

And here's the log on the 1.2.19 version - this is for "My View" page:

141 total queries executed.
135 unique queries executed.

6.3762 SQL Queries Total Time
6.9506 Page Request Total Time

=========================
141 total queries executed.
135 unique queries executed.
1 0.0002 database_api.php:105 db_connect()
SET NAMES UTF8
2 0.0001 plugin_api.php:808 plugin_register_installed()
SELECT basename, priority, protected FROM mantis_plugin_table WHERE enabled=1 ORDER BY priority DESC
3 0.0002 config_api.php:124 config_get()
SELECT config_id, user_id, project_id, type, value, access_reqd FROM mantis_config_table
4 0.0005 authentication_api.php:779 auth_is_cookie_valid()
SELECT FROM mantis_user_table WHERE cookie_string='7babfe3744520ebbfb84e05076c0d07a14ab28014e299cbd5feb875f8571a065'
5 0.0003 user_pref_api.php:171 user_pref_cache_row()
SELECT
FROM mantis_user_pref_table WHERE user_id=4 AND project_id=0
6 0.0008 tokens_api.php:263 token_purge_expired()
DELETE FROM mantis_tokens_table WHERE 1466415807 > expiry
7 0.0006 tokens_api.php:78 token_get()
SELECT FROM mantis_tokens_table WHERE type=5 AND owner=4
8 0.0004 tokens_api.php:78 token_get()
SELECT
FROM mantis_tokens_table WHERE type=5 AND owner=4
9 0.0005 tokens_api.php:43 token_exists()
SELECT id FROM mantis_tokens_table WHERE id=17
10 0.0005 tokens_api.php:224 token_update()
UPDATE mantis_tokens_table SET value='a:5:{s:7:"profile";b:0;s:13:"relationships";b:1;s:6:"filter";b:1;s:12:"bugnotestats";b:1;s:10:"monitoring";b:1;}', expiry=1497951807 WHERE id=17
11 0.0002 category_api.php:466 category_get_all_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE project_id=0 ORDER BY c.name
12 0.0003 project_hierarchy_api.php:187 project_hierarchy_cache()
SELECT DISTINCT p.id, ph.parent_id, p.name, p.inherit_global, ph.inherit_parent FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 ORDER BY p.name
13 0.0002 project_api.php:144 project_cache_array_rows()
SELECT
FROM mantis_project_table WHERE id IN (31,170,147,86,148,95,40,146,94,124,34,157,2,158,141,162,156,28,1,89,142,82,47,48,143,152,83,155,135,130,32,103,7,33,119)
14 0.0003 user_api.php:946 user_get_accessible_subprojects()
SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
15 0.0004 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=145
16 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=138
17 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=154
18 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=144
19 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=77
20 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=93
21 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=149
22 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=159
23 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=166
24 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=164
25 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=115
26 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=175
27 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=113
28 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=150
29 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=167
30 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=79
31 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=78
32 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=90
33 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=153
34 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=172
35 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=151
36 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=81
37 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=171
38 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=168
39 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=96
40 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=118
41 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=80
42 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=129
43 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=160
44 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=112
45 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=92
46 0.0004 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=137
47 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=161
48 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=165
49 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=88
50 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=49
51 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=52
52 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=85
53 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=58
54 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=169
55 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=101
56 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=114
57 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=50
58 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=59
59 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=55
60 0.0007 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=117
61 0.0006 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=84
62 0.0005 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=75
63 0.0009 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=56
64 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=51
65 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=102
66 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=53
67 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=54
68 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=98
69 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=76
70 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=87
71 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=63
72 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=100
73 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=60
74 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=116
75 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=61
76 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=62
77 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=57
78 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=139
79 0.0005 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=140
80 0.0005 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=131
81 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=132
82 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=133
83 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=134
84 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=104
85 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=111
86 0.0004 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=136
87 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=106
88 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=110
89 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=108
90 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=174
91 0.0003 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=120
92 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=163
93 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=97
94 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=173
95 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=122
96 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=99
97 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=176
98 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=177
99 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=179
100 0.0003 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=178
101 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=180
102 0.0002 project_api.php:104 project_cache_row()
SELECT
FROM mantis_project_table WHERE id=123
103 0.0002 project_api.php:104 project_cache_row()
SELECT FROM mantis_project_table WHERE id=121
104 0.0006 tokens_api.php:78 token_get()
SELECT
FROM mantis_tokens_table WHERE type=3 AND owner=4
105 0.0009 bug_api.php:644 bug_cache_array_rows()
SELECT FROM mantis_bug_table WHERE id IN (69998,80224,105210,105209,106223)
106 0.0444 user_api.php:1053 user_get_assigned_open_bug_count()
SELECT COUNT(
) FROM mantis_bug_table WHERE project_id IN (31,170,147,86,148,95,40,146,94,124,34,157,2,158,141,162,156,28,1,89,142,82,47,48,143,152,83,155,135,130,32,103,7,33,119,145,138,154,144,77,93,164,115,113,150,167,79,78,90,81,168,96,118,80,129,149,159,166,175,153,172,151,171,160,112,92,137,161,165,88,49,52,85,58,169,101,114,50,59,55,117,84,75,56,51,102,53,54,98,76,87,63,100,60,116,61,62,57,139,140,131,132,133,134,104,111,136,106,110,108,174,120,163,97,173,122,99,123,121,176,177,179,178,180) AND status<'80' AND handler_id=4
107 0.0004 filter_api.php:4582 filter_db_get_filter()
SELECT FROM mantis_filters_table WHERE id=6
108 0.0067 filter_api.php:4628 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
109 0.0002 custom_field_api.php:780 custom_field_get_ids()
SELECT FROM mantis_custom_field_table ORDER BY name ASC
110 0.0005 custom_field_api.php:750 custom_field_get_linked_ids()
SELECT DISTINCT cft.id FROM mantis_custom_field_table cft JOIN mantis_custom_field_project_table cfpt ON cfpt.field_id = cft.id JOIN mantis_project_table pt ON pt.id = cfpt.project_id AND pt.enabled = 1 LEFT JOIN mantis_project_user_list_table pult ON pult.project_id = cfpt.project_id AND pult.user_id = 4 , mantis_user_table ut WHERE ut.id = 4 AND ( pt.view_state = 10 OR pult.user_id = ut.id OR ( pult.user_id IS NULL AND ut.access_level >=90 ) )
111 0.0304 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) )
112 0.0302 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
113 0.0007 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (105210, 105209, 105674, 105672, 101295, 105108, 104776, 91786, 101297, 89458) GROUP BY bug_id
114 0.0001 category_api.php:512 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (911,899,3,2)
115 0.0476 file_api.php:84 file_bug_attachment_count()
SELECT bug_id, COUNT(bug_id) AS attachments FROM mantis_bug_file_table GROUP BY bug_id
116 0.0056 filter_api.php:4628 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
117 0.0542 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=0 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) )
118 0.0922 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.handler_id=0 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
119 0.0024 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (102713, 48390, 106579, 102497, 104732, 106501, 102559, 102349, 72528, 102527) GROUP BY bug_id
120 0.0001 category_api.php:512 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (795,994)
121 0.0003 filter_api.php:4628 filter_db_get_project_current()
SELECT FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
122 0.1591 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.reporter_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) )
123 0.3130 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.reporter_id=4 ) AND ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
124 0.0015 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (69998, 59592, 76521, 93549, 47021, 47020, 47019, 105210, 105209, 93270) GROUP BY bug_id
125 0.0001 category_api.php:512 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (951,985,19)
126 0.0003 filter_api.php:4628 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
127 1.4578 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status=80 ) )
128 1.2526 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status=80 ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
129 0.0009 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (106559, 69998, 106507, 106575, 77546, 85602, 59592, 92632, 106170, 106567) GROUP BY bug_id
130 0.0001 category_api.php:512 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (1174)
131 0.0003 filter_api.php:4628 filter_db_get_project_current()
SELECT FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
132 0.6259 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1
133 2.1959 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table.
FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
134 0.0008 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (106539, 106579, 106502, 106589, 105257, 106588, 102713, 106559, 106576, 69998) GROUP BY bug_id
135 0.0003 category_api.php:512 category_cache_array_rows()
SELECT c., p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (96,487,977)
136 0.0003 filter_api.php:4628 filter_db_get_project_current()
SELECT
FROM mantis_filters_table WHERE user_id=4 AND project_id=0 AND name=''
137 0.0142 filter_api.php:1027 filter_get_bug_count()
SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_bug_monitor_table user_monitor ON user_monitor.bug_id = mantis_bug_table.id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) AND ( user_monitor.user_id=4 ) )
138 0.0062 filter_api.php:2070 filter_get_bug_rows()
SELECT DISTINCT mantis_bug_table. FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_bug_monitor_table user_monitor ON user_monitor.bug_id = mantis_bug_table.id WHERE mantis_project_table.enabled = 1 AND ( ( mantis_bug_table.status in (10, 20, 40, 50, 60, 70, 80, 85) ) AND ( user_monitor.user_id=4 ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
139 0.0014 filter_api.php:2097 filter_cache_result()
SELECT DISTINCT bug_id,MAX(last_modified) as last_modified, COUNT(last_modified) as count FROM mantis_bugnote_table WHERE mantis_bugnote_table.bug_id in (77546, 48370, 46821, 83174, 100086, 30729, 50327, 34803, 102231, 78489) GROUP BY bug_id
140 0.0001 category_api.php:512 category_cache_array_rows()
SELECT c.
, p.name AS project_name FROM mantis_category_table AS c LEFT JOIN mantis_project_table AS p ON c.project_id=p.id WHERE c.id IN (542,532)
141 0.0004 user_api.php:1158 user_update_last_visit()
UPDATE mantis_user_table SET last_visit= 1466415814 WHERE id=4
6.3762 SQL Queries Total Time
6.9506 Page Request Total Time

atrol

atrol

2016-06-20 08:06

developer   ~0053427

As a temporary workaround you could add the following lines to file config_inc.php

$g_my_view_boxes = array (
'assigned' => '1',
'unassigned' => '2',
'reported' => '3',
'resolved' => '4',
'recent_mod' => '0',
'monitored' => '6',
'feedback' => '0',
'verify' => '0',
'my_comments' => '0'
);

After that, you will see no longer the "Recently Modified" box which is the first bottleneck.
See also related issue 0021044 for it.
Certainly no real workaround if you need this box for your daily work.

If you can spent some time you could also try the following steps:

  • Create a copy of your database

  • Run the following statement and let us know the results
    EXPLAIN SELECT DISTINCT mantis_bug_table . *
    FROM mantis_bug_table
    JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id
    WHERE mantis_project_table.enabled =1
    ORDER BY mantis_bug_table.last_updated DESC , mantis_bug_table.date_submitted DESC

  • Run the following statements
    ALTER TABLE mantis_bug_table ADD INDEX ( last_updated )
    ALTER TABLE mantis_bug_table ADD INDEX ( date_submitted )

  • Run again this statement and let us know the results
    EXPLAIN SELECT DISTINCT mantis_bug_table . *
    FROM mantis_bug_table
    JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id
    WHERE mantis_project_table.enabled =1
    ORDER BY mantis_bug_table.last_updated DESC , mantis_bug_table.date_submitted DESC

If the 2nd run is much faster than the 1st one you could create the indexes also in your production database.

bezl

bezl

2016-06-20 08:46

reporter   ~0053428

Thank you, I've removed recent_mod - it works fast enough now.
I will see if anyone will complain that this block has disappeared :)

atrol

atrol

2016-06-20 09:20

developer   ~0053429

Thanks for the feedback.
I am resolving this as a duplicate of 0021044 where you have been set to the monitoring list.

Don't expect a real solution in Mantis in short term (1.3.x and 2.0.x)

Independant from that, I am quite sure that some standard mysql performance tuning could enhance your situation at least a little bit.

Let's hope that no one misses the recent_mod block ;-)