How to efficiently use IN queries-PolarDB(PolarDB)-阿里云帮助中心

As your dataset grows, IN queries on partitioned tables can degrade quickly — more values in the IN clause means more shards to scan, which increases response time. PolarDB-X addresses this with dynamic partition pruning (available in V5.4.8-16069335 and later), which eliminates redundant values from each physical query before it reaches ApsaraDB RDS for MySQL. This article explains how pruning works and how to size your IN clause for low latency and high throughput.

How dynamic partition pruning works

In PolarDB-X, an IN query on a partition key is routed to the shards that hold the matching values. Consider an e-commerce ORDER table partitioned by ORDER_ID. A buyer with two orders triggers a query routed to at most two shards:

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

As the order count grows, more shards may be scanned — up to all shards if each value lands on a different one. The following figure shows how response time increases as the number of IN-clause values and the number of scanned shards increase.

Before pruning (versions earlier than V5.4.8-16069335): every physical query sent to ApsaraDB RDS for MySQL carries the full IN clause, regardless of how many values the shard actually holds. For a table with 128 shards and 128 values, each physical query contains 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);

After pruning (V5.4.8-16069335 and later): PolarDB-X calculates which shard each value belongs to at the compute layer, then prunes the IN clause so that each physical query contains only the values stored in 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 the SQL execution workload on each shard, increasing throughput and decreasing response time.

Single-node parallel execution

PolarDB-X also runs IN queries across shards in parallel using single-node parallel execution. The degree of parallelism (DOP) equals the number of cores on the node. On a 16-core node, the default DOP is 16. If the IN-clause values are distributed across 32 shards, the query completes in 2 batches.

Tune the number of IN-clause values

Guideline 1: Keep the value count well below the shard count

If the number of IN-clause values exceeds the number of shards, the query is routed to all shards on every execution. Keeping the count significantly smaller than the shard count ensures that not all shards are scanned and response time stays predictable.

Guideline 2: Prevent unbounded growth from business data

A query with 10 values today may have 10,000 values a year from now as your business grows. Design your query patterns so the number of IN-clause values does not grow in proportion with your data volume. If needed, paginate or batch lookups instead of passing an ever-growing list.

Guideline 3: Target 8–32 values for optimal performance

When the value count is between 8 and 32, the DOP is close to the default DOP of the single-node parallel execution feature (one DOP per core). This range delivers the best balance of response time and throughput.

With this range, PolarDB-X scales linearly as you add nodes. A 16-core node handles 10,000 concurrent IN queries. After adding a second 16-core node, the cluster handles 20,000 concurrent IN queries.

Comparison test

The following test shows the effect of dynamic partition pruning on response time and throughput.

Test environment:

Parameter	Value
Nodes	2
Node spec	16 cores, 64 GB memory
Shards	64
Data per shard	Millions of records

Response time with pruning enabled

The following figure shows response time changes as the number of concurrent queries and IN-clause values increase, with dynamic partition pruning enabled.

Throughput with pruning enabled

The following figure shows throughput changes as the number of concurrent queries and IN-clause values increase, with dynamic partition pruning enabled.

Response time with pruning disabled

The following figure shows response time changes as the number of concurrent queries and IN-clause values increase, with dynamic partition pruning disabled.

Throughput with pruning disabled

The following figure shows throughput changes as the number of concurrent queries and IN-clause values increase, with dynamic partition pruning disabled.

Conclusions

Keep IN-clause values between 8 and 32. This range maintains low response time and high throughput because the DOP is close to the default DOP of the single-node parallel execution feature, which equals the number of node cores.
Upgrade to PolarDB-X V5.4.8 or later to enable dynamic partition pruning. With pruning enabled, response time for IN queries decreases and throughput increases compared to earlier versions.