WL#704: MySQL loosy key scan (improved range optimization)

We need to make MySQL to scan index more intelegent way.

Ie in case we have   key_part1>A and key_part2=B  MySQL will do complete range
scan for key_part1>A which is not what you would want especially if key_part1 is
just a bit selective.

Similar case is just key_part2=B  - in case key_part1 is not very selective it
is  better to do scan key_part2=B for all values from key_part1 rather than do
complete table scan.

This is known as Loosy key scan in Oracle terminology.


Some notes from Monty about particular cases which one may with to start with:

The main problem here are:
- Recognise the above case as opt_range.cc will not notice this.
- Make a general case of this that can also handle things of type:
  WHERE key_first_part > const AND key_second_part BETWEEN const1 and const2
- How to make a handler interface for this.
  Far from trivial for a general case.

Peter>  - Shall I add it to worklog ?

Please do.
Estimated time to do:  + 100 hours (no kidding)

A similar, easer optimization to do is to provide an handler function:
- file->continue_ scanning_at_next_key_value(key_length_to_compare)
This would enable opt_range to do faster scan where there is a lot of
keys with the same prefix.

The intersting thing is that it's quite easy to do a general solution
with the current code for:

key_first_part >= const AND key_second_part = const

than for

key_first_part > const AND key_second_part = const

(And yes, it's far from trivial to this automaticly)