The first SQL statement is the original query generated by the
application.
Currently, the TiDB optimizer cannot automatically transform this form
into the most efficient execution plan, resulting in a suboptimal plan.
A workaround was tested by rewriting the IN subquery into an EXISTS
subquery. This significantly improved the execution plan.
However, further investigation shows that the original SQL itself is not
optimal: the JOIN members inside the subquery is unnecessary. After
removing that redundant join, even the IN version can produce a good
execution plan.
Below are the three SQL variants and their corresponding execution plans.
EXPLAIN SELECT COUNT(1)
FROM `international_contractors`
JOIN `members`
ON `members`.`id` = `international_contractors`.`member_id`
WHERE members.id IN (
SELECT DISTINCT
`members`.`id`
FROM `members`
JOIN `member_employments`
ON `member_employments`.`member_id` = `members`.`id`
WHERE (
`member_employments`.`start_date` <= '2026-01-05'
AND (
`member_employments`.`end_date` IS NULL
OR `member_employments`.`end_date` >= '2026-01-05'
)
)
AND (
`member_employments`.`worker_type` = 'Contractor'
AND `member_employments`.`country_code` != 'US'
)
AND `member_employments`.`member_id` = `members`.`id`
)
AND `international_contractors`.`company_id` = 7757616926151792;
+------------------------------------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_24 | 1.00 | root | | funcs:count(1)->Column#84 |
| └─HashJoin_184 | 4.13 | root | | inner join, equal:[eq(zenpayroll_evaluation_v3.members.id, zenpayroll_evaluation_v3.members.id)] |
| ├─IndexJoin_74(Build) | 4.13 | root | | inner join, inner:TableReader_71, outer key:zenpayroll_evaluation_v3.international_contractors.member_id, inner key:zenpayroll_evaluation_v3.members.id, equal cond:eq(zenpayroll_evaluation_v3.international_contractors.member_id, zenpayroll_evaluation_v3.members.id) |
| │ ├─IndexLookUp_97(Build) | 4.13 | root | | |
| │ │ ├─IndexRangeScan_95(Build) | 4.13 | cop[tikv] | table:international_contractors, index:index_international_contractors_on_company_id(company_id) | range:[7757616926151792,7757616926151792], keep order:false |
| │ │ └─TableRowIDScan_96(Probe) | 4.13 | cop[tikv] | table:international_contractors | keep order:false |
| │ └─TableReader_71(Probe) | 4.13 | root | | data:TableRangeScan_70 |
| │ └─TableRangeScan_70 | 4.13 | cop[tikv] | table:members | range: decided by [zenpayroll_evaluation_v3.international_contractors.member_id], keep order:false |
| └─HashAgg_118(Probe) | 15137.86 | root | | group by:zenpayroll_evaluation_v3.members.id, funcs:firstrow(zenpayroll_evaluation_v3.members.id)->zenpayroll_evaluation_v3.members.id |
| └─IndexJoin_139 | 15137.86 | root | | inner join, inner:IndexLookUp_138, outer key:zenpayroll_evaluation_v3.members.id, inner key:zenpayroll_evaluation_v3.member_employments.member_id, equal cond:eq(zenpayroll_evaluation_v3.members.id, zenpayroll_evaluation_v3.member_employments.member_id) |
| ├─IndexReader_167(Build) | 17030146.00 | root | | index:IndexFullScan_166 |
| │ └─IndexFullScan_166 | 17030146.00 | cop[tikv] | table:members, index:index_members_on_company_id(company_id) | keep order:false |
| └─IndexLookUp_138(Probe) | 15137.86 | root | | |
| ├─Selection_136(Build) | 1335725.74 | cop[tikv] | | or(isnull(zenpayroll_evaluation_v3.member_employments.end_date), ge(zenpayroll_evaluation_v3.member_employments.end_date, 2026-01-05 00:00:00.000000)) |
| │ └─IndexRangeScan_134 | 2907652.23 | cop[tikv] | table:member_employments, index:idx_on_member_id_worker_type_start_date_end_date_120150c221(member_id, worker_type, start_date, end_date) | range: decided by [eq(zenpayroll_evaluation_v3.member_employments.member_id, zenpayroll_evaluation_v3.members.id) eq(zenpayroll_evaluation_v3.member_employments.worker_type, Contractor) le(zenpayroll_evaluation_v3.member_employments.start_date, 2026-01-05 00:00:00.000000)], keep order:false |
| └─Selection_137(Probe) | 15137.86 | cop[tikv] | | ne(zenpayroll_evaluation_v3.member_employments.country_code, "US") |
| └─TableRowIDScan_135 | 1335725.74 | cop[tikv] | table:member_employments | keep order:false |
+------------------------------------------+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+EXPLAIN SELECT COUNT(1)
FROM `international_contractors`
JOIN `members`
ON `members`.`id` = `international_contractors`.`member_id`
WHERE EXISTS (
SELECT /*+ NO_DECORRELATE() INL_JOIN(inner_members, member_employments ) */ 1
FROM `members` AS `inner_members`
JOIN `member_employments`
ON `member_employments`.`member_id` = `inner_members`.`id`
WHERE (
`member_employments`.`start_date` <= '2026-01-05'
AND (
`member_employments`.`end_date` IS NULL
OR `member_employments`.`end_date` >= '2026-01-05'
)
)
AND (
`member_employments`.`worker_type` = 'Contractor'
AND `member_employments`.`country_code` != 'US'
)
AND `member_employments`.`member_id` = `members`.`id`
)
AND `international_contractors`.`company_id` = 7757616926151792;
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_23 | 1.00 | root | | funcs:count(1)->Column#85 |
| └─Apply_122 | 4.13 | root | | CARTESIAN semi join |
| ├─IndexJoin_31(Build) | 4.13 | root | | inner join, inner:TableReader_28, outer key:zenpayroll_evaluation_v3.international_contractors.member_id, inner key:zenpayroll_evaluation_v3.members.id, equal cond:eq(zenpayroll_evaluation_v3.international_contractors.member_id, zenpayroll_evaluation_v3.members.id) |
| │ ├─IndexLookUp_54(Build) | 4.13 | root | | |
| │ │ ├─IndexRangeScan_52(Build) | 4.13 | cop[tikv] | table:international_contractors, index:index_international_contractors_on_company_id(company_id) | range:[7757616926151792,7757616926151792], keep order:false |
| │ │ └─TableRowIDScan_53(Probe) | 4.13 | cop[tikv] | table:international_contractors | keep order:false |
| │ └─TableReader_28(Probe) | 4.13 | root | | data:TableRangeScan_27 |
| │ └─TableRangeScan_27 | 4.13 | cop[tikv] | table:members | range: decided by [zenpayroll_evaluation_v3.international_contractors.member_id], keep order:false |
| └─Limit_77(Probe) | 0.01 | root | | offset:0, count:1 |
| └─IndexJoin_98 | 0.01 | root | | inner join, inner:IndexLookUp_97, outer key:zenpayroll_evaluation_v3.members.id, inner key:zenpayroll_evaluation_v3.member_employments.member_id, equal cond:eq(zenpayroll_evaluation_v3.members.id, zenpayroll_evaluation_v3.member_employments.member_id) |
| ├─TableReader_120(Build) | 4.13 | root | | data:TableRangeScan_119 |
| │ └─TableRangeScan_119 | 4.13 | cop[tikv] | table:inner_members | range: decided by [eq(zenpayroll_evaluation_v3.members.id, zenpayroll_evaluation_v3.members.id)], keep order:false |
| └─IndexLookUp_97(Probe) | 0.01 | root | | |
| ├─Selection_95(Build) | 0.45 | cop[tikv] | | eq(zenpayroll_evaluation_v3.member_employments.member_id, zenpayroll_evaluation_v3.members.id), or(isnull(zenpayroll_evaluation_v3.member_employments.end_date), ge(zenpayroll_evaluation_v3.member_employments.end_date, 2026-01-05 00:00:00.000000)) |
| │ └─IndexRangeScan_93 | 4.31 | cop[tikv] | table:member_employments, index:idx_on_member_id_worker_type_start_date_end_date_120150c221(member_id, worker_type, start_date, end_date) | range: decided by [eq(zenpayroll_evaluation_v3.member_employments.member_id, zenpayroll_evaluation_v3.members.id) eq(zenpayroll_evaluation_v3.member_employments.worker_type, Contractor) le(zenpayroll_evaluation_v3.member_employments.start_date, 2026-01-05 00:00:00.000000)], keep order:false |
| └─Selection_96(Probe) | 0.01 | cop[tikv] | | ne(zenpayroll_evaluation_v3.member_employments.country_code, "US") |
| └─TableRowIDScan_94 | 0.45 | cop[tikv] | table:member_employments | keep order:false |
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+EXPLAIN SELECT COUNT(1)
FROM `international_contractors`
JOIN `members`
ON `members`.`id` = `international_contractors`.`member_id`
WHERE members.id IN (
SELECT DISTINCT
`member_employments`.`member_id`
FROM `member_employments`
WHERE (
`member_employments`.`start_date` <= '2026-01-05'
AND (
`member_employments`.`end_date` IS NULL
OR `member_employments`.`end_date` >= '2026-01-05'
)
)
AND (
`member_employments`.`worker_type` = 'Contractor'
AND `member_employments`.`country_code` != 'US'
)
)
AND `international_contractors`.`company_id` = 7757616926151792;
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_19 | 1.00 | root | | funcs:count(1)->Column#65 |
| └─IndexHashJoin_157 | 4.13 | root | | inner join, inner:HashAgg_153, outer key:zenpayroll_evaluation_v3.members.id, inner key:zenpayroll_evaluation_v3.member_employments.member_id, equal cond:eq(zenpayroll_evaluation_v3.members.id, zenpayroll_evaluation_v3.member_employments.member_id) |
| ├─IndexJoin_72(Build) | 4.13 | root | | inner join, inner:TableReader_69, outer key:zenpayroll_evaluation_v3.international_contractors.member_id, inner key:zenpayroll_evaluation_v3.members.id, equal cond:eq(zenpayroll_evaluation_v3.international_contractors.member_id, zenpayroll_evaluation_v3.members.id) |
| │ ├─IndexLookUp_95(Build) | 4.13 | root | | |
| │ │ ├─IndexRangeScan_93(Build) | 4.13 | cop[tikv] | table:international_contractors, index:index_international_contractors_on_company_id(company_id) | range:[7757616926151792,7757616926151792], keep order:false |
| │ │ └─TableRowIDScan_94(Probe) | 4.13 | cop[tikv] | table:international_contractors | keep order:false |
| │ └─TableReader_69(Probe) | 4.13 | root | | data:TableRangeScan_68 |
| │ └─TableRangeScan_68 | 4.13 | cop[tikv] | table:members | range: decided by [zenpayroll_evaluation_v3.international_contractors.member_id], keep order:false |
| └─HashAgg_153(Probe) | 4.31 | root | | group by:zenpayroll_evaluation_v3.member_employments.member_id, funcs:firstrow(zenpayroll_evaluation_v3.member_employments.member_id)->zenpayroll_evaluation_v3.member_employments.member_id |
| └─IndexLookUp_154 | 4.31 | root | | |
| ├─Selection_150(Build) | 4.31 | cop[tikv] | | or(isnull(zenpayroll_evaluation_v3.member_employments.end_date), ge(zenpayroll_evaluation_v3.member_employments.end_date, 2026-01-05 00:00:00.000000)) |
| │ └─IndexRangeScan_148 | 4.31 | cop[tikv] | table:member_employments, index:idx_on_member_id_worker_type_start_date_end_date_120150c221(member_id, worker_type, start_date, end_date) | range: decided by [eq(zenpayroll_evaluation_v3.member_employments.member_id, zenpayroll_evaluation_v3.members.id) eq(zenpayroll_evaluation_v3.member_employments.worker_type, Contractor) le(zenpayroll_evaluation_v3.member_employments.start_date, 2026-01-05 00:00:00.000000)], keep order:false |
| └─HashAgg_152(Probe) | 4.31 | cop[tikv] | | group by:zenpayroll_evaluation_v3.member_employments.member_id |
| └─Selection_151 | 4.13 | cop[tikv] | | ne(zenpayroll_evaluation_v3.member_employments.country_code, "US") |
| └─TableRowIDScan_149 | 4.31 | cop[tikv] | table:member_employments | keep order:false |
+------------------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+- The original
INquery results in a suboptimal execution plan due to optimizer limitations in handling this correlated structure with an unnecessary join. - Rewriting it as
EXISTSimproves the plan significantly. - However, the root cause is the redundant
JOIN membersinside the subquery.
After removing that join, theINversion can also produce an efficient execution plan comparable to theEXISTSrewrite.