maria-discuss team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

On 01/27/2011 01:27 PM, Justin Swanhart wrote:
> 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?

Hi,

USE INDEX literally means that the optimizer MUST take into account ONLY
the indexes from the USE INDEX list when looking for the execution plan.
FORCE INDEX additionally says that any full table scan should be
considered as very expensive.

Regards,
Igor.

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