# Mysql dependent subquery every loop execute three loops **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Tags:** mysql\ **Created:** [May 10, 2024, 7:41am UTC](https://forums.percona.com/t/mysql-dependent-subquery-every-loop-execute-three-loops/30214 "2024-05-10T07:41:26Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![jingxu](https://avatars.discourse-cdn.com/v4/letter/j/c2a13f/32.png) [@jingxu](https://forums.percona.com/u/jingxu)\ **Post date:** [May 10, 2024, 7:41am UTC](https://forums.percona.com/t/mysql-dependent-subquery-every-loop-execute-three-loops/30214/1 "2024-05-10T07:41:26Z")
hello: i have two tables,this relation is 1:N,but two tables left join on A.id=(dependent subquery). so outer table A one record,then dependent subquery execute three loops; why every loop execute three loops? for example: CREATE TABLE `a` ( `id` int NOT NULL DEFAULT ‘0’, `claim_status` int NOT NULL, `claim_time` datetime DEFAULT NULL, `type_id` int DEFAULT NULL , `create_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci; CREATE TABLE `b` ( `id` varchar(40) CHARACTER SET utf8 NOT NULL, `headless_package_id` int DEFAULT NULL, `claim_time` datetime DEFAULT NULL, `confirm_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `index01` (`headless_package_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_0900\_ai\_ci; root@localhost : test 03:32:02\> select \* from A; ±---------±-------------±--------------------±--------±--------------------+ | id | claim\_status | claim\_time | type\_id | create\_time | ±---------±-------------±--------------------±--------±--------------------+ | 99358404 | 2 | 2022-01-08 10:05:43 | 0 | 2022-01-01 00:10:33 | | 99358412 | 2 | 2022-01-10 14:56:02 | 0 | 2022-01-01 00:13:26 | | 99358422 | 2 | 2022-01-19 10:50:43 | 0 | 2022-01-01 00:19:03 | | 99358442 | 2 | 2022-01-06 19:27:27 | 0 | 2022-01-01 00:30:23 | | 99358446 | 2 | 2022-01-07 18:22:02 | 0 | 2022-01-01 00:32:31 | | 99358448 | 2 | 2022-01-07 10:51:45 | 0 | 2022-01-01 00:33:07 | | 99358450 | 2 | 2022-01-04 11:03:58 | 0 | 2022-01-01 00:34:20 | | 99358452 | 2 | 2022-01-07 09:43:12 | 0 | 2022-01-01 00:36:49 | ±---------±-------------±--------------------±--------±--------------------+ 8 rows in set (0.00 sec) root@localhost : test 03:32:42\> select \* from B; ±---------------------------------±--------------------±--------------------±--------------------+ | id | headless\_package\_id | claim\_time | confirm\_time | ±---------------------------------±--------------------±--------------------±--------------------+ | 443ec574f6c14539a549a5729be3e7f7 | 99358412 | 2022-01-10 14:56:02 | 2022-01-10 15:23:02 | | 442f842b75e94d8bb62251900b3296e3 | 99358442 | 2022-01-06 19:27:28 | 2022-01-07 10:20:10 | | 44f0ba8c7cdb4e2fa728bd2b7eb18244 | 99358448 | 2022-01-07 10:51:46 | 2022-01-07 11:11:06 | | 44415ed44cd1480fb03cf3da68f87b68 | 99358450 | 2022-01-03 16:02:41 | 2022-01-03 17:47:38 | | 44b867e336554c139bf30346c141f98d | 99358446 | 2022-01-07 18:22:03 | 2022-01-08 09:01:49 | | 444d00e4cf324e42a3ef6e9f34c0599f | 99358404 | 2022-01-08 10:05:43 | 2022-01-08 13:36:53 | | 4492fb67f3774c97a359fdbe11aa2bb2 | 99358452 | 2022-01-07 09:43:13 | 2022-01-07 10:36:26 | | 44838565c02c4daf98cf9b3d333d2d2d | 99358452 | 2022-01-06 12:13:25 | 2022-01-06 13:24:58 | | 4434a8c0141243adbff26406b5b484d6 | 99358422 | 2022-01-19 10:50:43 | 2022-01-19 11:01:39 | | 44f57bc8ac4c4fbe9ec4289bb0d6c99e | 99358452 | 2022-01-03 09:52:19 | 2022-01-03 10:37:11 | | 445f647b1075462f8ef7189ab75ea6cc | 99358450 | 2022-01-04 11:03:58 | 2022-01-04 18:13:29 | ±---------------------------------±--------------------±--------------------±--------------------+ 11 rows in set (0.00 sec) example A : A has 8 records,B loop 24. explain analyze select h.id as hid from A h LEFT JOIN B claim ON claim.id = (SELECT c\_in.id FROM B c\_in WHERE c\_in.headless\_package\_id = h.id ORDER BY c\_in.claim\_time DESC LIMIT 1); | EXPLAIN | ±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | → Nested loop left join (cost=3.85 rows=8) (actual time=0.313…0.800 rows=8 loops=1) → Index scan on h using PRIMARY (cost=1.05 rows=8) (actual time=0.053…0.061 rows=8 loops=1) → Filter: (claim.id = (select #2)) (cost=0.26 rows=1) (actual time=0.091…0.091 rows=1 loops=8) → Single-row index lookup on claim using PRIMARY (id=(select #2)) (cost=0.26 rows=1) (actual time=0.074…0.074 rows=1 loops=8) → Select #2 (subquery in condition; dependent) → Limit: 1 row(s) (cost=0.48 rows=1) (actual time=0.023…0.023 rows=1 loops=24) → Sort: c\_in.claim\_time DESC, limit input to 1 row(s) per chunk (cost=0.48 rows=1) (actual time=0.023…0.023 rows=1 loops=24) → Index lookup on c\_in using index01 (headless\_package\_id=h.id) (actual time=0.015…0.017 rows=1 loops=24) ---
**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\ **Post date:** [May 13, 2024, 1:24pm UTC](https://forums.percona.com/t/mysql-dependent-subquery-every-loop-execute-three-loops/30214/2 "2024-05-13T13:24:39Z")
Hi jingxu, The query has 3 “steps”. Table A, B and the subquery . You can check that by running the regular ANALYZE MySQL will be very inefficient in resolving this kind of queries with a dependent subquery. You should avoid having subqueries if possible