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 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;