From: Michael Widenius Date: January 12 2010 11:39am Subject: why is the default of --optimizer_search_depth 62? List-Archive: http://lists.mysql.com/internals/37634 Message-Id: <19276.24410.488307.758448@narttu.askmonty.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Guilhem" == Guilhem Bichot 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