MantisBT

View Issue Details Jump to Notes ] Wiki ] Related Changesets ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0015678mantisbtfilterspublic2013-03-23 13:262013-04-06 10:30
Reporteratrol 
Assigned Todregad 
PriorityhighSeveritymajorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product Version1.2.15 
Target Version1.3.xFixed in Version1.3.x 
Summary0015678: Bad performance when filtering and using match type "Any Condition"
DescriptionFiltering is very slow and blocks the issue tracker for other requests.
This happens when filtering by "Monitored by" and any other condition when using match type "Any Condition"
Additional InformationStatements 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()
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, 30, 40, 50, 80) ) OR ( user_monitor.user_id=11111 ) )
13 21.4255 filter_api.php:2051 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, 30, 40, 50, 80) ) OR ( user_monitor.user_id=11111 ) ) ORDER BY mantis_bug_table.sticky DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC
TagsNo tags attached.
Attached Files

- Relationships
related to 0006809closedrombert mantisbt Using an 'Or' filter logic 
related to 0015573closeddregad mantisbt CVE-2013-1883: One query can be issued via current Mantis interface to take down site 

-  Notes
User avatar (0035999)
dregad (developer)
2013-03-30 19:09
edited on: 2013-03-30 19:12

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):
+------+-------------+------+-----------------+-------+---------------------------+-----+
|select|table        |type  |possible_keys    |key    |ref                        |rows |
+------+-------------+------+-----------------+-------+---------------------------+-----+
|SIMPLE|bug_table    |ALL   |status,project_id|NULL   |NULL                       |11132|
|SIMPLE|project_table|eq_ref|PRIMARY,id       |PRIMARY|mantis_bug_table.project_id|    1|
|SIMPLE|user_monitor |ref   |NULL             |PRIMARY|mantis_bug_table.id        | 7378|
+------+-------------+------+-----------------+-------+---------------------------+-----+

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
+------+--------------------+------+-----------------+-------+---------------------------+-----+
|select|table               |type  |possible_keys    |key    |ref                        |rows |
+------+--------------------+------+-----------------+-------+---------------------------+-----+
|SIMPLE|mantis_bug_table    |ALL   |status,project_id|NULL   |NULL                       |11132|
|SIMPLE|mantis_project_table|eq_ref|PRIMARY,id       |PRIMARY|mantis_bug_table.project_id|    1|
|SIMPLE|user_monitor        |ref   |bug_id           |bug_id |mantis_bug_table.id        |    2|
+------+--------------------+------+-----------------+-------+---------------------------+-----+

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).
The bad news: since the fix involves a schema change, we can't deploy it in 1.2...


- Related Changesets
MantisBT: master 8ff83382
Timestamp: 2013-04-05 14:47:22
Author: dregad
Details ] 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 ]

- Issue History
Date Modified Username Field Change
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.x
2013-03-30 19:10 dregad Note Edited: 0035999 View Revisions
2013-03-30 19:12 dregad Note Edited: 0035999 View Revisions
2013-04-04 16:53 dregad Relationship added related to 0015713
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.x


MantisBT 1.2.17 [^]
Copyright © 2000 - 2014 MantisBT Team
Time: 0.0804 seconds.
memory usage: 3,058 KB
Powered by Mantis Bugtracker