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.
(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 | ||
