Hi,
Below question got in one discussion.Appreciate any guidance on this.
Below is a query which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery , from the execution path it seems the total response time is mainly, to be sum of the "IN" and the "NOT IN" subquery section. My thought was that both "IN" and "NOT IN" should be executed/evaluated in parallel but not in serial fashion.
In the execution path below , the line number marked in bold are the top lines for the IN and NOT IN subquery evaluation and they are showing "Actual time" as Approx ~9 seconds and ~8 seconds and they seems to be summed up and the top lines showing it to be ~19 seconds. Then onwards it keeps on increasing with other "nested loop" joins.
Note:- This query is running on a MYSQL 8.0 database. So I'm wondering if there is any mysql list similar to Oracle list , in which i can share this issue?
Added the query in below path:-
https://gist.github.com/databasetech0073/95bce00c3a6bd4ae8d195401e0383185
SELECT ......
FROM R_CON_ESTS RC_STS,
R_CON rc,
D_LKP D_LKP_STS,
D_LKP D_LKP_FRQ,
(select RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_VER_NB
from R_CON_E RCE
where RCE.MTNE_ID in (SELECT MI1.MTNE_ID
FROM M_INF mi1 WHERE MI1.AID = :AID
UNION
SELECT rg.RG_MF_SK_ID
from RG_M_F_INF rg where rg.AS_ID =:AID
UNION
SELECT fti.FT_SRK_ID
from M_FT fti where fti.AS_ID= :AID
)
and (RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_ver_nb) NOT IN
(SELECT RCE_NS.DRV_DT, RCE_NS.AID, RCE_NS.R_CON_ID, RCE_NS.R_CON_VER_NB
FROM R_CON_E RCE_NS
WHERE RCE_NS.MTNE_ID NOT IN (select MI2.MTNE_ID
from M_INF MI2 where MI2.AID = :AID
UNION
SELECT RG2.RG_MF_SK_ID
from RG_M_F_INF RG2 where RG2.AS_ID =:AID
UNION
SELECT FTI1.FT_SRK_ID
from M_FT FTI1 where FTI1.AS_ID= :AID
))
) b
where RC_STS.RR_FRQ_NB = D_LKP_FRQ.D_LKP_NB
and RC_STS.R_CON_ESTS_NB = D_LKP_STS.D_LKP_NB
and RC_STS.R_CON_ID = rc.R_CON_ID
and RC_STS.R_CON_VER_NB = rc.R_CON_VER_NB
and RC_STS.AID = rc.AID
and RC_STS.AID = b.AID
and RC_STS.R_CON_ID = b.R_CON_ID
and RC_STS.R_CON_VER_NB = b.R_CON_VER_NB
order by 3,4,2;
-> Sort: RC_STS.R_CON_ID, RC_STS.R_CON_VER_NB, RC_STS.R_EX_RID (actual time=44392.655..44644.844 rows=745483 loops=1)
-> Stream results (cost=311479029610.37 rows=860847650219) (actual time=8957.556..42133.969 rows=745483 loops=1)
-> Nested loop inner join (cost=311479029610.37 rows=860847650219) (actual time=8957.548..40891.903 rows=745483 loops=1)
-> Nested loop inner join (cost=225393084569.25 rows=860847650219) (actual time=8957.541..40597.741 rows=745483 loops=1)
-> Nested loop inner join (cost=139307139528.12 rows=860847650219) (actual time=8957.530..40092.267 rows=745483 loops=1)
-> Nested loop antijoin (cost=53221194487.00 rows=532199430400) (actual time=8957.477..29529.382 rows=671352 loops=1)
-> Nested loop inner join (cost=886687.00 rows=729520) (actual time=0.123..19714.306 rows=692583 loops=1)
-> Filter: <in_optimizer>(RCE.MTNE_ID,<exists>(select #3)) (cost=84215.00 rows=729520) (actual time=0.085..9045.124 rows=692583 loops=1)
-> Covering index scan on RCE using R_58 (cost=84215.00 rows=729520) (actual time=0.055..534.110 rows=742706 loops=1)
-> Select #3 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union temporary> (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Union materialize with deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Covering index lookup on mi1 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX', MTNE_ID=<cache>(RCE.MTNE_ID)) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Single-row covering index lookup on rg using PRIMARY (RG_MF_SK_ID=<cache>(RCE.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX') (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter: (fti.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Covering index lookup on fti using AK_MFTI (FT_SRK_ID=<cache>(RCE.MTNE_ID)) (cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on rc using R_26 (AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.00 rows=1) (actual time=0.014..0.015 rows=1 loops=692583)
-> Single-row index lookup on <subquery7> using <auto_distinct_key> (DRV_DT=RCE.DRV_DT, AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=157167.31..157167.31 rows=1) (actual time=0.014..0.014 rows=0 loops=692583)
-> Materialize with deduplication (cost=157167.00..157167.00 rows=729520) (actual time=8957.347..8957.347 rows=25843 loops=1)
-> Filter: ((RCE_NS.DRV_DT is not null) and (RCE_NS.AID is not null) and (RCE_NS.R_CON_ID is not null) and (RCE_NS.R_CON_VER_NB is not null)) (cost=84215.00 rows=729520) (actual time=1737.420..8871.505 rows=50123 loops=1)
-> Filter: <in_optimizer>(RCE_NS.MTNE_ID,<exists>(select #8) is false) (cost=84215.00 rows=729520) (actual time=1737.417..8860.489 rows=50123 loops=1)
-> Covering index scan on RCE_NS using R_58 (cost=84215.00 rows=729520) (actual time=0.039..531.571 rows=742706 loops=1)
-> Select #8 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union temporary> (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Union materialize with deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.13 rows=1) (actual time=0.007..0.007 rows=1 loops=742706)
-> Covering index lookup on MI2 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX', MTNE_ID=<cache>(RCE_NS.MTNE_ID)) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.10 rows=1) (actual time=0.004..0.004 rows=1 loops=132294)
-> Single-row covering index lookup on RG2 using PRIMARY (RG_MF_SK_ID=<cache>(RCE_NS.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX') (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter: (FTI1.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Covering index lookup on FTI1 using AK_MFTI (FT_SRK_ID=<cache>(RCE_NS.MTNE_ID)) (cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on RC_STS using RCE_STS (AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.62 rows=2) (actual time=0.013..0.016 rows=1 loops=671352)
-> Single-row index lookup on D_LKP_STS using PRIMARY (D_LKP_NB=RC_STS.R_CON_ESTS_NB) (cost=1.00 rows=1) (actual time=0.000..0.000 rows=1 loops=745483)
-> Single-row index lookup on D_LKP_FRQ using PRIMARY (D_LKP_NB=RC_STS.RR_FRQ_NB) (cost=1.00 rows=1) (actual time=0.000..0.000 rows=1 loops=745483)
