From 5ab66ffddc150e981c35ddf1e7ef77e81aa52e80 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 16 Jul 2010 13:52:02 +0300 Subject: MWL#89: Cost-based choice between Materialization and IN->EXISTS transformation 1. Changed the lazy optimization for subqueries that can be materialized into bottom-up optimization during the optimization of the main query. The main change is implemented by the method Item_in_subselect::setup_engine. All other changes were required to correct problems resulting from changing the order of optimization. Most of these problems followed the same pattern - there are some shared structures between a subquery and its parent query. Depending on which one is optimized first (parent or child query), these shared strucutres may get different values, thus resulting in an inconsistent query plan. 2. Changed the code-generation for subquery materialization to be performed in runtime memory for each (re)execution, instead of in statement memory (once per prepared statement). - Item_in_subselect::setup_engine() no longer creates materialization related objects in statement memory. - Merged subselect_hash_sj_engine::init_permanent and subselect_hash_sj_engine::init_runtime into subselect_hash_sj_engine::init, which is called for each (re)execution. - Fixed deletion of the temp table accordingly. mysql-test/r/subselect_mat.result: Adjusted changed EXPLAIN because of earlier optimization of subqueries. --- sql/sql_class.h | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) (limited to 'sql/sql_class.h') diff --git a/sql/sql_class.h b/sql/sql_class.h index 6ef330e8ee6..9784fe01536 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2852,6 +2852,8 @@ public: uint convert_blob_length; CHARSET_INFO *table_charset; bool schema_table; + /* TRUE if the temp table is created for subquery materialization. */ + bool materialized_subquery; /* True if GROUP BY and its aggregate functions are already computed by a table access method (e.g. by loose index scan). In this case @@ -2875,8 +2877,8 @@ public: TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), - schema_table(0), precomputed_group_by(0), force_copy_fields(0), - bit_fields_as_long(0), skip_create_table(0) + schema_table(0), materialized_subquery(0), precomputed_group_by(0), + force_copy_fields(0), bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() { @@ -2905,6 +2907,7 @@ public: bool send_data(List &items); bool send_eof(); bool flush(); + TMP_TABLE_PARAM *get_tmp_table_param() { return &tmp_table_param; } virtual bool create_result_table(THD *thd, List *column_types, bool is_distinct, ulonglong options, @@ -2969,7 +2972,7 @@ protected: ha_rows count_rows; public: - select_materialize_with_stats() {} + select_materialize_with_stats() { tmp_table_param.init(); } virtual bool create_result_table(THD *thd, List *column_types, bool is_distinct, ulonglong options, const char *alias, bool bit_fields_as_long); -- cgit v1.2.1