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