0016922: View Issues list is very slow if a filter is used - MantisBT

ID	Project	Category	View Status	Date Submitted	Last Update

0016922	mantisbt	performance	public	2014-02-05 04:32	2014-02-18 06:23

Reporter	Apollo	Assigned To	dregad
Priority	normal	Severity	minor	Reproducibility	always
Status	closed	Resolution	no change required
Product Version	1.2.14

Summary	0016922: View Issues list is very slow if a filter is used
Description	If a user has a filter, the "View Issues" List is very slow and takes about minutes to load. We have about 30.000 Tickets in our System and 200.000 history entries. Due to calculating the sum of the list entries in filter_api.php -> filter_get_bug_count the system gets slow. In this query a index on mantis_bug_text_table can't be used, so all lines are read from mysql.
Additional Information	Solution: We have moved the WHERE ... OR mantis_bug_table.bug_text_id = mantis_bug_text_table.id to a LEFT JOIN LEFT JOIN mantis_bug_text_table ON mantis_bug_table.bug_text_id = mantis_bug_text_table.id in that query to fix the issue. These lines changed (file: filter_api.php, line 1998 - 1999) to: $t_join_clauses[] = " LEFT JOIN $t_bug_text_table ON $t_bug_table.bug_text_id = $t_bug_text_table.id";
Tags	No tags attached.

dregad 2014-02-05 05:42 developer ~0039318	Could you please submit a github pull request with your fix, or at least a unified diff to make it easier for us to review it ?

Apollo 2014-02-05 05:48 reporter ~0039319 Last edited: 2014-02-05 05:51	+++ filter_api.php @@ -1995,8 +1995,10 @@ `# add text query elements to arrays if ( !$t_first ) {` $t_from_clauses[] = "$t_bug_text_table"; $t_where_clauses[] = "$t_bug_table.bug_text_id = $t_bug_text_table.id"; $t_join_clauses[] = " LEFT JOIN $t_bug_text_table ON $t_bug_table.bug_text_id = $t_bug_text_table.id"; `$t_where_clauses[] = $t_textsearch_where_clause; $t_join_clauses[] = " LEFT JOIN $t_bugnote_table ON $t_bug_table.id = $t_bugnote_table.bug_id"; $t_join_clauses[] = " LEFT JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id";`

dregad 2014-02-05 10:54 developer ~0039324	This issue was actually fixed in 1.2.15 (see attached changeset). Please upgrade to latest version.

MantisBT: master 44055f88 2013-03-18 18:36 dregad Details Diff	Fix filter api issue with 'any condition' and text search A filter combining some criteria and a text search with 'any condition' results in a cartesian product, which has the potential to bring down the site as the RDBMS eats up all available resources. The root cause of this behavior is joining the bug_text table with a from clause and setting the join's criteria in the query's where clause, without taking consideration the operator's precedence (AND/OR). This commit resolves the problem by using a JOIN clause instead, which makes the query cleaner. Fixes 0015573		Affected Issues 0015573, 0016922
	mod - core/filter_api.php		Diff File