Description:
The same statement may produce different results with different execution plans.
How to repeat:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16891
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.00 sec)
mysql> create table test4(col1 varchar(40), col2 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test4
-> values
-> ('3134' , 88663422)
-> ,('3135' , 88663423)
-> ,('3137' , 88663425)
-> ,('38866001' , 88663425)
-> ,('388660010001', 162023)
-> ,('388660010002', 88664116)
-> ,('38866002' , 88663697)
-> ,('388660020001', 88663697)
-> ,('388660020002', 88664117)
-> ,('38866003' , 88663724)
-> ,('388660030001', 88663724)
-> ,('388660030002', 88664247)
-> ,('38866004' , 88663763)
-> ,('388660040001', 88663763)
-> ,('388660040002', 88664238)
-> ,('38866005' , 88663972)
-> ,('388660050001', 88663972)
-> ,('388660050002', 88664163)
-> ,('388660050003', 88664222)
-> ,('38866006' , 88664031)
-> ,('388660060001', 88664031)
-> ,('388660060002', 88664183)
-> ,('3138' , 88663426)
-> ,('3140' , 88663428)
-> ,('3145' , 88663433)
-> ,('3147' , 88663435)
-> ,('3148' , 88663436)
-> ,('3149' , 88663437)
-> ,('3150' , 88663438)
-> ,('3151' , 88663439)
-> ,('3154' , 88663442)
-> ,('3155' , 88663443)
-> ,('3158' , 88663446)
-> ,('3160' , 88663448)
-> ,('3165' , 88663453)
-> ,('3168' , 88663456)
-> ,('3170' , 88663458)
-> ,('3172' , 88663460)
-> ,('3183' , 88663471)
-> ,('3185' , 88663473)
-> ,('3187' , 88663475)
-> ,('3189' , 88663477)
-> ,('3193' , 88663481)
-> ,('3194' , 88663482)
-> ,('3198' , 88663486)
-> ,('3199' , 88663487)
-> ,('3202' , 88663490)
-> ,('3204' , 88663492)
-> ,('3207' , 88663495)
-> ,('3213' , 88663501)
-> ,('3216' , 88663504)
-> ,('3218' , 88663506)
-> ,('3220' , 88663508)
-> ,('3224' , 88663512)
-> ,('3227' , 88663515)
-> ,('3236' , 88663524)
-> ,('3238' , 88663526)
-> ,('3243' , 88663531)
-> ,('3245' , 88663533)
-> ,('3246' , 88663534)
-> ,('3247' , 88663535)
-> ,('3248' , 88663536)
-> ,('3249' , 88663537)
-> ,('3256' , 88663544)
-> ,('3257' , 88663545)
-> ,('3259' , 88663547)
-> ,('3263' , 88663551)
-> ,('3265' , 88663553)
-> ,('3267' , 88663555)
-> ,('3271' , 88663559)
-> ,('3275' , 88663563)
-> ,('3280' , 88663568)
-> ,('3284' , 88664531)
-> ,('3285' , 88664532)
-> ,('3286' , 88664533)
-> ,('3292' , 88664849);
Query OK, 76 rows affected (0.01 sec)
Records: 76 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> with temp_itemcode as (
-> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
-> ), temp_data as (
-> select b.col1 as rcol1, a.col2, a.col1
-> from test4 a
-> join temp_itemcode b
-> on a.col1 like concat(b.col1,'%')
-> ), temp_data2 as (
-> select d.col1
-> from temp_data c
-> join temp_itemcode d
-> on c.col1 = d.col1 and c.rcol1=d.col1
-> )
-> select e.*, f.*
-> from temp_data2 e
-> join temp_data f
-> on f.rcol1 = e.col1
-> where not exists (select null
-> from temp_data g
-> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+--------------+
| col1 | rcol1 | col2 | col1 |
+----------+----------+----------+--------------+
| 38866001 | 38866001 | 88663425 | 38866001 |
| 38866001 | 38866001 | 162023 | 388660010001 |
| 38866001 | 38866001 | 88664116 | 388660010002 |
+----------+----------+----------+--------------+
3 rows in set (0.01 sec)
mysql> explain format=tree
-> with temp_itemcode as (
-> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
-> ), temp_data as (
-> select b.col1 as rcol1, a.col2, a.col1
-> from test4 a
-> join temp_itemcode b
-> on a.col1 like concat(b.col1,'%')
-> ), temp_data2 as (
-> select d.col1
-> from temp_data c
-> join temp_itemcode d
-> on c.col1 = d.col1 and c.rcol1=d.col1
-> )
-> select e.*, f.*
-> from temp_data2 e
-> join temp_data f
-> on f.rcol1 = e.col1
-> where not exists (select null
-> from temp_data g
-> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop anti-join
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join
-> Table scan on a (cost=0.14 rows=76)
-> Hash
-> Nested loop inner join
-> Nested loop inner join
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join (a.col1 = t.col1)
-> Table scan on a (cost=0.18 rows=76)
-> Hash
-> Filter: (t.col1 is not null)
-> Materialize table function
-> Materialize table function
-> Materialize table function
-> Nested loop inner join
-> Materialize table function
-> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%'))) (cost=46.55 rows=76)
-> Table scan on a (cost=46.55 rows=76)
1 row in set, 4 warnings (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------+
| Note | 1276 | Field or reference 'f.rcol1' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col2' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 |
+-------+------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> create index ind_test4_01 on test4(col1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index ind_test4_02 on test4(col2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> with temp_itemcode as (
-> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
-> ), temp_data as (
-> select b.col1 as rcol1, a.col2, a.col1
-> from test4 a
-> join temp_itemcode b
-> on a.col1 like concat(b.col1,'%')
-> ), temp_data2 as (
-> select d.col1
-> from temp_data c
-> join temp_itemcode d
-> on c.col1 = d.col1 and c.rcol1=d.col1
-> )
-> select e.*, f.*
-> from temp_data2 e
-> join temp_data f
-> on f.rcol1 = e.col1
-> where not exists (select null
-> from temp_data g
-> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
+----------+----------+----------+----------+
| col1 | rcol1 | col2 | col1 |
+----------+----------+----------+----------+
| 38866001 | 38866001 | 88663425 | 38866001 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)
mysql> explain format=tree
-> with temp_itemcode as (
-> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
-> ), temp_data as (
-> select b.col1 as rcol1, a.col2, a.col1
-> from test4 a
-> join temp_itemcode b
-> on a.col1 like concat(b.col1,'%')
-> ), temp_data2 as (
-> select d.col1
-> from temp_data c
-> join temp_itemcode d
-> on c.col1 = d.col1 and c.rcol1=d.col1
-> )
-> select e.*, f.*
-> from temp_data2 e
-> join temp_data f
-> on f.rcol1 = e.col1
-> where not exists (select null
-> from temp_data g
-> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Remove duplicate (t, a, a, t, t) rows using temporary table (weedout)
-> Nested loop anti-join
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join
-> Table scan on a (cost=0.55 rows=76)
-> Hash
-> Nested loop inner join
-> Filter: (t.col1 is not null)
-> Materialize table function
-> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1)
-> Nested loop inner join
-> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1)) (cost=0.28 rows=1)
-> Index lookup on a using ind_test4_02 (col2=a.col2) (cost=0.28 rows=1)
-> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%')))
-> Materialize table function
1 row in set, 4 warnings (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------+
| Note | 1276 | Field or reference 'f.rcol1' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col2' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 |
| Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 |
+-------+------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)
Suggested fix:
#WA1: no_semijoin()
with temp_itemcode as (
select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
), temp_data as (
select b.col1 as rcol1, a.col2, a.col1
from test4 a
join temp_itemcode b
on a.col1 like concat(b.col1,'%')
), temp_data2 as (
select d.col1
from temp_data c
join temp_itemcode d
on c.col1 = d.col1 and c.rcol1=d.col1
)
select e.*, f.*
from temp_data2 e
join temp_data f
on f.rcol1 = e.col1
where not exists (select/*+ no_semijoin()*/ null
from temp_data g
where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
col1 |rcol1 |col2 |col1
--------+--------+--------+------------
38866001|38866001|88663425|38866001
38866001|38866001| 162023|388660010001
38866001|38866001|88664116|388660010002
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: ((a.col1 like concat(t.col1,'%')) and exists(select #8) is false)
-> Inner hash join
-> Table scan on a (cost=0.55 rows=76)
-> Hash
-> Nested loop inner join
-> Filter: (t.col1 is not null)
-> Materialize table function
-> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1)
-> Select #8 (subquery in condition; dependent)
-> Limit: 1 row(s)
-> Nested loop inner join
-> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1)) (cost=0.28 rows=0)
-> Index lookup on a using ind_test4_02 (col2=a.col2), with index condition: (a.col2 = a.col2) (cost=0.28 rows=1)
-> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%')))
-> Materialize table function
#WA2: no_merge()
with temp_itemcode as (
select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
), temp_data as (
select b.col1 as rcol1, a.col2, a.col1
from test4 a
join temp_itemcode b
on a.col1 like concat(b.col1,'%')
), temp_data2 as (
select d.col1
from temp_data c
join temp_itemcode d
on c.col1 = d.col1 and c.rcol1=d.col1
)
select e.*, f.*
from temp_data2 e
join temp_data f
on f.rcol1 = e.col1
where not exists (select/*+ no_merge() */ null
from temp_data g
where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
col1 |rcol1 |col2 |col1
--------+--------+--------+------------
38866001|38866001|88663425|38866001
38866001|38866001| 162023|388660010001
38866001|38866001|88664116|388660010002
-> Nested loop anti-join
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join (t.col1 = t.col1)
-> Materialize table function
-> Hash
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join
-> Table scan on a (cost=0.55 rows=76)
-> Hash
-> Nested loop inner join
-> Filter: (t.col1 is not null)
-> Materialize table function
-> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1)
-> Filter: ((g.col1 like concat(a.col1,'%')) and (a.col1 <> g.col1))
-> Index lookup on g using <auto_key0> (rcol1=t.col1, col2=a.col2)
-> Materialize CTE temp_data
-> Filter: (a.col1 like concat(t.col1,'%'))
-> Inner hash join
-> Table scan on a (cost=0.55 rows=76)
-> Hash
-> Materialize table function
WA3: DO NOT USE JSON_TABLE
with temp_itemcode as (
-- select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
select '38866001' as col1 from dual
), temp_data as (
select b.col1 as rcol1, a.col2, a.col1
from test4 a
join temp_itemcode b
on a.col1 like concat(b.col1,'%')
), temp_data2 as (
select d.col1
from temp_data c
join temp_itemcode d
on c.col1 = d.col1 and c.rcol1=d.col1
)
select e.*, f.*
from temp_data2 e
join temp_data f
on f.rcol1 = e.col1
where not exists (select null
from temp_data g
where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
col1 |rcol1 |col2 |col1
--------+--------+--------+------------
38866001|38866001|88663425|38866001
38866001|38866001| 162023|388660010001
38866001|38866001|88664116|388660010002
-> Remove duplicate (b, d, b, a, a) rows using temporary table (weedout)
-> Nested loop anti-join
-> Inner hash join (cost=1.44 rows=1)
-> Filter: (a.col1 like <cache>(concat('38866001','%'))) (cost=1.09 rows=8)
-> Table scan on a (cost=1.09 rows=76)
-> Hash
-> Filter: ((a.col1 = '38866001') and (a.col1 like <cache>(concat('38866001','%')))) (cost=0.35 rows=1)
-> Index lookup on a using ind_test4_01 (col1='38866001') (cost=0.35 rows=1)
-> Nested loop inner join
-> Filter: (b.col1 = '38866001')
-> Table scan on b
-> Materialize CTE temp_itemcode if needed (query plan printed elsewhere)
-> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(b.col1,'%'))) (cost=0.31 rows=1)
-> Index lookup on a using ind_test4_02 (col2=a.col2) (cost=0.31 rows=1)
#WA4: diable/invisible/remove related indexes
alter table test4 alter index ind_test4_01 invisible;
alter table test4 alter index ind_test4_02 invisible;
with temp_itemcode as (
select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t
), temp_data as (
select b.col1 as rcol1, a.col2, a.col1
from test4 a
join temp_itemcode b
on a.col1 like concat(b.col1,'%')
), temp_data2 as (
select d.col1
from temp_data c
join temp_itemcode d
on c.col1 = d.col1 and c.rcol1=d.col1
)
select e.*, f.*
from temp_data2 e
join temp_data f
on f.rcol1 = e.col1
where not exists (select null
from temp_data g
where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1);
#WA5: INNER JOIN -> LEFT JOIN
...
select e.*, f.*
from temp_data2 e
left join temp_data f
on f.rcol1 = e.col1
...
Description: The same statement may produce different results with different execution plans. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16891 Server version: 8.0.18 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) mysql> create table test4(col1 varchar(40), col2 int); Query OK, 0 rows affected (0.02 sec) mysql> insert into test4 -> values -> ('3134' , 88663422) -> ,('3135' , 88663423) -> ,('3137' , 88663425) -> ,('38866001' , 88663425) -> ,('388660010001', 162023) -> ,('388660010002', 88664116) -> ,('38866002' , 88663697) -> ,('388660020001', 88663697) -> ,('388660020002', 88664117) -> ,('38866003' , 88663724) -> ,('388660030001', 88663724) -> ,('388660030002', 88664247) -> ,('38866004' , 88663763) -> ,('388660040001', 88663763) -> ,('388660040002', 88664238) -> ,('38866005' , 88663972) -> ,('388660050001', 88663972) -> ,('388660050002', 88664163) -> ,('388660050003', 88664222) -> ,('38866006' , 88664031) -> ,('388660060001', 88664031) -> ,('388660060002', 88664183) -> ,('3138' , 88663426) -> ,('3140' , 88663428) -> ,('3145' , 88663433) -> ,('3147' , 88663435) -> ,('3148' , 88663436) -> ,('3149' , 88663437) -> ,('3150' , 88663438) -> ,('3151' , 88663439) -> ,('3154' , 88663442) -> ,('3155' , 88663443) -> ,('3158' , 88663446) -> ,('3160' , 88663448) -> ,('3165' , 88663453) -> ,('3168' , 88663456) -> ,('3170' , 88663458) -> ,('3172' , 88663460) -> ,('3183' , 88663471) -> ,('3185' , 88663473) -> ,('3187' , 88663475) -> ,('3189' , 88663477) -> ,('3193' , 88663481) -> ,('3194' , 88663482) -> ,('3198' , 88663486) -> ,('3199' , 88663487) -> ,('3202' , 88663490) -> ,('3204' , 88663492) -> ,('3207' , 88663495) -> ,('3213' , 88663501) -> ,('3216' , 88663504) -> ,('3218' , 88663506) -> ,('3220' , 88663508) -> ,('3224' , 88663512) -> ,('3227' , 88663515) -> ,('3236' , 88663524) -> ,('3238' , 88663526) -> ,('3243' , 88663531) -> ,('3245' , 88663533) -> ,('3246' , 88663534) -> ,('3247' , 88663535) -> ,('3248' , 88663536) -> ,('3249' , 88663537) -> ,('3256' , 88663544) -> ,('3257' , 88663545) -> ,('3259' , 88663547) -> ,('3263' , 88663551) -> ,('3265' , 88663553) -> ,('3267' , 88663555) -> ,('3271' , 88663559) -> ,('3275' , 88663563) -> ,('3280' , 88663568) -> ,('3284' , 88664531) -> ,('3285' , 88664532) -> ,('3286' , 88664533) -> ,('3292' , 88664849); Query OK, 76 rows affected (0.01 sec) Records: 76 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> with temp_itemcode as ( -> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t -> ), temp_data as ( -> select b.col1 as rcol1, a.col2, a.col1 -> from test4 a -> join temp_itemcode b -> on a.col1 like concat(b.col1,'%') -> ), temp_data2 as ( -> select d.col1 -> from temp_data c -> join temp_itemcode d -> on c.col1 = d.col1 and c.rcol1=d.col1 -> ) -> select e.*, f.* -> from temp_data2 e -> join temp_data f -> on f.rcol1 = e.col1 -> where not exists (select null -> from temp_data g -> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); +----------+----------+----------+--------------+ | col1 | rcol1 | col2 | col1 | +----------+----------+----------+--------------+ | 38866001 | 38866001 | 88663425 | 38866001 | | 38866001 | 38866001 | 162023 | 388660010001 | | 38866001 | 38866001 | 88664116 | 388660010002 | +----------+----------+----------+--------------+ 3 rows in set (0.01 sec) mysql> explain format=tree -> with temp_itemcode as ( -> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t -> ), temp_data as ( -> select b.col1 as rcol1, a.col2, a.col1 -> from test4 a -> join temp_itemcode b -> on a.col1 like concat(b.col1,'%') -> ), temp_data2 as ( -> select d.col1 -> from temp_data c -> join temp_itemcode d -> on c.col1 = d.col1 and c.rcol1=d.col1 -> ) -> select e.*, f.* -> from temp_data2 e -> join temp_data f -> on f.rcol1 = e.col1 -> where not exists (select null -> from temp_data g -> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G *************************** 1. row *************************** EXPLAIN: -> Nested loop anti-join -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join -> Table scan on a (cost=0.14 rows=76) -> Hash -> Nested loop inner join -> Nested loop inner join -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join (a.col1 = t.col1) -> Table scan on a (cost=0.18 rows=76) -> Hash -> Filter: (t.col1 is not null) -> Materialize table function -> Materialize table function -> Materialize table function -> Nested loop inner join -> Materialize table function -> Filter: ((a.col2 = a.col2) and (a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(t.col1,'%'))) (cost=46.55 rows=76) -> Table scan on a (cost=46.55 rows=76) 1 row in set, 4 warnings (0.00 sec) mysql> show warnings; +-------+------+---------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------+ | Note | 1276 | Field or reference 'f.rcol1' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col2' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 | +-------+------+---------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> create index ind_test4_01 on test4(col1); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index ind_test4_02 on test4(col2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> with temp_itemcode as ( -> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t -> ), temp_data as ( -> select b.col1 as rcol1, a.col2, a.col1 -> from test4 a -> join temp_itemcode b -> on a.col1 like concat(b.col1,'%') -> ), temp_data2 as ( -> select d.col1 -> from temp_data c -> join temp_itemcode d -> on c.col1 = d.col1 and c.rcol1=d.col1 -> ) -> select e.*, f.* -> from temp_data2 e -> join temp_data f -> on f.rcol1 = e.col1 -> where not exists (select null -> from temp_data g -> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); +----------+----------+----------+----------+ | col1 | rcol1 | col2 | col1 | +----------+----------+----------+----------+ | 38866001 | 38866001 | 88663425 | 38866001 | +----------+----------+----------+----------+ 1 row in set (0.00 sec) mysql> explain format=tree -> with temp_itemcode as ( -> select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t -> ), temp_data as ( -> select b.col1 as rcol1, a.col2, a.col1 -> from test4 a -> join temp_itemcode b -> on a.col1 like concat(b.col1,'%') -> ), temp_data2 as ( -> select d.col1 -> from temp_data c -> join temp_itemcode d -> on c.col1 = d.col1 and c.rcol1=d.col1 -> ) -> select e.*, f.* -> from temp_data2 e -> join temp_data f -> on f.rcol1 = e.col1 -> where not exists (select null -> from temp_data g -> where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1)\G *************************** 1. row *************************** EXPLAIN: -> Remove duplicate (t, a, a, t, t) rows using temporary table (weedout) -> Nested loop anti-join -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join -> Table scan on a (cost=0.55 rows=76) -> Hash -> Nested loop inner join -> Filter: (t.col1 is not null) -> Materialize table function -> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1) -> Nested loop inner join -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1)) (cost=0.28 rows=1) -> Index lookup on a using ind_test4_02 (col2=a.col2) (cost=0.28 rows=1) -> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%'))) -> Materialize table function 1 row in set, 4 warnings (0.00 sec) mysql> show warnings; +-------+------+---------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------+ | Note | 1276 | Field or reference 'f.rcol1' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col2' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 | | Note | 1276 | Field or reference 'f.col1' of SELECT #8 was resolved in SELECT #1 | +-------+------+---------------------------------------------------------------------+ 4 rows in set (0.00 sec) Suggested fix: #WA1: no_semijoin() with temp_itemcode as ( select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t ), temp_data as ( select b.col1 as rcol1, a.col2, a.col1 from test4 a join temp_itemcode b on a.col1 like concat(b.col1,'%') ), temp_data2 as ( select d.col1 from temp_data c join temp_itemcode d on c.col1 = d.col1 and c.rcol1=d.col1 ) select e.*, f.* from temp_data2 e join temp_data f on f.rcol1 = e.col1 where not exists (select/*+ no_semijoin()*/ null from temp_data g where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); col1 |rcol1 |col2 |col1 --------+--------+--------+------------ 38866001|38866001|88663425|38866001 38866001|38866001| 162023|388660010001 38866001|38866001|88664116|388660010002 -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: ((a.col1 like concat(t.col1,'%')) and exists(select #8) is false) -> Inner hash join -> Table scan on a (cost=0.55 rows=76) -> Hash -> Nested loop inner join -> Filter: (t.col1 is not null) -> Materialize table function -> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1) -> Select #8 (subquery in condition; dependent) -> Limit: 1 row(s) -> Nested loop inner join -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1)) (cost=0.28 rows=0) -> Index lookup on a using ind_test4_02 (col2=a.col2), with index condition: (a.col2 = a.col2) (cost=0.28 rows=1) -> Filter: ((t.col1 = t.col1) and (a.col1 like concat(t.col1,'%'))) -> Materialize table function #WA2: no_merge() with temp_itemcode as ( select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t ), temp_data as ( select b.col1 as rcol1, a.col2, a.col1 from test4 a join temp_itemcode b on a.col1 like concat(b.col1,'%') ), temp_data2 as ( select d.col1 from temp_data c join temp_itemcode d on c.col1 = d.col1 and c.rcol1=d.col1 ) select e.*, f.* from temp_data2 e join temp_data f on f.rcol1 = e.col1 where not exists (select/*+ no_merge() */ null from temp_data g where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); col1 |rcol1 |col2 |col1 --------+--------+--------+------------ 38866001|38866001|88663425|38866001 38866001|38866001| 162023|388660010001 38866001|38866001|88664116|388660010002 -> Nested loop anti-join -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join (t.col1 = t.col1) -> Materialize table function -> Hash -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join -> Table scan on a (cost=0.55 rows=76) -> Hash -> Nested loop inner join -> Filter: (t.col1 is not null) -> Materialize table function -> Index lookup on a using ind_test4_01 (col1=t.col1) (cost=0.30 rows=1) -> Filter: ((g.col1 like concat(a.col1,'%')) and (a.col1 <> g.col1)) -> Index lookup on g using <auto_key0> (rcol1=t.col1, col2=a.col2) -> Materialize CTE temp_data -> Filter: (a.col1 like concat(t.col1,'%')) -> Inner hash join -> Table scan on a (cost=0.55 rows=76) -> Hash -> Materialize table function WA3: DO NOT USE JSON_TABLE with temp_itemcode as ( -- select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t select '38866001' as col1 from dual ), temp_data as ( select b.col1 as rcol1, a.col2, a.col1 from test4 a join temp_itemcode b on a.col1 like concat(b.col1,'%') ), temp_data2 as ( select d.col1 from temp_data c join temp_itemcode d on c.col1 = d.col1 and c.rcol1=d.col1 ) select e.*, f.* from temp_data2 e join temp_data f on f.rcol1 = e.col1 where not exists (select null from temp_data g where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); col1 |rcol1 |col2 |col1 --------+--------+--------+------------ 38866001|38866001|88663425|38866001 38866001|38866001| 162023|388660010001 38866001|38866001|88664116|388660010002 -> Remove duplicate (b, d, b, a, a) rows using temporary table (weedout) -> Nested loop anti-join -> Inner hash join (cost=1.44 rows=1) -> Filter: (a.col1 like <cache>(concat('38866001','%'))) (cost=1.09 rows=8) -> Table scan on a (cost=1.09 rows=76) -> Hash -> Filter: ((a.col1 = '38866001') and (a.col1 like <cache>(concat('38866001','%')))) (cost=0.35 rows=1) -> Index lookup on a using ind_test4_01 (col1='38866001') (cost=0.35 rows=1) -> Nested loop inner join -> Filter: (b.col1 = '38866001') -> Table scan on b -> Materialize CTE temp_itemcode if needed (query plan printed elsewhere) -> Filter: ((a.col1 like concat(a.col1,'%')) and (a.col1 <> a.col1) and (a.col1 like concat(b.col1,'%'))) (cost=0.31 rows=1) -> Index lookup on a using ind_test4_02 (col2=a.col2) (cost=0.31 rows=1) #WA4: diable/invisible/remove related indexes alter table test4 alter index ind_test4_01 invisible; alter table test4 alter index ind_test4_02 invisible; with temp_itemcode as ( select col1 from json_table('["38866001"]' ,'$[*]' columns(col1 varchar(40) character set utf8mb4 path '$')) as t ), temp_data as ( select b.col1 as rcol1, a.col2, a.col1 from test4 a join temp_itemcode b on a.col1 like concat(b.col1,'%') ), temp_data2 as ( select d.col1 from temp_data c join temp_itemcode d on c.col1 = d.col1 and c.rcol1=d.col1 ) select e.*, f.* from temp_data2 e join temp_data f on f.rcol1 = e.col1 where not exists (select null from temp_data g where f.rcol1=g.rcol1 and f.col2=g.col2 and g.col1 like concat(f.col1,'%') and f.col1 != g.col1); #WA5: INNER JOIN -> LEFT JOIN ... select e.*, f.* from temp_data2 e left join temp_data f on f.rcol1 = e.col1 ...