January 2010 - developers - lists.mariadb.org

[Maria-developers] Progress (by Psergey): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:25)=-=- As of today, there is code that - collects outside references - creates a temporary table with index that would allow for fast lookups. there is no code to - fill the temporary table - make lookups into it Reported zero hours worked. Estimate unchanged. -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Progress (by Psergey): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:25)=-=- As of today, there is code that - collects outside references - creates a temporary table with index that would allow for fast lookups. there is no code to - fill the temporary table - make lookups into it Reported zero hours worked. Estimate unchanged. -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:23)=-=- Title modified. --- /tmp/wklog.18.old.24804 2010-01-11 13:23:04.000000000 +0200 +++ /tmp/wklog.18.new.24804 2010-01-11 13:23:04.000000000 +0200 @@ -1 +1 @@ -subquery optimizations: Use cache for correlated non-semijoin subqueries +subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) -=-=(Psergey - Mon, 11 Jan 2010, 13:22)=-=- High Level Description modified. --- /tmp/wklog.18.old.24759 2010-01-11 11:22:35.000000000 +0000 +++ /tmp/wklog.18.new.24759 2010-01-11 11:22:35.000000000 +0000 @@ -1,3 +1,5 @@ +(( This WL entry is superseded by MWL#66 )) + Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: (( This WL entry is superseded by MWL#66 )) Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:23)=-=- Title modified. --- /tmp/wklog.18.old.24804 2010-01-11 13:23:04.000000000 +0200 +++ /tmp/wklog.18.new.24804 2010-01-11 13:23:04.000000000 +0200 @@ -1 +1 @@ -subquery optimizations: Use cache for correlated non-semijoin subqueries +subquery optimizations: Use cache for correlated non-semijoin subqueries (superseded by MWL#66) -=-=(Psergey - Mon, 11 Jan 2010, 13:22)=-=- High Level Description modified. --- /tmp/wklog.18.old.24759 2010-01-11 11:22:35.000000000 +0000 +++ /tmp/wklog.18.new.24759 2010-01-11 11:22:35.000000000 +0000 @@ -1,3 +1,5 @@ +(( This WL entry is superseded by MWL#66 )) + Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: (( This WL entry is superseded by MWL#66 )) Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): subquery optimizations: Use cache for correlated non-semijoin subqueries (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:22)=-=- High Level Description modified. --- /tmp/wklog.18.old.24759 2010-01-11 11:22:35.000000000 +0000 +++ /tmp/wklog.18.new.24759 2010-01-11 11:22:35.000000000 +0000 @@ -1,3 +1,5 @@ +(( This WL entry is superseded by MWL#66 )) + Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: (( This WL entry is superseded by MWL#66 )) Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): subquery optimizations: Use cache for correlated non-semijoin subqueries (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 11 Jan 2010, 13:22)=-=- High Level Description modified. --- /tmp/wklog.18.old.24759 2010-01-11 11:22:35.000000000 +0000 +++ /tmp/wklog.18.new.24759 2010-01-11 11:22:35.000000000 +0000 @@ -1,3 +1,5 @@ +(( This WL entry is superseded by MWL#66 )) + Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: (( This WL entry is superseded by MWL#66 )) Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): subquery optimizations: Use cache for correlated non-semijoin subqueries (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Guest): subquery optimizations: Use cache for correlated non-semijoin subqueries (18)
by worklog-noreply＠askmonty.org 11 Jan '10

11 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: subquery optimizations: Use cache for correlated non-semijoin subqueries CREATION DATE..: Mon, 11 May 2009, 19:03 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 18 (http://askmonty.org/worklog/?tid=18) VERSION........: Server-9.x STATUS.........: Cancelled PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Mon, 11 Jan 2010, 13:21)=-=- Status updated. --- /tmp/wklog.18.old.24677 2010-01-11 13:21:24.000000000 +0200 +++ /tmp/wklog.18.new.24677 2010-01-11 13:21:24.000000000 +0200 @@ -1 +1 @@ -Un-Assigned +Cancelled -=-=(Psergey - Mon, 11 May 2009, 19:05)=-=- High-Level Specification modified. --- /tmp/wklog.18.old.1385 2009-05-11 19:05:55.000000000 +0300 +++ /tmp/wklog.18.new.1385 2009-05-11 19:05:55.000000000 +0300 @@ -1 +1,12 @@ +Not a spec but some considerations: +* eq_ref access has one-element "lookup cache" + +* Materialization optimization (WL#1110) also has lookup cache, see +subselect_hash_semi_join_engine::test_if_l_operand_changed. + +The tricky parts are: + +1. Assemble a list of correlation references + +2. Determine cache size. DESCRIPTION: Suppose there is a subquery that is correlated, and is not in the WHERE clause e.g. SELECT ..., (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; or SELECT ..., left_expr IN (SELECT ... FROM t2 WHERE t2.col=t1.col) FROM t1; In this case, the only strategy is to re-evaluate the predicate for every record combination of the outer select. This can be improved if we catch all correlation references and implement a lookup cache: (left_expr, outer_ref_value1, ... outer_ref_valueN) -> predicate_value it's hard to predict what will be the optimal size of the cache but one-element cache will most certainly be worth it. HIGH-LEVEL SPECIFICATION: Not a spec but some considerations: * eq_ref access has one-element "lookup cache" * Materialization optimization (WL#1110) also has lookup cache, see subselect_hash_semi_join_engine::test_if_l_operand_changed. The tricky parts are: 1. Assemble a list of correlation references 2. Determine cache size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Solaris 10 Buildbot Suggestions
by Adam M. Dutko 11 Jan '10

11 Jan '10

I've posted a brief list of what I did (from what I remember) to setup a Maria DB buildbot on a Solaris 10 (SPARC) system. You can find it at http://littlehat.homelinux.org/tuts/MariaDB/buildbot/README-SOL10-SPARC . Hopefully it helps others in the future. Maybe we can post part of it at http://askmonty.org/wiki/index.php/Buildbot or at least a link? -Adam

2 1

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2791: Merge MariaDB 5.1.41-rc release into main, bumping configure.in version number.
by noreply＠launchpad.net 09 Jan '10

09 Jan '10

Merge authors: Kristian Nielsen (knielsen) Michael Widenius (monty) ------------------------------------------------------------ revno: 2791 [merge] committer: knielsen(a)knielsen-hq.org branch nick: mariadb-5.1 timestamp: Sat 2010-01-09 11:45:27 +0100 message: Merge MariaDB 5.1.41-rc release into main, bumping configure.in version number. added: BUILD/compile-bintar BUILD/util.sh modified: BUILD/Makefile.am BUILD/SETUP.sh BUILD/compile-solaris-amd64-debug-forte* BUILD/compile-solaris-x86-32* BUILD/compile-solaris-x86-32-debug* BUILD/compile-solaris-x86-32-debug-forte* BUILD/compile-solaris-x86-forte-32* configure.in extra/libevent/evbuffer.c extra/libevent/select.c mysql-test/mysql-test-run.pl mysql-test/suite/pbxt/r/lock_multi.result mysql-test/suite/pbxt/t/lock_multi.test mysys/my_sync.c sql/ha_ndbcluster.cc storage/archive/azlib.h storage/maria/ma_blockrec.c storage/maria/ma_loghandler.c storage/maria/ma_test3.c storage/myisam/mi_test3.c storage/pbxt/src/ha_pbxt.cc storage/pbxt/src/restart_xt.cc storage/pbxt/src/thread_xt.cc storage/pbxt/src/thread_xt.h storage/xtradb/srv/srv0srv.c support-files/compiler_warnings.supp -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0