The problem seems to be the mismatch between range and index_merge costs:
Range access costs - are calculated in handler::multi_range_read_info_const(), the formula is roughly
handler::read_time() + #rows / TIME_FOR_COMPARE
index_merge costs: - are calculated in opt_range.cc. - for a 2-way intersection over a MyISAM table the formula is roughly:
handler->keyread_time(index1, ...) + handler->keyread_time(index2, ...) + get_sweep_read_cost(...)
note that the formula only includes index costs, #rows / TIME_FOR_COMPARE was not added.
The problem seems to be the mismatch between range and index_merge costs:
Range access costs :multi_ range_read_ info_const( ), the formula is roughly
- are calculated in handler:
handler: :read_time( ) + #rows / TIME_FOR_COMPARE
index_merge costs:
- are calculated in opt_range.cc.
- for a 2-way intersection over a MyISAM table the formula is roughly:
handler- >keyread_ time(index1, ...) + handler- >keyread_ time(index2, ...) + get_sweep_ read_cost( ...)
note that the formula only includes index costs, #rows / TIME_FOR_COMPARE was not added.