Description

best_access_path() function has record_count parameter which specifies how many times the access method will be invoked.

However for eq_ref access, best_access_path() calls prev_record_reads() to determine the "effective" number of eq_ref accesses that will be done.

The return value of prev_record_reads() can be very different from record_count (much less), so this can have a dramatic effect on the query plan choice.

Historically, the code in prev_record_reads() seems to have tried to account for two effects:

1. eq_ref lookup cache (as described below)
2. A guess that it is cheaper when one makes lookup with a few repeating key values (as opposed to hitting totally different key values each time)

but the implementation was incorrect for either/both.

(One can observe that these two are not orthogonal. If one can take advantage of eq_ref lookup cache, this means we will not hit a totally different value each time)

What is fixed

This MDEV is about making prev_record_reads() to:

• Correctly account for eq_ref lookup cache use.
• Otherwise, do not try to take into account that we might be reading the same values over and over.

The rationale for for #2 is that we assume most key reads come from OS/disk cache. In this case, reading the same value multiple times has nearly the same speed as reading a different value each time.

Eq_ref lookup cache

Check the code in join_read_key2().
See the call to cmp_buffer_with_ref().
The logic here is that "if we're about to make a key lookup using the same value as the last time, skip the lookup".