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 <i...@askmonty.org> 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@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
