Determine the number of values in the IN clause of a query

When an IN query targets a partition key in PolarDB-X 1.0, the number of values in the IN clause directly determines how many shards are scanned — and therefore how fast the query completes. This topic explains how dynamic partition pruning works, how to verify that it is active, and how to choose the right number of values to keep response time low and throughput high.

How partition routing works

In a sharded PolarDB-X 1.0 table, each row is stored in a shard based on the value of its partition key. When a query uses an IN clause on the partition key, PolarDB-X 1.0 routes the query only to the shards that contain the matching values.

Consider an e-commerce ORDER table sharded by ORDER_ID across 128 shards. A buyer querying two orders generates this SQL:

SELECT * FROM ORDER WHERE ORDER_ID IN (id1, id2)

This query is routed to at most 2 shards — one per value. As the number of orders grows, so does the IN clause, potentially fanning out to all 128 shards and degrading performance.

Dynamic partition pruning

PolarDB-X 1.0 V5.4.8-16069335 and later optimize IN queries through dynamic partition pruning. At the compute layer, PolarDB-X 1.0 groups IN values by the shard they belong to before dispatching physical SQL statements. Each physical statement sent to ApsaraDB RDS for MySQL contains only the values stored in that specific shard.

Before V5.4.8-16069335 (no pruning): querying 128 values across 128 shards generates 128 physical statements, each carrying all 128 values:

SELECT * FROM ORDER WHERE ORDER_ID_1 IN (id1, id2, id3 ... id128);
SELECT * FROM ORDER WHERE ORDER_ID_2 IN (id1, id2, id3 ... id128);
SELECT * FROM ORDER WHERE ORDER_ID_3 IN (id1, id2, id3 ... id128);
-- ...
SELECT * FROM ORDER WHERE ORDER_ID_128 IN (id1, id2, id3 ... id128);

V5.4.8-16069335 and later (with pruning): each physical statement carries only the values for that shard:

SELECT * FROM ORDER WHERE ORDER_ID_1 IN (id1);
SELECT * FROM ORDER WHERE ORDER_ID_2 IN (id2, id12);
SELECT * FROM ORDER WHERE ORDER_ID_3 IN (id3, id4, id5);
-- ...
SELECT * FROM ORDER WHERE ORDER_ID_32 IN (id100 ... id128);

This reduces both the per-statement payload and the total work performed by ApsaraDB RDS for MySQL.

Single-node parallel execution

PolarDB-X 1.0 runs shard queries in parallel using single-node parallel execution. The default degree of parallelism (DOP) equals the number of CPU cores on the node.

With a 16-core node and an IN query spanning 32 shards, the DOP is 16 — so all 32 shard queries complete in 2 parallel batches.

Recommended IN clause size

Keep the number of IN values within a range that avoids scanning all shards on every query:

Stay well below the total shard count. If every query fans out to all shards, you lose the benefit of sharding.
Avoid letting the count grow unbounded. Business growth should not silently degrade query performance.
Target 8–32 values. In this range, the number of shards touched stays close to the default DOP, letting single-node parallel execution handle the load efficiently.

At 8–32 values, the IN query maps to a number of shards close to the node's core count. The parallel executor can process all shards in one or two batches, keeping both latency and throughput predictable.

Update to PolarDB-X 1.0 V5.4.8 or later to enable dynamic partition pruning. Without it, each physical SQL statement carries all IN values regardless of the target shard.

Linear scalability

When IN clause sizes stay within the recommended range, concurrent IN query throughput scales linearly with additional nodes:

One 16-core node: ~10,000 concurrent IN queries
Two 16-core nodes: ~20,000 concurrent IN queries

Comparison test

The following test results show how response time and throughput change as the number of IN values and concurrent queries increase. The test environment uses two nodes (16 cores, 64 GB memory each) and a table with 64 shards, each containing millions of rows.

Dynamic partition pruning enabled (V5.4.8-16069335 and later)

Response time vs. concurrent queries and IN clause size:
Throughput vs. concurrent queries and IN clause size:

Dynamic partition pruning disabled (before V5.4.8-16069335)

Response time vs. concurrent queries and IN clause size:
Throughput vs. concurrent queries and IN clause size:

Key conclusions from the test:

8–32 values consistently delivers the lowest response time and highest throughput. In this range, the number of shards touched is close to the default DOP, making full use of single-node parallel execution.
Enabling dynamic partition pruning (V5.4.8-16069335 and later) significantly reduces response time and increases throughput for IN queries at all concurrency levels.