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)