From 2775f80f7d287cb0ed478543bf135b9399f56d66 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 6 Apr 2010 00:16:45 +0400 Subject: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - First code (needs cleanup). --- sql/item_subselect.h | 21 +++++++++++++++++---- 1 file changed, 17 insertions(+), 4 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index e538f02d80a..5414c2b6bb9 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -52,9 +52,9 @@ public: public: /* unit of subquery */ st_select_lex_unit *unit; -protected: /* engine that perform execution of subselect (single select or union) */ subselect_engine *engine; +protected: /* old engine if engine was changed */ subselect_engine *old_engine; /* cache of used external tables */ @@ -139,6 +139,7 @@ public: bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void recalc_used_tables(st_select_lex *new_parent, bool after_pullout); + virtual int optimize(); virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; @@ -333,7 +334,9 @@ protected: all JOIN in UNION */ Item *expr; +public: Item_in_optimizer *optimizer; +protected: bool was_null; bool abort_on_null; public: @@ -379,6 +382,10 @@ public: }; enum_exec_method exec_method; + /* JTBM: temporary measure to tell JTBM predicates from SJ predicates */ + bool convert_to_semi_join; + double startup_cost; + bool *get_cond_guard(int i) { return pushed_cond_guards ? pushed_cond_guards + i : NULL; @@ -428,7 +435,7 @@ public: bool fix_fields(THD *thd, Item **ref); void fix_after_pullout(st_select_lex *new_parent, Item **ref); void update_used_tables(); - bool setup_engine(); + bool setup_engine(bool dont_switch_arena); bool init_left_expr_cache(); /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } @@ -502,6 +509,7 @@ public: THD * get_thd() { return thd; } virtual int prepare()= 0; virtual void fix_length_and_dec(Item_cache** row)= 0; + virtual int optimize() { DBUG_ASSERT(0); return 0; } /* Execute the engine @@ -734,7 +742,7 @@ inline bool Item_subselect::is_uncacheable() const class subselect_hash_sj_engine : public subselect_engine { -protected: +public: /* The table into which the subquery is materialized. */ TABLE *tmp_table; /* TRUE if the subquery was materialized into a temp table. */ @@ -746,14 +754,16 @@ protected: of subselect_single_select_engine::[prepare | cols]. */ subselect_single_select_engine *materialize_engine; +protected: /* The engine used to compute the IN predicate. */ subselect_engine *lookup_engine; /* QEP to execute the subquery and materialize its result into a temporary table. Created during the first call to exec(). */ +public: JOIN *materialize_join; - +protected: /* Keyparts of the only non-NULL composite index in a rowid merge. */ MY_BITMAP non_null_key_parts; /* Keyparts of the single column indexes with NULL, one keypart per index. */ @@ -766,7 +776,9 @@ protected: IN results because index lookups sometimes match values that are actually not equal to the search key in SQL terms. */ +public: Item_cond_and *semi_join_conds; +protected: /* Possible execution strategies that can be used to compute hash semi-join.*/ enum exec_strategy { UNDEFINED, @@ -806,6 +818,7 @@ public: bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ + int optimize(); int exec(); virtual void print(String *str, enum_query_type query_type); uint cols() -- cgit v1.2.1 From 30bac798066ced860d65bee8b3ef63d7b100e47d Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 7 Apr 2010 01:29:09 +0400 Subject: Always refer to materialized table as "SUBSELECT#%d" where %d is select number - for Item-based materialization, it was "materialized subselect" - for SJ-Materialization it was "subquery%d" --- sql/item_subselect.h | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 5414c2b6bb9..c26977982d5 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -814,7 +814,7 @@ public: } ~subselect_hash_sj_engine(); - bool init_permanent(List *tmp_columns); + bool init_permanent(List *tmp_columns, uint subquery_id); bool init_runtime(); void cleanup(); int prepare() { return 0; } /* Override virtual function in base class. */ -- cgit v1.2.1 From b2c57cedbfd0f8ce2b4f67021a2aca5dedf9b6c9 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 25 Apr 2010 12:23:52 +0400 Subject: Code cleanup in subquery optimizations --- sql/item_subselect.h | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index c26977982d5..bd1c11fa29e 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -382,8 +382,20 @@ public: }; enum_exec_method exec_method; - /* JTBM: temporary measure to tell JTBM predicates from SJ predicates */ + /* + JTBM: temporary measure to tell JTBM predicates from SJ predicates + psergey-jtbm-todo: can't we do without this? + - either remove it altogether + - or put into enum_exec_method + + We can't remove it altogether as it is used to classify contents in + join->sj_subselects. + */ bool convert_to_semi_join; + + /* + Cost to populate the temporary table (set on if-needed basis). + */ double startup_cost; bool *get_cond_guard(int i) -- cgit v1.2.1 From ed8aa9868e074e6489f3b71f585606b6eecc97c6 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 11 May 2010 11:53:40 +0400 Subject: - Make subselect_sj2 testcase more stable - Better comments --- sql/item_subselect.h | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index bd1c11fa29e..3c3d6cdbf00 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -46,6 +46,7 @@ public: < child_join->prepare < engine->prepare *ref= substitution; + substitution= NULL; < Item_subselect::fix_fields */ Item *substitution; @@ -390,6 +391,7 @@ public: We can't remove it altogether as it is used to classify contents in join->sj_subselects. + jtbm-todo: option 1: let sj_subselects list pairs. */ bool convert_to_semi_join; -- cgit v1.2.1 From 3f595889d35c81540eb14ef3c53105cb6c4db833 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 23 May 2010 23:13:18 +0400 Subject: Subqueries: Inside-out execution for non-semijoin materialized subqueries that are AND-parts of the WHERE - Code cleanup - Query plan change is due to s/ha_rows JOIN_TAB::read_time/double JOIN_TAB::read_time/ --- sql/item_subselect.h | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 3c3d6cdbf00..3e8ec9b174d 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -393,12 +393,12 @@ public: join->sj_subselects. jtbm-todo: option 1: let sj_subselects list pairs. */ - bool convert_to_semi_join; + bool is_flattenable_semijoin; /* Cost to populate the temporary table (set on if-needed basis). */ - double startup_cost; + //double startup_cost; bool *get_cond_guard(int i) { -- cgit v1.2.1 From e9bac8db82f54d715997cceff082452c5406d36e Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 11 Jun 2010 18:43:06 +0400 Subject: MWL#90, code cleanup - Remove garbage comments - fix a bug in join_tab_cmp --- sql/item_subselect.h | 9 +-------- 1 file changed, 1 insertion(+), 8 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 3e8ec9b174d..3454c8a2779 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -384,14 +384,7 @@ public: enum_exec_method exec_method; /* - JTBM: temporary measure to tell JTBM predicates from SJ predicates - psergey-jtbm-todo: can't we do without this? - - either remove it altogether - - or put into enum_exec_method - - We can't remove it altogether as it is used to classify contents in - join->sj_subselects. - jtbm-todo: option 1: let sj_subselects list pairs. + TRUE<=>this is a flattenable semi-join, false overwise. */ bool is_flattenable_semijoin; -- cgit v1.2.1 From 792c67ff618f7fa5edb39563b0ccf48b4a30db0f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 19 Mar 2011 12:27:08 +0300 Subject: MWL#90: Address review feedback part #1 --- sql/item_subselect.h | 5 ----- 1 file changed, 5 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 772196a4ea3..39780708e60 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -406,11 +406,6 @@ public: */ bool is_flattenable_semijoin; - /* - Cost to populate the temporary table (set on if-needed basis). - */ - //double startup_cost; - bool *get_cond_guard(int i) { return pushed_cond_guards ? pushed_cond_guards + i : NULL; -- cgit v1.2.1 From b77e3dc9f403cea4d8ccebf87267096f5194d31c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 22 Mar 2011 13:09:55 +0300 Subject: MWL#90: Address review feedback part #5 --- sql/item_subselect.h | 113 ++++++++++++++++++++++++--------------------------- 1 file changed, 54 insertions(+), 59 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 39780708e60..b3b4c717f38 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -36,6 +36,22 @@ class Item_subselect :public Item_result_field protected: /* thread handler, will be assigned in fix_fields only */ THD *thd; + /* old engine if engine was changed */ + subselect_engine *old_engine; + /* cache of used external tables */ + table_map used_tables_cache; + /* allowed number of columns (1 for single value subqueries) */ + uint max_columns; + /* where subquery is placed */ + enum_parsing_place parsing_place; + /* work with 'substitution' */ + bool have_to_be_excluded; + /* cache of constant state */ + bool const_item_cache; + + bool inside_first_fix_fields; + bool done_first_fix_fields; +public: /* Used inside Item_subselect::fix_fields() according to this scenario: > Item_subselect::fix_fields @@ -49,30 +65,12 @@ protected: substitution= NULL; < Item_subselect::fix_fields */ -public: Item *substitution; /* unit of subquery */ st_select_lex_unit *unit; Item *expr_cache; /* engine that perform execution of subselect (single select or union) */ subselect_engine *engine; -protected: - /* old engine if engine was changed */ - subselect_engine *old_engine; - /* cache of used external tables */ - table_map used_tables_cache; - /* allowed number of columns (1 for single value subqueries) */ - uint max_columns; - /* where subquery is placed */ - enum_parsing_place parsing_place; - /* work with 'substitution' */ - bool have_to_be_excluded; - /* cache of constant state */ - bool const_item_cache; - - bool inside_first_fix_fields; - bool done_first_fix_fields; -public: /* A reference from inside subquery predicate to somewhere outside of it */ class Ref_to_outside : public Sql_alloc { @@ -774,61 +772,26 @@ public: of subselect_single_select_engine::[prepare | cols]. */ subselect_single_select_engine *materialize_engine; -protected: - /* The engine used to compute the IN predicate. */ - subselect_engine *lookup_engine; /* QEP to execute the subquery and materialize its result into a temporary table. Created during the first call to exec(). */ -public: JOIN *materialize_join; -protected: - /* Keyparts of the only non-NULL composite index in a rowid merge. */ - MY_BITMAP non_null_key_parts; - /* Keyparts of the single column indexes with NULL, one keypart per index. */ - MY_BITMAP partial_match_key_parts; - uint count_partial_match_columns; - uint count_null_only_columns; /* A conjunction of all the equality condtions between all pairs of expressions that are arguments of an IN predicate. We need these to post-filter some IN results because index lookups sometimes match values that are actually not equal to the search key in SQL terms. - */ -public: + */ Item_cond_and *semi_join_conds; -protected: - /* Possible execution strategies that can be used to compute hash semi-join.*/ - enum exec_strategy { - UNDEFINED, - COMPLETE_MATCH, /* Use regular index lookups. */ - PARTIAL_MATCH, /* Use some partial matching strategy. */ - PARTIAL_MATCH_MERGE, /* Use partial matching through index merging. */ - PARTIAL_MATCH_SCAN, /* Use partial matching through table scan. */ - IMPOSSIBLE /* Subquery materialization is not applicable. */ - }; - /* The chosen execution strategy. Computed after materialization. */ - exec_strategy strategy; -protected: - exec_strategy get_strategy_using_schema(); - exec_strategy get_strategy_using_data(); - ulonglong rowid_merge_buff_size(bool has_non_null_key, - bool has_covering_null_row, - MY_BITMAP *partial_match_key_parts); - void choose_partial_match_strategy(bool has_non_null_key, - bool has_covering_null_row, - MY_BITMAP *partial_match_key_parts); - bool make_semi_join_conds(); - subselect_uniquesubquery_engine* make_unique_engine(); -public: subselect_hash_sj_engine(THD *thd, Item_subselect *in_predicate, subselect_single_select_engine *old_engine) - :subselect_engine(thd, in_predicate, NULL), tmp_table(NULL), - is_materialized(FALSE), materialize_engine(old_engine), lookup_engine(NULL), - materialize_join(NULL), count_partial_match_columns(0), - count_null_only_columns(0), semi_join_conds(NULL), strategy(UNDEFINED) + : subselect_engine(thd, in_predicate, NULL), + tmp_table(NULL), is_materialized(FALSE), materialize_engine(old_engine), + materialize_join(NULL), semi_join_conds(NULL), lookup_engine(NULL), + count_partial_match_columns(0), count_null_only_columns(0), + strategy(UNDEFINED) {} ~subselect_hash_sj_engine(); @@ -856,6 +819,38 @@ public: //=>base class bool change_result(Item_subselect *si, select_result_interceptor *result); bool no_tables();//=>base class + +protected: + /* The engine used to compute the IN predicate. */ + subselect_engine *lookup_engine; + /* Keyparts of the only non-NULL composite index in a rowid merge. */ + MY_BITMAP non_null_key_parts; + /* Keyparts of the single column indexes with NULL, one keypart per index. */ + MY_BITMAP partial_match_key_parts; + uint count_partial_match_columns; + uint count_null_only_columns; + /* Possible execution strategies that can be used to compute hash semi-join.*/ + enum exec_strategy { + UNDEFINED, + COMPLETE_MATCH, /* Use regular index lookups. */ + PARTIAL_MATCH, /* Use some partial matching strategy. */ + PARTIAL_MATCH_MERGE, /* Use partial matching through index merging. */ + PARTIAL_MATCH_SCAN, /* Use partial matching through table scan. */ + IMPOSSIBLE /* Subquery materialization is not applicable. */ + }; + /* The chosen execution strategy. Computed after materialization. */ + exec_strategy strategy; + exec_strategy get_strategy_using_schema(); + exec_strategy get_strategy_using_data(); + ulonglong rowid_merge_buff_size(bool has_non_null_key, + bool has_covering_null_row, + MY_BITMAP *partial_match_key_parts); + void choose_partial_match_strategy(bool has_non_null_key, + bool has_covering_null_row, + MY_BITMAP *partial_match_key_parts); + bool make_semi_join_conds(); + subselect_uniquesubquery_engine* make_unique_engine(); + }; -- cgit v1.2.1 From dc937fb53b33fc7c58612bf3f7ac1ab189393729 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 29 Mar 2011 14:33:59 +0400 Subject: MWL#90: Address review feedback part #16 --- sql/item_subselect.h | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'sql/item_subselect.h') diff --git a/sql/item_subselect.h b/sql/item_subselect.h index b3b4c717f38..2a35f5a8079 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -313,6 +313,8 @@ public: }; +TABLE_LIST * const NO_JOIN_NEST=(TABLE_LIST*)0x1; + /** Representation of IN subquery predicates of the form "left_expr IN (SELECT ...)". @@ -351,12 +353,10 @@ protected: all JOIN in UNION */ Item *expr; -public: - Item_in_optimizer *optimizer; -protected: bool was_null; bool abort_on_null; public: + Item_in_optimizer *optimizer; /* Used to trigger on/off conditions that were pushed down to subselect */ bool *pushed_cond_guards; @@ -365,7 +365,7 @@ public: /* Used by subquery optimizations to keep track about in which clause this subquery predicate is located: - (TABLE_LIST*) 1 - the predicate is an AND-part of the WHERE + NO_JOIN_NEST - the predicate is an AND-part of the WHERE join nest pointer - the predicate is an AND-part of ON expression of a join nest NULL - for all other locations @@ -377,7 +377,7 @@ public: - pointer to join nest if the subquery predicate is in the ON expression - (TABLE_LIST*)1 if the predicate is in the WHERE. */ - TABLE_LIST *expr_join_nest; + //TABLE_LIST *expr_join_nest; /* Types of left_expr and subquery's select list allow to perform subquery materialization. Currently, we set this to FALSE when it as well could @@ -420,7 +420,7 @@ public: Item_in_subselect(Item * left_expr, st_select_lex *select_lex); Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), - is_constant(FALSE), optimizer(0), abort_on_null(0), + is_constant(FALSE), abort_on_null(0), optimizer(0), pushed_cond_guards(NULL), exec_method(NOT_TRANSFORMED), upper_item(0) {} void cleanup(); -- cgit v1.2.1