Low Value in range_optimizer_max_mem_size Might Cause Full Table Scan

range_optimizer_max_mem_size Might Cause Full Table ScanAlthough how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this.

What problem does this variable cause if it is not properly sized? Let’s find out with an example!

Given the following table definition:

With ~2M rows

With the default value of  range_optimizer_max_mem_size = 8388608, the following query on PK executes very fast!

But what if we decrease range_optimizer_max_mem_size to let’s say, 500?

Woooh! Query execution time increased from 0.00 seconds to 0.82 seconds! What might be happening? Let’s check the explain and handlers:

With range_optimizer_max_mem_size = 8388608: