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