Comment 2 for bug 711648

I don't see anything wrong here.

Attempt#1 and #2 are OK.

ANALYZE command updates index cardinalities. After that, the optimizer starts to prefer a prefix of PRIMARY key over i_l_orderkey index. This is ok, too (even if it wasn't: this would have no relation to mrr_sort_keys switch/code).

optimizer_switch='mrr_sort_keys=off' setting disables key sorting functionality. DS-MRR's operation over a clustered primary key is defined as
 - sort the keys
 - use sorted keys array to make lookups in key order (note that lookups produce full result records here, there is no separate rnd_pos() step).

that is, if sorting is disabled, then DS-MRR has nothing to do. handler->multi_range_read_info() function will indicate that to BKA code by setting HA_MRR_USE_DEFAULT_IMPL. when BKA code sees this flag, it disables use of join buffering.

I think everything is operating as-designed here.