maria-discuss team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

On 01/19/2011 04:15 PM, MARK CALLAGHAN wrote:
> 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.

Mark,
When you use the clause FORCE/USE INDEX (idx1,...,idxn) for a table only
idx1,...,idxn are considered for index access to the table.
Accordingly records_in_range() is called only for these indexes.

Regards,
Igor.

> 
> 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.
>