View Issue Details

IDProjectCategoryView StatusLast Update
0021998mantisbtperformancepublic2016-12-30 15:54
ReporterbezlAssigned Tocproensa 
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionfixed 
Product Version1.3.4 
Target Version1.3.5Fixed in Version1.3.5 
Summary0021998: My View page timeline history query performance
Description

MantisBT Version 1.3.4
Schema Version 209

Hi, please help - maybe we should add some indexes in database in order to make it work faster.

We have:
Issues: 163,137
Projects: 136

There's one query which takes 8.2 seconds to execute - see SQL log attached.

TagsNo tags attached.

Activities

bezl

bezl

2016-12-01 08:08

reporter  

my_view_page.sql.log (36,037 bytes)
cproensa

cproensa

2016-12-01 08:47

developer   ~0054649

Last edited: 2016-12-01 08:50

View 3 revisions

Can you provide some info:
There should exists an index "idx_bug_history_date_modified" in bug-history table.

8 secs is a lot of time, while all the other filter queries are very fast.

This is my example with all_projects:

DB-56   0.8605  history_api.php:237 history_get_range_result_filter()   
SELECT
  *
FROM
  mantis_bug_history_table
JOIN
  (
    SELECT DISTINCT
      mantis_bug_table.id
    FROM
      mantis_bug_table
    JOIN mantis_project_table
    ON
      mantis_project_table.id = mantis_bug_table.project_id
    WHERE
      mantis_project_table.enabled = 1
  )
  B ON mantis_bug_history_table.bug_id=B.id
WHERE
  date_modified  >= 1479994502
AND date_modified < 1480599302
ORDER BY
  mantis_bug_history_table.date_modified DESC,
  mantis_bug_history_table.id DESC
;

which is 0.8 secs

With these sizes:

SELECT count(1) FROM mantis_bug_history_table;
'2581780'
SELECT count(1) FROM mantis_bug_table;
'208881'
SELECT count(1) FROM mantis_project_table;
'173'

This is the explain from the previous query:

'1', 'PRIMARY', 'mantis_bug_history_table', 'range', 'idx_bug_history_bug_id,idx_bug_history_date_modified', 'idx_bug_history_date_modified', '4', NULL, '12874', 'Using index condition; Using filesort'
'1', 'PRIMARY', '<derived2>', 'ref', 'key0', 'key0', '4', 'mantis_bug_history_table.bug_id', '10', ''
'2', 'DERIVED', 'mantis_bug_table', 'ALL', 'idx_project', NULL, NULL, NULL, '208881', 'Using temporary'
'2', 'DERIVED', 'mantis_project_table', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'mantis_bug_table.project_id', '1', 'Using where; Distinct'

Execute your query in a sql client, and provide the explain plan to find where the time is going.

bezl

bezl

2016-12-02 03:01

reporter   ~0054658

Out mantis database tables are InnoDB, so STRAIGHT_JOIN on this query has fixed the issue and explain looks the same as yours now!

Thank you!

cproensa

cproensa

2016-12-02 03:51

developer   ~0054659

Did you change it directly in code?
Have you tried to "analyze table" instead?

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.

cproensa

cproensa

2016-12-02 04:04

developer   ~0054660

Last edited: 2016-12-02 04:05

View 2 revisions

@bezl
Can you compare this query, against the original (change date values as appropiate), in the same scenario where you get the long query times?

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.
We should not rely on specific dbms, but testing is good anyway.

SELECT
  *
FROM
  mantis_bug_history_table
WHERE
mantis_bug_history_table.bug_id IN (
  SELECT mantis_bug_table.id
    FROM
      mantis_bug_table
    JOIN mantis_project_table
    ON
      mantis_project_table.id = mantis_bug_table.project_id
    WHERE
      mantis_project_table.enabled = 1
)
AND date_modified  >= 1479994502
AND date_modified < 1480599302
ORDER BY
  mantis_bug_history_table.date_modified DESC,
  mantis_bug_history_table.id DESC
;

(wrote it by hand.., so syntax may be wrong)

bezl

bezl

2016-12-02 04:47

reporter   ~0054662

Yes, we tried analyze table before changing Mantis code.
We're using Percona Mysql 5.6.31-77.0

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)
Here is an explanation:

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 ,
1 , SIMPLE , mantis_bug_table , eq_ref , PRIMARY,idx_project , PRIMARY , 4 , uaapartments_bzi.mantis_bug_history_table.bug_id , 1 , NULL ,
1 , SIMPLE , mantis_project_table , eq_ref , PRIMARY,enabled , PRIMARY , 4 , uaapartments_bzi.mantis_bug_table.project_id , 1 , Using where ,

cproensa

cproensa

2016-12-02 05:24

developer   ~0054663

The IN subquery seems to be faster here.
I will test other dbs, and if the results are consistent, this can be changed.
Thanks

cproensa

cproensa

2016-12-02 19:29

developer   ~0054666

I did some tests:

With queries:
Q1: select ... JOIN (filter subquery)
Q2: select ... WHERE id in (filter subquery)
Q3: select ... WHERE EXISTS (filter subquery)

Aprox times, 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

Attached file with the queries and plans.

Q2 and Q3 are transformed exactly to the same plan.
oracle and postgres do a better job by optimizing Q1 than mysql.
mysql doesn't like Q1.



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;
history_query.txt (184,627 bytes)
cproensa

cproensa

2016-12-04 18:19

developer   ~0054670

https://github.com/mantisbt/mantisbt/pull/971

Related Changesets

MantisBT: master-1.3.x d69f9f5f

2016-12-04 18:07:43

cproensa


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
mod - core/history_api.php Diff File

Issue History

Date Modified Username Field Change
2016-12-01 08:08 bezl New Issue
2016-12-01 08:08 bezl File Added: my_view_page.sql.log
2016-12-01 08:47 cproensa Assigned To => cproensa
2016-12-01 08:47 cproensa Status new => feedback
2016-12-01 08:47 cproensa Note Added: 0054649
2016-12-01 08:49 cproensa Note Edited: 0054649 View Revisions
2016-12-01 08:50 cproensa Note Edited: 0054649 View Revisions
2016-12-02 03:01 bezl Note Added: 0054658
2016-12-02 03:01 bezl Status feedback => assigned
2016-12-02 03:51 cproensa Note Added: 0054659
2016-12-02 04:04 cproensa Note Added: 0054660
2016-12-02 04:05 cproensa Note Edited: 0054660 View Revisions
2016-12-02 04:47 bezl Note Added: 0054662
2016-12-02 05:24 cproensa Note Added: 0054663
2016-12-02 19:29 cproensa File Added: history_query.txt
2016-12-02 19:29 cproensa Note Added: 0054666
2016-12-02 19:31 cproensa Summary My View takes from 8 to 10 seconds to load. => My View page timeline history query performance
2016-12-02 19:32 cproensa Category db mysql => performance
2016-12-04 18:19 cproensa Note Added: 0054670
2016-12-24 05:31 dregad Changeset attached => MantisBT master-1.3.x d69f9f5f
2016-12-24 05:31 dregad Assigned To cproensa => dregad
2016-12-24 05:31 dregad Status assigned => resolved
2016-12-24 05:31 dregad Resolution open => fixed
2016-12-24 05:31 dregad Fixed in Version => 1.3.5
2016-12-24 05:35 dregad Assigned To dregad => cproensa
2016-12-24 05:35 dregad Target Version => 1.3.5
2016-12-30 15:54 vboctor Status resolved => closed