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
