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:
- eq_ref lookup cache (as described below)
- 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".
|