View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0015678||mantisbt||filters||public||2013-03-23 13:26||2014-12-08 00:34|
|Target Version||1.3.0-beta.1||Fixed in Version||1.3.0-beta.1|
|Summary||0015678: Bad performance when filtering and using match type "Any Condition"|
Filtering is very slow and blocks the issue tracker for other requests.
Statements causing the issue when using "Monitored by" = "myself" (user_id = 11111) in combination with "Hide Status" = "closed"
12 22.5539 filter_api.php:1009 filter_get_bug_count()
|Tags||No tags attached.|
I had a look at this issue. On my test database, the 2nd query runs in about 16-18 seconds on average, and its explain plan is as follows (non-relevant columns removed):
So the reason for the bad performance is (of course) that the SQL generated by this filter causes a full table scan to occur on the mantis_bug_monitor_table. Based on my cardinalities, we're looking at more than 82 million rows.
Analysis shows that the MySQL optimizer was not able to determine an appropriate key to use for joining mantis_bug_monitor_table and mantis_bug_table (possible_keys == NULL), and fell back on using the only available index for the joined table (key == PRIMARY) to match against the reference key, which is in fact a bug id (ref == mantis_bug_table.id).
The root cause for this, is that mantis_bug_monitor_table's PRIMARY (and only) index is [user_id, bug_id]. To achieve good performance here, we'd need to be able browse the table by bug_id too.
To prove my point, I added a new index as follows:
ALTER TABLE mantis_bug_monitor_table ADD INDEX bug_id ( bug_id );
With that, the explain plan becomes
The full table scan goes away, we check only a couple of rows instead of 7K+ and the query runs in 0.03 seconds.
So the good news is: fix is quite simple really (although some proper regression testing for adding this new index should be done).
MantisBT: master 8ff83382
2013-04-05 14:47:22Details Diff
|Fix 0015678: Add new index to mantis_bug_monitor_table
The new index is on bug_id column, and resolves a performance issue when
filtering on users monitoring issues with any other search criteria when
using match type "Any Condition".
|mod - admin/schema.php||Diff File|
|2013-03-23 13:26||atrol||New Issue|
|2013-03-23 13:29||atrol||Relationship added||related to 0015573|
|2013-03-23 13:30||atrol||Relationship added||related to 0006809|
|2013-03-30 19:09||dregad||Note Added: 0035999|
|2013-03-30 19:09||dregad||Status||new => confirmed|
|2013-03-30 19:10||dregad||Product Version||git trunk => 1.2.15|
|2013-03-30 19:10||dregad||Target Version||1.2.15 => 1.3.0-beta.1|
|2013-03-30 19:10||dregad||Note Edited: 0035999||View Revisions|
|2013-03-30 19:12||dregad||Note Edited: 0035999||View Revisions|
|2013-04-06 08:29||dregad||Changeset attached||=> MantisBT master 8ff83382|
|2013-04-06 08:29||dregad||Assigned To||=> dregad|
|2013-04-06 08:29||dregad||Status||confirmed => resolved|
|2013-04-06 08:29||dregad||Resolution||open => fixed|
|2013-04-06 08:29||dregad||Fixed in Version||=> 1.3.0-beta.1|
|2014-12-08 00:34||vboctor||Status||resolved => closed|