3219
MySQLWARNINGNotableOptimizerHIGH confidence
Capacity exceeded in range optimizer
Production Risk
Medium — query may use a full table scan instead of optimal index range.
Why it happens
- 1The range optimizer exceeded its memory budget set by range_optimizer_max_mem_size.
- 2Query has too many range conditions for the optimizer to fully analyze.
How to reproduce
trigger — this will error
trigger — this will error
SELECT * FROM t1 WHERE id IN (1,2,3,...,10000);
expected output
Warning 3219: Capacity exceeded in the range optimizer — switching to full scan.
Fix 1
Increase range_optimizer_max_mem_size
Increase range_optimizer_max_mem_size
SET SESSION range_optimizer_max_mem_size = 8388608;
Why this works
Allows the optimizer to analyze more range conditions before falling back.
Fix 2
Reduce the number of IN() values
Reduce the number of IN() values
-- Use a temporary table or JOIN instead of large IN() lists.
Why this works
Fewer predicates reduce optimizer memory usage.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3219 ER_CAPACITY_EXCEEDED_IN_RANGE_OPTIMIZER
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev