Re: MySQL 5.6 update doubled IOs

[Øystein Grøvlen]

Hi Rick,

MRR is only beneficial if you need to follow several references from the index into the base table. This may be the case even if LIMIT 1 is specified. E.g., if there are extra conditons that needs to be evaluated so that the matching rows are few and far apart, or if you first need to access and sort many rows before picking the "top" one.

In the case discussed in this thread, the interesting row was always the first row that was accessed. Hence, MRR would not be beneficial in this case. The optimizer could definitely do a better job here, but currently LIMIT clauses are not taken into account when deciding whether to use MRR or not.

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