maria-discuss team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

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



-- 
Mark Callaghan
mdcallag@xxxxxxxxx