How Many Values Can MySQL IN Handle? 100K‑10M IDs and the Ultimate Solution

Problem Overview

An interview question asks how to retrieve order information for 100 000, 1 000 000 or even 10 000 000 user IDs using a single SQL statement. The naive answer—"just put all IDs in an IN clause"—fails for three reasons:

The raw IN list can exceed max_allowed_packet (default 4 MB), causing the query to be truncated.

Parsing such a long constant string forces MySQL to load the entire list into memory, increasing parse time from milliseconds to seconds.

The optimizer treats a huge IN list as low‑selectivity and switches to a full‑table scan (type=ALL), turning an indexed lookup into a costly scan.

Entry‑Level Pitfall: Direct IN

Example of the dangerous code:

String sql = "SELECT * FROM orders WHERE user_id IN (" + String.join(",", userIds) + ")";

Consequences:

SQL length > 1 MB (10 000 BIGINT values ≈ 800 KB plus the statement prefix).

Even if the packet limit is not hit, parsing takes seconds and the optimizer forces a full‑table scan.

Result: query time jumps from < 1 ms to several seconds, and the database CPU spikes.

Emergency Work‑Around: Batch IN + Client‑Side Merge

Split the ID list into smaller batches (e.g., 1 000 IDs per batch) and issue multiple queries, merging results in the application.

public List<Order> queryOrdersInBatches(List<Long> userIds, int batchSize) {
    List<Order> result = new ArrayList<>();
    for (int i = 0; i < userIds.size(); i += batchSize) {
        List<Long> batch = userIds.subList(i, Math.min(i + batchSize, userIds.size()));
        String placeholders = batch.stream().map(String::valueOf).collect(Collectors.joining(","));
        String sql = "SELECT order_id, user_id, amount, create_time FROM orders WHERE user_id IN (" + placeholders + ")";
        List<Order> batchResult = jdbcTemplate.query(sql, ORDER_ROW_MAPPER);
        result.addAll(batchResult);
    }
    return result;
}

Key notes:

Batch size 1 000 keeps the statement under the packet limit.

Each batch still suffers from optimizer degradation; total latency becomes batchCount × 200 ms (e.g., 100 × 200 ms = 20 s), which exceeds typical service timeouts.

High concurrency (e.g., 100 parallel threads) creates a “small‑packet storm” and drives DB CPU to >90 %.

Robust Solution: Temporary Table + JOIN

Load all IDs into a lightweight temporary table that has a primary key, then join it with the target table. This converts many small lookups into a single indexed join.

CREATE TEMPORARY TABLE tmp_user_ids (
    id BIGINT NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO tmp_user_ids VALUES (1), (2), (3), ..., (100000);

SELECT o.* FROM orders o JOIN tmp_user_ids t ON o.user_id = t.id;

Advantages:

Bypasses max_allowed_packet —the large ID set is stored as rows, not a giant string.

JOIN uses the primary‑key index, guaranteeing an index‑based plan (no full‑table scan).

Network round‑trips drop from ~100 to 2 (create‑temp + join).

Supports additional filters, ordering, and pagination.

Correct Implementation Details

Always define a PRIMARY KEY on the temporary table; otherwise the join degrades to a full scan.

Insert IDs in batches of 5 000–10 000 to avoid a single massive transaction that would lock the temp table.

Choose the storage engine: ENGINE=Memory for < 10 000 rows (fastest), ENGINE=InnoDB for larger sets (supports row‑level locking).

Explicitly DROP TEMPORARY TABLE in long‑lived connections to prevent memory accumulation.

Scaling to Hundreds of Millions: Sharding + Stream‑Merge

When the ID set reaches millions and QPS exceeds 10 k, a single MySQL instance becomes a bottleneck. The article recommends horizontal sharding (e.g., ShardingSphere) with parallel query routing and streaming result merge.

# ShardingSphere core config (8 shards)
sharding:
  tables:
    orders:
      actual-data-nodes: ds$->{0..7}.orders
      table-strategy:
        standard:
          sharding-column: user_id
          sharding-algorithm-name: mod-algorithm
  sharding-algorithms:
    mod-algorithm:
      type: MOD
      props:
        sharding-count: 8
props:
  max-connections-size-per-query: 32
  sql-with-limit-rewrite: true   # force stream merge
  streaming-merge-buffer-size: 512
  memory-merge-max-row-count: -1
  jdbc:
    result-set-type: FORWARD_ONLY
    fetch-size: 100

Key points:

The sharding key must be the same as the query column ( user_id) to avoid broadcast queries.

Each shard returns rows via a forward‑only ResultSet; the merge layer streams rows, limiting the in‑memory buffer to 512 rows.

Setting max-result-row-count-per-query prevents any single shard from returning an overwhelming result set.

Combine with circuit‑breaker (Sentinel/Hystrix) to protect the system if a shard becomes unavailable.

Common Pitfalls in Sharding

Querying by a non‑sharding column (e.g., order_id) forces a broadcast to all shards, leading to massive full‑table scans and OOM in the merge node.

Insufficient buffer settings cause the merge node to load all results into memory, crashing the JVM.

Decision Tree & Best‑Practice Checklist

Always run EXPLAIN and ensure the plan does not contain type=ALL.

Set a query timeout (e.g., 500 ms) to protect connection pools.

Enable circuit‑breaker and rate‑limit high‑QPS paths.

Perform load testing before production rollout.

Recommended solution based on data volume and QPS:

Data < 1 M, QPS < 1 k: Temporary table + JOIN.

Data > 10 M, QPS > 10 k: Sharding + stream‑merge.

No table‑creation permission or low‑frequency scripts: Batch IN with forced index hint.

Interview Answer Template

Structure the answer as Problem → Evolution → Final Solution :

State the three core risks of a massive IN clause (packet limit, optimizer fallback, resource explosion).

Present the progression: simple batch IN → temporary table + JOIN → sharding with streaming merge.

Conclude with the decision tree that matches the business scenario.

This demonstrates both depth of technical knowledge and architectural thinking.