List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:January 12 2010 11:39am
Subject:why is the default of --optimizer_search_depth 62?
View as plain text  
Hi!

>>>>> "Guilhem" == Guilhem Bichot <guilhem@stripped> writes:

Guilhem> Hello,
Guilhem> I see the default value for this variable is 62 (i.e. exhaustive search 
Guilhem> for the best query plan, as we allow maximum ~64 tables in a join). It's 
Guilhem> so in 5.0, 5.1, 6.0-codebase.

I think we only allow 61 tables in the join.

3 bits in the table map is reserved as markers:

#define MAX_TABLES	(sizeof(table_map)*8-3)	/* Max tables in join */
#define PARAM_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-3))
#define OUTER_REF_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-2))
#define RAND_TABLE_BIT	(((table_map) 1) << (sizeof(table_map)*8-1))

Guilhem> I remember Timour explaining that indeed beyond 7 tables, exhaustive 
Guilhem> search becomes very costly.

Guilhem> --optimizer-search-depth=0, documented as "make a reasonable choice", 
Guilhem> chooses min(number of tables, 7) (see determine_search_depth() in 
Guilhem> sql_select.cc).

Guilhem> Why is exhaustive (62) the default? I guess most users use the default 
Guilhem> (naturally), which is why choosing a good default is important...

Guilhem> Does anyone have an idea or some background about this default?

I think the default should be MAX_TABLES, not MAX_TABLES+1
(don't understand the +1)

For the answer about the default value, we have wait for Timour.

Regards,
Monty
Thread
why is the default of --optimizer_search_depth 62?Guilhem Bichot12 Jan
  • why is the default of --optimizer_search_depth 62?Michael Widenius12 Jan
  • Re: why is the default of --optimizer_search_depth 62?Timour Katchaounov12 Jan