[Maria-discuss] Question about FORCE INDEX and records_in_range
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. 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. -- Mark Callaghan mdcallag@gmail.com
For simple single table selects when FORCE INDEX is used it already does what I want it to do and the call stack is. Maybe that is sufficient. #0 ha_innobase::records_in_range (this=0xafcce90, keynr=1, min_key=0x41776b40, max_key=0x41776b20) at handler/ha_innodb.cc:7791 #1 0x000000000073bcdc in check_quick_keys (param=0x41776f50, idx=0, key_tree=0xafcf3f0, min_key=0x417772c5 "", min_key_flag=0, min_keypart=0, max_key=0x417781c3 "", max_key_flag=0, max_keypart=0) at opt_range.cc:7690 #2 0x000000000073b753 in check_quick_keys (param=0x41776f50, idx=0, key_tree=0xafd2f10, min_key=0x417772c0 "", min_key_flag=0, min_keypart=-1, max_key=0x417781be "", max_key_flag=0, max_keypart=-1) at opt_range.cc:7596 #3 0x000000000073c009 in check_quick_select (param=0x41776f50, idx=0, tree=0xafd2f10, update_tbl_stats=true) at opt_range.cc:7465 #4 0x000000000073c389 in get_key_scans_params (param=0x41776f50, tree=0xafd53e0, index_read_must_be_used=false, update_tbl_stats=true, read_time=1.8051194539249147) at opt_range.cc:4865 #5 0x0000000000745fe0 in SQL_SELECT::test_quick_select (this=0xafd2d50, thd=0xafa3560, keys_to_use={map = 2}, prev_tables=0, limit=18446744073709551615, force_quick_range=false) at opt_range.cc:2372 #6 0x000000000069ab91 in get_quick_record_count (thd=0xafa3560, select=0xafd2d50, table=0xafcea40, keys=0xafd2a18, limit=18446744073709551615) at sql_select.cc:2604 #7 0x00000000006a0144 in make_join_statistics (join=0xafd8760, tables_arg=0xafcca60, conds=0xafd1b40, keyuse_array=0xafd9d48) at sql_select.cc:3036 #8 0x00000000006a2db2 in JOIN::optimize (this=0xafd8760) at sql_select.cc:1042 #9 0x00000000006a74e9 in mysql_select (thd=0xafa3560, rref_pointer_array=0xafa57b8, tables=0xafcca60, wild_num=1, fields=@0xafa56d0, conds=0xafd1b40, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xafd7f00, unit=0xafa5188, select_lex=0xafa55c0) at sql_select.cc:2543 #10 0x00000000006ad0b5 in handle_select (thd=0xafa3560, lex=0xafa50e8, result=0xafd7f00, setup_tables_done_option=0) at sql_select.cc:284 #11 0x0000000000610700 in execute_sqlcom_select (thd=0xafa3560, all_tables=0xafcca60, last_timer=0x4177bf08) at sql_parse.cc:5537 #12 0x0000000000611564 in mysql_execute_command (thd=0xafa3560, last_timer=0x4177bf08) at sql_parse.cc:2593 #13 0x000000000061a46e in mysql_parse (thd=0xafa3560, rawbuf=0xafcf480 "select * from foobar FORCE INDEX (x1) where j = 1 and k = 1", length=60, found_semicolon=0x4177bea0, last_timer=0x4177bf08) at sql_parse.cc:6470 #14 0x000000000061c76a in dispatch_command (command=COM_QUERY, thd=0xafa3560, packet=0xafc4541 "select * from foobar FORCE INDEX (x1) where j = 1 and k = 1", packet_length=60) at sql_parse.cc:1321 On Wed, Jan 19, 2011 at 4:15 PM, MARK CALLAGHAN <mdcallag@gmail.com> 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.
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.
-- Mark Callaghan mdcallag@gmail.com
-- Mark Callaghan mdcallag@gmail.com
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.
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@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
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
participants (3)
-
Igor Babaev -
Justin Swanhart -
MARK CALLAGHAN