View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0021998 | mantisbt | performance | public | 2016-12-01 08:08 | 2016-12-30 15:54 |
Reporter | bezl | Assigned To | cproensa | ||
Priority | normal | Severity | minor | Reproducibility | have not tried |
Status | closed | Resolution | fixed | ||
Product Version | 1.3.4 | ||||
Target Version | 1.3.5 | Fixed in Version | 1.3.5 | ||
Summary | 0021998: My View page timeline history query performance | ||||
Description | MantisBT Version 1.3.4 Hi, please help - maybe we should add some indexes in database in order to make it work faster. We have: There's one query which takes 8.2 seconds to execute - see SQL log attached. | ||||
Tags | No tags attached. | ||||
Attached Files | |||||
Can you provide some info: 8 secs is a lot of time, while all the other filter queries are very fast. This is my example with all_projects: which is 0.8 secs With these sizes: This is the explain from the previous query: Execute your query in a sql client, and provide the explain plan to find where the time is going. |
|
Out mantis database tables are InnoDB, so STRAIGHT_JOIN on this query has fixed the issue and Thank you! |
|
Did you change it directly in code? which version of mysql are you using? (i run on mariadb, fyi) Your feedback is useful because more queries like this, that join with filters, may be added in the future. |
|
@bezl Notice that instead of the explicit join, this IN would make mysql optimize it as semi-join, which may have a better chance with the optimizer. <pre> (wrote it by hand.., so syntax may be wrong) |
|
Yes, we tried analyze table before changing Mantis code. We've tried also to change the engine back to MyISAM and "Explain" looks exactly as yours without STRAIGHT_JOIN. But we get alot of "Waiting for table level lock" on MyISAM. Your last query on InnoDB executed fast enough. Result is 58679 rows in set (0.38 sec) 1 , SIMPLE , mantis_bug_history_table , range , idx_bug_history_bug_id,idx_bug_history_date_modified , idx_bug_history_date_modified , 4 , NULL , 112896 , Using index condition , |
|
The IN subquery seems to be faster here. |
|
I did some tests: With queries: Aprox times, in seconds: mysql (mariadb 12.2) pgsql 9.3 oracle Attached file with the queries and plans. Q2 and Q3 are transformed exactly to the same plan. history_query.txt (184,627 bytes)
time in seconds mysql (mariadb 12.2) Q1 < 0.8 Q2 < 0.03 Q3 < 0.03 pgsql 9.3 Q1 < 0.33 Q2 < 0.23 Q3 < 0.26 oracle Q1 < 0.4 Q2 < 0.25 Q3 < 0.25 ------------------------------------ Q1: ------------------------------------ SELECT H.*, now() FROM mantispro.mantis_bug_history_table H JOIN ( SELECT DISTINCT mantis_bug_table.id FROM mantispro.mantis_bug_table JOIN mantispro.mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 ) B ON H.bug_id=B.id WHERE date_modified >= 1400843387 AND date_modified < 1400843387+604800 ORDER BY H.date_modified DESC, H.id DESC ; ------------------------------------ Q2: ------------------------------------ SELECT H.*, now() FROM mantispro.mantis_bug_history_table H WHERE H.bug_id IN ( SELECT mantis_bug_table.id FROM mantispro.mantis_bug_table JOIN mantispro.mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 ) AND date_modified >= 1400843387 AND date_modified < 1400843387+604800 ORDER BY H.date_modified DESC, H.id DESC ; ------------------------------------ Q3: ------------------------------------ SELECT H.*, now() FROM mantispro.mantis_bug_history_table H WHERE exists ( SELECT 1 FROM mantispro.mantis_bug_table JOIN mantispro.mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = 1 AND mantis_bug_table.id=H.bug_id ) AND date_modified >= 1400843387 AND date_modified < 1400843387+604800 ORDER BY H.date_modified DESC, H.id DESC ; ------------------------------------ MYSQL EXPLAIN: ------------------------------------ 1 PRIMARY H range idx_bug_history_bug_id,idx_bug_history_date_modified idx_bug_history_date_modified 4 NULL 7669 Using index condition; Using filesort 1 PRIMARY <derived2> ref key0 key0 4 mantispro.H.bug_id 10 2 DERIVED mantis_bug_table ALL idx_project NULL NULL NULL 208884 Using temporary 2 DERIVED mantis_project_table eq_ref PRIMARY PRIMARY 4 mantispro.mantis_bug_table.project_id 1 Using where; Distinct 1 PRIMARY H range idx_bug_history_bug_id,idx_bug_history_date_modified idx_bug_history_date_modified 4 NULL 7669 Using index condition; Using filesort 1 PRIMARY mantis_bug_table eq_ref PRIMARY,idx_project PRIMARY 4 mantispro.H.bug_id 1 1 PRIMARY mantis_project_table eq_ref PRIMARY PRIMARY 4 mantispro.mantis_bug_table.project_id 1 Using where 1 PRIMARY H range idx_bug_history_bug_id,idx_bug_history_date_modified idx_bug_history_date_modified 4 NULL 7669 Using index condition; Using filesort 1 PRIMARY mantis_bug_table eq_ref PRIMARY,idx_project PRIMARY 4 mantispro.H.bug_id 1 1 PRIMARY mantis_project_table eq_ref PRIMARY PRIMARY 4 mantispro.mantis_bug_table.project_id 1 Using where ------------------------------------ PGSQL EXPLAIN: ------------------------------------ Sort (cost=35953.29..35974.63 rows=8538 width=47) Sort Key: h.date_modified, h.id -> Merge Join (cost=31696.53..35395.77 rows=8538 width=47) Merge Cond: (mantis_bug_table.id = h.bug_id) -> Unique (cost=30783.38..31797.62 rows=202847 width=8) -> Sort (cost=30783.38..31290.50 rows=202847 width=8) Sort Key: mantis_bug_table.id -> Hash Join (cost=6.26..10126.89 rows=202847 width=8) Hash Cond: (mantis_bug_table.project_id = mantis_project_table.id) -> Seq Scan on mantis_bug_table (cost=0.00..7308.84 rows=208884 width=16) -> Hash (cost=4.16..4.16 rows=168 width=8) -> Seq Scan on mantis_project_table (cost=0.00..4.16 rows=168 width=8) Filter: (enabled = 1) -> Sort (cost=913.15..934.49 rows=8538 width=47) Sort Key: h.bug_id -> Index Scan using idx_34531_idx_bug_history_date_modified on mantis_bug_history_table h (cost=0.43..355.63 rows=8538 width=47) Index Cond: ((date_modified >= 1400843387) AND (date_modified < 1401448187)) QUERY PLAN Sort (cost=15505.73..15527.07 rows=8538 width=47) Sort Key: h.date_modified, h.id -> Hash Semi Join (cost=13455.91..14948.21 rows=8538 width=47) Hash Cond: (h.bug_id = mantis_bug_table.id) -> Index Scan using idx_34531_idx_bug_history_date_modified on mantis_bug_history_table h (cost=0.43..355.63 rows=8538 width=47) Index Cond: ((date_modified >= 1400843387) AND (date_modified < 1401448187)) -> Hash (cost=10126.89..10126.89 rows=202847 width=8) -> Hash Join (cost=6.26..10126.89 rows=202847 width=8) Hash Cond: (mantis_bug_table.project_id = mantis_project_table.id) -> Seq Scan on mantis_bug_table (cost=0.00..7308.84 rows=208884 width=16) -> Hash (cost=4.16..4.16 rows=168 width=8) -> Seq Scan on mantis_project_table (cost=0.00..4.16 rows=168 width=8) Filter: (enabled = 1) QUERY PLAN Sort (cost=15505.73..15527.07 rows=8538 width=47) Sort Key: h.date_modified, h.id -> Hash Semi Join (cost=13455.91..14948.21 rows=8538 width=47) Hash Cond: (h.bug_id = mantis_bug_table.id) -> Index Scan using idx_34531_idx_bug_history_date_modified on mantis_bug_history_table h (cost=0.43..355.63 rows=8538 width=47) Index Cond: ((date_modified >= 1400843387) AND (date_modified < 1401448187)) -> Hash (cost=10126.89..10126.89 rows=202847 width=8) -> Hash Join (cost=6.26..10126.89 rows=202847 width=8) Hash Cond: (mantis_bug_table.project_id = mantis_project_table.id) -> Seq Scan on mantis_bug_table (cost=0.00..7308.84 rows=208884 width=16) -> Hash (cost=4.16..4.16 rows=168 width=8) -> Seq Scan on mantis_project_table (cost=0.00..4.16 rows=168 width=8) Filter: (enabled = 1) ------------------------------------ ORACLE EXPLAIN: ------------------------------------ 1861 SELECT STATEMENT 1861 SORT ORDER BY 1770 HASH JOIN 122 JOIN FILTER CREATE :BF0000 122 TABLE ACCESS BY INDEX ROWID MANTIS_BUG_HISTORY_TABLE 36 INDEX RANGE SCAN IDX_BUG_HISTORY_DATE_MODIFIED 1646 VIEW 1646 HASH UNIQUE 1000 JOIN FILTER USE :BF0000 1000 HASH JOIN 3 TABLE ACCESS FULL MANTIS_PROJECT_TABLE 994 TABLE ACCESS FULL MANTIS_BUG_TABLE 1215 SELECT STATEMENT 1215 SORT ORDER BY 1124 HASH JOIN SEMI 122 TABLE ACCESS BY INDEX ROWID MANTIS_BUG_HISTORY_TABLE 36 INDEX RANGE SCAN IDX_BUG_HISTORY_DATE_MODIFIED 1000 VIEW VW_NSO_1 1000 HASH JOIN 3 TABLE ACCESS FULL MANTIS_PROJECT_TABLE 994 TABLE ACCESS FULL MANTIS_BUG_TABLE 1215 SELECT STATEMENT 1215 SORT ORDER BY 1124 HASH JOIN SEMI 122 TABLE ACCESS BY INDEX ROWID MANTIS_BUG_HISTORY_TABLE 36 INDEX RANGE SCAN IDX_BUG_HISTORY_DATE_MODIFIED 1000 VIEW VW_SQ_1 1000 HASH JOIN 3 TABLE ACCESS FULL MANTIS_PROJECT_TABLE 994 TABLE ACCESS FULL MANTIS_BUG_TABLE ------------------- WITH dates AS ( select tmin, tmax, tmax-tmin as tdif from (select min(last_updated)+604800 as tmin, max(last_updated) as tmax from mantispro.mantis_bug_table) z ), trand AS ( select tmin + round(dbms_random.value * tdif) AS tstart from dates ) select tstart from trand; |
|
MantisBT: master-1.3.x d69f9f5f 2016-12-04 13:07 Committer: dregad Details Diff |
Modify history filter range query To get history rows based on filter, use SELECT .. FROM history WHERE bug_id IN ( filter query ) instead of SELECT .. FROM history JOIN ( filter query ) It's a faster join, and generally is better optimized by different database engines. Fixes: 0021998 |
Affected Issues 0021998 |
|
mod - core/history_api.php | Diff File |