We suspect that our servers do too much extra disk IO in
ha_innobase::records_in_range to determine selectivity for indexes
that don't get used in a query. For example when there are multiple
indexes that might be used, I assume that the MySQL optimizer calls
records_in_range for each and I know that InnoDB does two index
lookups per call to find the leaf blocks for the start and stop
predicates of the index scan. For the index that is used on the query
any disk IO done in records_in_range isn't wasted. We can think of it
as prefetch. But for the other indexes that disk IO is likely to be
wasted.
I rarely touch or even read optimizer code in MySQL. How difficult
would it be to have an option to either not call records_in_range when
a hint is used (such as FORCE INDEX or REALLY FORCE INDEX)? The
alternative is to figure out in records_in_range when the special hint
has been used for an index other than the one for which
records_in_range was called and return a large value without doing
index lookups for all but the hinted index.
--
Mark Callaghan
mdcallag(a)gmail.com
