Re: MySQL 5.6 update doubled IOs

[Øystein Grøvlen]

Hi Christian,

What happens if you before running this query do:

set optimizer_switch='mrr=off';

MRR will give overhead to your query since it will buffer several keys before doing table look-up. In your case, due to LIMIT 1 clause, you only need to look up the first key since, given the index that is used, it will be the one with the lowest ip_end and the only one that satisfy the ip_begin condition (assuming non-overlapping intervals). Because of the latter, you will not benefit from Index Condition Pushdown either, and might as well do:

set optimizer_switch='index_condition_pushdown=off':

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway