Query performance issue

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)