Although 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:
CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1
With ~2M rows
select count(*) from joinit ; +----------+ | count(*) | +----------+ | 2097152 | +----------+
With the default value of range_optimizer_max_mem_size = 8388608, the …[Read more]