maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

USE INDEX is a hint, and MySQL can choose a different index.  How does
it do this if records_in_range isn't called for other indexes?

--Justin

On Wed, Jan 19, 2011 at 10:30 PM, Igor Babaev <igor@xxxxxxxxxxxx> wrote:
> 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.
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>