- developers - lists.mariadb.org

[Maria-developers] Updated (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 25 May '10

25 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 24 May 2010, 20:59)=-=- High-Level Specification modified. --- /tmp/wklog.119.old.25116 2010-05-24 20:59:40.000000000 +0000 +++ /tmp/wklog.119.new.25116 2010-05-24 20:59:40.000000000 +0000 @@ -1 +1,113 @@ +<contents> +HLS +1. Problems to be addressed in this WL +2. Pushdown of conditions into non-mergeable VIEWs +2.1 A note about VIEWs with ORDER BY ... LIMIT +2.2 What condition can be pushed +3. Pushdown from HAVING into WHERE +4. When to do the pushdown +5. Other things to take care of + + +</contents> + +1. Problems to be addressed in this WL +====================================== +The problem actually consists of two parts: +1. Condition on VIEW columns are not pushed down into VIEWs. +2. Even if conditions were pushed, they would have been put into VIEW's +HAVING clause, which would not give enough of speedup. In order to get a +real speedup, the optimizer must be able to move relevant part of HAVING +into WHERE (and then use it for further optimizations) in order to provide +the desired speedup. Note that HAVING->WHERE condition move is orthogonal +to VIEW processing. + +2. Pushdown of conditions into non-mergeable VIEWs +================================================== +We can push a condition into non-mergeable VIEW when VIEW's top-most operation +is selection (i.e., filtering). This is true, for example, when the VIEW is +defined as + + SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] + +and not true when the VIEW is defined as + + SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n + +Generalizing the above, we arrive at the following rule: + + For non-mergeable VIEWs, + - pushdown must not be done if VIEW uses ORDER BY .. LIMIT + - when pushdown is done, the pushed condition should be added to the WHERE + clause. + +Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as +top operation. + +(TODO: what about SELECT DISTINCT?) +(TODO: pushdown down into IN subqueries?) + +2.1 A note about VIEWs with ORDER BY ... LIMIT +---------------------------------------------- +Although it is not possible to push a condition below the ORDER BY ... LIMIT +operation, there is still some benefit from checking the condition early as +that would allow to avoid writing non-matching rows into temporary table. + +We could do that if we introduced a post-ORDERBY selection operation. That +operation would also allow to support ORDER BY ... LIMIT inside subqueries +(we don't currently support those because default subquery strategy, +IN->EXISTS rewrite, also needs to push a condition into subquery). + +2.2 What condition can be pushed +-------------------------------- +Assuming simplify_joins() operation has done normalization: +* If the VIEW is in top-level join list, or inside a semi-join that's in + top-level join list, we can push parts of WHERE condition. +* If the VIEW is inside an outer join, we can push it's ON expression. + +We can reuse make_cond_for_index()/make_remainder_cond() code to extract part +of condition that can be pushed, and the remainder, respectively. + +Pushability criteria for an atomic (i.e. not AND/OR) condition is that + + the condition only uses VIEW's fields. + +(TODO: what about fields of const tables? Do we have const tables already +retrived by the time VIEW is materialized? If yes, we could push down +expressions that refer to const tables, too) + +3. Pushdown from HAVING into WHERE +================================== +The idea is: + + Parts of HAVING that refer to columns/expressions we're doing GROUP BY on + can be put into WHERE. + +(TODO: do we need to handle case of grouping over expressions?) + +(TODO: when moving expression for HAVING to WHERE, do we need +to do something with it? Replace all Item_ref objects with items that +they refer to? + - In case of referring to expression, do we get + Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? +) + +4. When to do the pushdown +========================== +In order to do pushdown, we must have prepare phase finished +for both parent (so that we can make sense of its WHERE condition) and +child (so that we know what it has in its select list). + +We can do pushdown before we've done join optimization (i.e. choose_plan() +call) of the parent. + +We must do pushdown before we've done JOIN::optimize() of the child +(in particular, it must be done before we do update_ref_and_keys() and +range analysis in the child). + + +5. Other things to take care of +=============================== +* Pushing down fulltext predicates (it seems one needs to "register" a + fulltext predicate when it is moved from one select from another? Ask Serg) DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. HIGH-LEVEL SPECIFICATION: <contents> HLS 1. Problems to be addressed in this WL 2. Pushdown of conditions into non-mergeable VIEWs 2.1 A note about VIEWs with ORDER BY ... LIMIT 2.2 What condition can be pushed 3. Pushdown from HAVING into WHERE 4. When to do the pushdown 5. Other things to take care of </contents> 1. Problems to be addressed in this WL ====================================== The problem actually consists of two parts: 1. Condition on VIEW columns are not pushed down into VIEWs. 2. Even if conditions were pushed, they would have been put into VIEW's HAVING clause, which would not give enough of speedup. In order to get a real speedup, the optimizer must be able to move relevant part of HAVING into WHERE (and then use it for further optimizations) in order to provide the desired speedup. Note that HAVING->WHERE condition move is orthogonal to VIEW processing. 2. Pushdown of conditions into non-mergeable VIEWs ================================================== We can push a condition into non-mergeable VIEW when VIEW's top-most operation is selection (i.e., filtering). This is true, for example, when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] and not true when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n Generalizing the above, we arrive at the following rule: For non-mergeable VIEWs, - pushdown must not be done if VIEW uses ORDER BY .. LIMIT - when pushdown is done, the pushed condition should be added to the WHERE clause. Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as top operation. (TODO: what about SELECT DISTINCT?) (TODO: pushdown down into IN subqueries?) 2.1 A note about VIEWs with ORDER BY ... LIMIT ---------------------------------------------- Although it is not possible to push a condition below the ORDER BY ... LIMIT operation, there is still some benefit from checking the condition early as that would allow to avoid writing non-matching rows into temporary table. We could do that if we introduced a post-ORDERBY selection operation. That operation would also allow to support ORDER BY ... LIMIT inside subqueries (we don't currently support those because default subquery strategy, IN->EXISTS rewrite, also needs to push a condition into subquery). 2.2 What condition can be pushed -------------------------------- Assuming simplify_joins() operation has done normalization: * If the VIEW is in top-level join list, or inside a semi-join that's in top-level join list, we can push parts of WHERE condition. * If the VIEW is inside an outer join, we can push it's ON expression. We can reuse make_cond_for_index()/make_remainder_cond() code to extract part of condition that can be pushed, and the remainder, respectively. Pushability criteria for an atomic (i.e. not AND/OR) condition is that the condition only uses VIEW's fields. (TODO: what about fields of const tables? Do we have const tables already retrived by the time VIEW is materialized? If yes, we could push down expressions that refer to const tables, too) 3. Pushdown from HAVING into WHERE ================================== The idea is: Parts of HAVING that refer to columns/expressions we're doing GROUP BY on can be put into WHERE. (TODO: do we need to handle case of grouping over expressions?) (TODO: when moving expression for HAVING to WHERE, do we need to do something with it? Replace all Item_ref objects with items that they refer to? - In case of referring to expression, do we get Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? ) 4. When to do the pushdown ========================== In order to do pushdown, we must have prepare phase finished for both parent (so that we can make sense of its WHERE condition) and child (so that we know what it has in its select list). We can do pushdown before we've done join optimization (i.e. choose_plan() call) of the parent. We must do pushdown before we've done JOIN::optimize() of the child (in particular, it must be done before we do update_ref_and_keys() and range analysis in the child). 5. Other things to take care of =============================== * Pushing down fulltext predicates (it seems one needs to "register" a fulltext predicate when it is moved from one select from another? Ask Serg) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

3 2

[Maria-developers] Updated (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 24 May 2010, 20:59)=-=- High-Level Specification modified. --- /tmp/wklog.119.old.25116 2010-05-24 20:59:40.000000000 +0000 +++ /tmp/wklog.119.new.25116 2010-05-24 20:59:40.000000000 +0000 @@ -1 +1,113 @@ +<contents> +HLS +1. Problems to be addressed in this WL +2. Pushdown of conditions into non-mergeable VIEWs +2.1 A note about VIEWs with ORDER BY ... LIMIT +2.2 What condition can be pushed +3. Pushdown from HAVING into WHERE +4. When to do the pushdown +5. Other things to take care of + + +</contents> + +1. Problems to be addressed in this WL +====================================== +The problem actually consists of two parts: +1. Condition on VIEW columns are not pushed down into VIEWs. +2. Even if conditions were pushed, they would have been put into VIEW's +HAVING clause, which would not give enough of speedup. In order to get a +real speedup, the optimizer must be able to move relevant part of HAVING +into WHERE (and then use it for further optimizations) in order to provide +the desired speedup. Note that HAVING->WHERE condition move is orthogonal +to VIEW processing. + +2. Pushdown of conditions into non-mergeable VIEWs +================================================== +We can push a condition into non-mergeable VIEW when VIEW's top-most operation +is selection (i.e., filtering). This is true, for example, when the VIEW is +defined as + + SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] + +and not true when the VIEW is defined as + + SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n + +Generalizing the above, we arrive at the following rule: + + For non-mergeable VIEWs, + - pushdown must not be done if VIEW uses ORDER BY .. LIMIT + - when pushdown is done, the pushed condition should be added to the WHERE + clause. + +Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as +top operation. + +(TODO: what about SELECT DISTINCT?) +(TODO: pushdown down into IN subqueries?) + +2.1 A note about VIEWs with ORDER BY ... LIMIT +---------------------------------------------- +Although it is not possible to push a condition below the ORDER BY ... LIMIT +operation, there is still some benefit from checking the condition early as +that would allow to avoid writing non-matching rows into temporary table. + +We could do that if we introduced a post-ORDERBY selection operation. That +operation would also allow to support ORDER BY ... LIMIT inside subqueries +(we don't currently support those because default subquery strategy, +IN->EXISTS rewrite, also needs to push a condition into subquery). + +2.2 What condition can be pushed +-------------------------------- +Assuming simplify_joins() operation has done normalization: +* If the VIEW is in top-level join list, or inside a semi-join that's in + top-level join list, we can push parts of WHERE condition. +* If the VIEW is inside an outer join, we can push it's ON expression. + +We can reuse make_cond_for_index()/make_remainder_cond() code to extract part +of condition that can be pushed, and the remainder, respectively. + +Pushability criteria for an atomic (i.e. not AND/OR) condition is that + + the condition only uses VIEW's fields. + +(TODO: what about fields of const tables? Do we have const tables already +retrived by the time VIEW is materialized? If yes, we could push down +expressions that refer to const tables, too) + +3. Pushdown from HAVING into WHERE +================================== +The idea is: + + Parts of HAVING that refer to columns/expressions we're doing GROUP BY on + can be put into WHERE. + +(TODO: do we need to handle case of grouping over expressions?) + +(TODO: when moving expression for HAVING to WHERE, do we need +to do something with it? Replace all Item_ref objects with items that +they refer to? + - In case of referring to expression, do we get + Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? +) + +4. When to do the pushdown +========================== +In order to do pushdown, we must have prepare phase finished +for both parent (so that we can make sense of its WHERE condition) and +child (so that we know what it has in its select list). + +We can do pushdown before we've done join optimization (i.e. choose_plan() +call) of the parent. + +We must do pushdown before we've done JOIN::optimize() of the child +(in particular, it must be done before we do update_ref_and_keys() and +range analysis in the child). + + +5. Other things to take care of +=============================== +* Pushing down fulltext predicates (it seems one needs to "register" a + fulltext predicate when it is moved from one select from another? Ask Serg) DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. HIGH-LEVEL SPECIFICATION: <contents> HLS 1. Problems to be addressed in this WL 2. Pushdown of conditions into non-mergeable VIEWs 2.1 A note about VIEWs with ORDER BY ... LIMIT 2.2 What condition can be pushed 3. Pushdown from HAVING into WHERE 4. When to do the pushdown 5. Other things to take care of </contents> 1. Problems to be addressed in this WL ====================================== The problem actually consists of two parts: 1. Condition on VIEW columns are not pushed down into VIEWs. 2. Even if conditions were pushed, they would have been put into VIEW's HAVING clause, which would not give enough of speedup. In order to get a real speedup, the optimizer must be able to move relevant part of HAVING into WHERE (and then use it for further optimizations) in order to provide the desired speedup. Note that HAVING->WHERE condition move is orthogonal to VIEW processing. 2. Pushdown of conditions into non-mergeable VIEWs ================================================== We can push a condition into non-mergeable VIEW when VIEW's top-most operation is selection (i.e., filtering). This is true, for example, when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] and not true when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n Generalizing the above, we arrive at the following rule: For non-mergeable VIEWs, - pushdown must not be done if VIEW uses ORDER BY .. LIMIT - when pushdown is done, the pushed condition should be added to the WHERE clause. Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as top operation. (TODO: what about SELECT DISTINCT?) (TODO: pushdown down into IN subqueries?) 2.1 A note about VIEWs with ORDER BY ... LIMIT ---------------------------------------------- Although it is not possible to push a condition below the ORDER BY ... LIMIT operation, there is still some benefit from checking the condition early as that would allow to avoid writing non-matching rows into temporary table. We could do that if we introduced a post-ORDERBY selection operation. That operation would also allow to support ORDER BY ... LIMIT inside subqueries (we don't currently support those because default subquery strategy, IN->EXISTS rewrite, also needs to push a condition into subquery). 2.2 What condition can be pushed -------------------------------- Assuming simplify_joins() operation has done normalization: * If the VIEW is in top-level join list, or inside a semi-join that's in top-level join list, we can push parts of WHERE condition. * If the VIEW is inside an outer join, we can push it's ON expression. We can reuse make_cond_for_index()/make_remainder_cond() code to extract part of condition that can be pushed, and the remainder, respectively. Pushability criteria for an atomic (i.e. not AND/OR) condition is that the condition only uses VIEW's fields. (TODO: what about fields of const tables? Do we have const tables already retrived by the time VIEW is materialized? If yes, we could push down expressions that refer to const tables, too) 3. Pushdown from HAVING into WHERE ================================== The idea is: Parts of HAVING that refer to columns/expressions we're doing GROUP BY on can be put into WHERE. (TODO: do we need to handle case of grouping over expressions?) (TODO: when moving expression for HAVING to WHERE, do we need to do something with it? Replace all Item_ref objects with items that they refer to? - In case of referring to expression, do we get Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? ) 4. When to do the pushdown ========================== In order to do pushdown, we must have prepare phase finished for both parent (so that we can make sense of its WHERE condition) and child (so that we know what it has in its select list). We can do pushdown before we've done join optimization (i.e. choose_plan() call) of the parent. We must do pushdown before we've done JOIN::optimize() of the child (in particular, it must be done before we do update_ref_and_keys() and range analysis in the child). 5. Other things to take care of =============================== * Pushing down fulltext predicates (it seems one needs to "register" a fulltext predicate when it is moved from one select from another? Ask Serg) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Psergey - Mon, 24 May 2010, 20:59)=-=- High-Level Specification modified. --- /tmp/wklog.119.old.25116 2010-05-24 20:59:40.000000000 +0000 +++ /tmp/wklog.119.new.25116 2010-05-24 20:59:40.000000000 +0000 @@ -1 +1,113 @@ +<contents> +HLS +1. Problems to be addressed in this WL +2. Pushdown of conditions into non-mergeable VIEWs +2.1 A note about VIEWs with ORDER BY ... LIMIT +2.2 What condition can be pushed +3. Pushdown from HAVING into WHERE +4. When to do the pushdown +5. Other things to take care of + + +</contents> + +1. Problems to be addressed in this WL +====================================== +The problem actually consists of two parts: +1. Condition on VIEW columns are not pushed down into VIEWs. +2. Even if conditions were pushed, they would have been put into VIEW's +HAVING clause, which would not give enough of speedup. In order to get a +real speedup, the optimizer must be able to move relevant part of HAVING +into WHERE (and then use it for further optimizations) in order to provide +the desired speedup. Note that HAVING->WHERE condition move is orthogonal +to VIEW processing. + +2. Pushdown of conditions into non-mergeable VIEWs +================================================== +We can push a condition into non-mergeable VIEW when VIEW's top-most operation +is selection (i.e., filtering). This is true, for example, when the VIEW is +defined as + + SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] + +and not true when the VIEW is defined as + + SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n + +Generalizing the above, we arrive at the following rule: + + For non-mergeable VIEWs, + - pushdown must not be done if VIEW uses ORDER BY .. LIMIT + - when pushdown is done, the pushed condition should be added to the WHERE + clause. + +Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as +top operation. + +(TODO: what about SELECT DISTINCT?) +(TODO: pushdown down into IN subqueries?) + +2.1 A note about VIEWs with ORDER BY ... LIMIT +---------------------------------------------- +Although it is not possible to push a condition below the ORDER BY ... LIMIT +operation, there is still some benefit from checking the condition early as +that would allow to avoid writing non-matching rows into temporary table. + +We could do that if we introduced a post-ORDERBY selection operation. That +operation would also allow to support ORDER BY ... LIMIT inside subqueries +(we don't currently support those because default subquery strategy, +IN->EXISTS rewrite, also needs to push a condition into subquery). + +2.2 What condition can be pushed +-------------------------------- +Assuming simplify_joins() operation has done normalization: +* If the VIEW is in top-level join list, or inside a semi-join that's in + top-level join list, we can push parts of WHERE condition. +* If the VIEW is inside an outer join, we can push it's ON expression. + +We can reuse make_cond_for_index()/make_remainder_cond() code to extract part +of condition that can be pushed, and the remainder, respectively. + +Pushability criteria for an atomic (i.e. not AND/OR) condition is that + + the condition only uses VIEW's fields. + +(TODO: what about fields of const tables? Do we have const tables already +retrived by the time VIEW is materialized? If yes, we could push down +expressions that refer to const tables, too) + +3. Pushdown from HAVING into WHERE +================================== +The idea is: + + Parts of HAVING that refer to columns/expressions we're doing GROUP BY on + can be put into WHERE. + +(TODO: do we need to handle case of grouping over expressions?) + +(TODO: when moving expression for HAVING to WHERE, do we need +to do something with it? Replace all Item_ref objects with items that +they refer to? + - In case of referring to expression, do we get + Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? +) + +4. When to do the pushdown +========================== +In order to do pushdown, we must have prepare phase finished +for both parent (so that we can make sense of its WHERE condition) and +child (so that we know what it has in its select list). + +We can do pushdown before we've done join optimization (i.e. choose_plan() +call) of the parent. + +We must do pushdown before we've done JOIN::optimize() of the child +(in particular, it must be done before we do update_ref_and_keys() and +range analysis in the child). + + +5. Other things to take care of +=============================== +* Pushing down fulltext predicates (it seems one needs to "register" a + fulltext predicate when it is moved from one select from another? Ask Serg) DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. HIGH-LEVEL SPECIFICATION: <contents> HLS 1. Problems to be addressed in this WL 2. Pushdown of conditions into non-mergeable VIEWs 2.1 A note about VIEWs with ORDER BY ... LIMIT 2.2 What condition can be pushed 3. Pushdown from HAVING into WHERE 4. When to do the pushdown 5. Other things to take care of </contents> 1. Problems to be addressed in this WL ====================================== The problem actually consists of two parts: 1. Condition on VIEW columns are not pushed down into VIEWs. 2. Even if conditions were pushed, they would have been put into VIEW's HAVING clause, which would not give enough of speedup. In order to get a real speedup, the optimizer must be able to move relevant part of HAVING into WHERE (and then use it for further optimizations) in order to provide the desired speedup. Note that HAVING->WHERE condition move is orthogonal to VIEW processing. 2. Pushdown of conditions into non-mergeable VIEWs ================================================== We can push a condition into non-mergeable VIEW when VIEW's top-most operation is selection (i.e., filtering). This is true, for example, when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] [HAVING having_cond] and not true when the VIEW is defined as SELECT select_list FROM from_clause [WHERE where_cond] ORDER BY expr LIMIT n Generalizing the above, we arrive at the following rule: For non-mergeable VIEWs, - pushdown must not be done if VIEW uses ORDER BY .. LIMIT - when pushdown is done, the pushed condition should be added to the WHERE clause. Note: In scope of this WL, we will not hande VIEWs that have UNION [ALL] as top operation. (TODO: what about SELECT DISTINCT?) (TODO: pushdown down into IN subqueries?) 2.1 A note about VIEWs with ORDER BY ... LIMIT ---------------------------------------------- Although it is not possible to push a condition below the ORDER BY ... LIMIT operation, there is still some benefit from checking the condition early as that would allow to avoid writing non-matching rows into temporary table. We could do that if we introduced a post-ORDERBY selection operation. That operation would also allow to support ORDER BY ... LIMIT inside subqueries (we don't currently support those because default subquery strategy, IN->EXISTS rewrite, also needs to push a condition into subquery). 2.2 What condition can be pushed -------------------------------- Assuming simplify_joins() operation has done normalization: * If the VIEW is in top-level join list, or inside a semi-join that's in top-level join list, we can push parts of WHERE condition. * If the VIEW is inside an outer join, we can push it's ON expression. We can reuse make_cond_for_index()/make_remainder_cond() code to extract part of condition that can be pushed, and the remainder, respectively. Pushability criteria for an atomic (i.e. not AND/OR) condition is that the condition only uses VIEW's fields. (TODO: what about fields of const tables? Do we have const tables already retrived by the time VIEW is materialized? If yes, we could push down expressions that refer to const tables, too) 3. Pushdown from HAVING into WHERE ================================== The idea is: Parts of HAVING that refer to columns/expressions we're doing GROUP BY on can be put into WHERE. (TODO: do we need to handle case of grouping over expressions?) (TODO: when moving expression for HAVING to WHERE, do we need to do something with it? Replace all Item_ref objects with items that they refer to? - In case of referring to expression, do we get Item_ref(where_clause_expr) or expr( Item_ref(..), .., Item_ref(..))? ) 4. When to do the pushdown ========================== In order to do pushdown, we must have prepare phase finished for both parent (so that we can make sense of its WHERE condition) and child (so that we know what it has in its select list). We can do pushdown before we've done join optimization (i.e. choose_plan() call) of the parent. We must do pushdown before we've done JOIN::optimize() of the child (in particular, it must be done before we do update_ref_and_keys() and range analysis in the child). 5. Other things to take care of =============================== * Pushing down fulltext predicates (it seems one needs to "register" a fulltext predicate when it is moved from one select from another? Ask Serg) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Psergey): Push conditions down into non-mergeable VIEWs when possible (119)
by worklog-noreply＠askmonty.org 24 May '10

24 May '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Push conditions down into non-mergeable VIEWs when possible CREATION DATE..: Mon, 24 May 2010, 20:52 SUPERVISOR.....: Igor IMPLEMENTOR....: COPIES TO......: Psergey, Timour CATEGORY.......: Server-RawIdeaBin TASK ID........: 119 (http://askmonty.org/worklog/?tid=119) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: There are complaints (see links below) about cases with non-mergeable VIEW (because it has a GROUP BY), a query that has restrictions on the grouped column, and poor performance that is caused by VIEW processing code ignoring the restriction. This WL is about addressing this issue. links to complaints: http://code.openark.org/blog/mysql/views-better-performance-with-condition-… http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-perform… The target version is MariaDB 5.3+, because it has late optimization/execution for FROM-subqueries/non mergeable VIEWs, which makes it much more feasible to inject something into VIEW before it is optimized/executed. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2789: Subquery cache (as is) in file:///home/bell/maria/bzr/work-maria-5.3-scache/
by sanja＠askmonty.org 24 May '10

24 May '10

At file:///home/bell/maria/bzr/work-maria-5.3-scache/ ------------------------------------------------------------ revno: 2789 revision-id: sanja(a)askmonty.org-20100524172956-7b14x01aodizr3sq parent: sergii(a)pisem.net-20100510134608-oyi2vznyghgcrt0x committer: sanja(a)askmonty.org branch nick: work-maria-5.3-scache timestamp: Mon 2010-05-24 20:29:56 +0300 message: Subquery cache (as is) === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/libmysqld/Makefile.am 2010-05-24 17:29:56 +0000 @@ -80,7 +80,8 @@ sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_subquery_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ b/sql/CMakeLists.txt 2010-05-24 17:29:56 +0000 @@ -78,7 +78,7 @@ rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_subquery_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/sql/Makefile.am 2010-05-24 17:29:56 +0000 @@ -80,7 +80,7 @@ event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_subquery_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_subquery_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item.cc 2010-05-24 17:29:56 +0000 @@ -2273,6 +2273,13 @@ str->append(str_value); } +void Item_bool_cache::print(String *str, enum_query_type query_type) +{ + if (null_value) + str->append("NULL", 4); + else + Item_int::print(str, query_type); +} Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) @@ -3646,12 +3653,17 @@ resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3673,7 @@ resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -3698,6 +3710,7 @@ resolving) */ SELECT_LEX *previous_select= current_sel; + for (; previous_select->outer_select() != last_select; previous_select= previous_select->outer_select()) { @@ -3726,6 +3739,7 @@ mark_as_dependent(thd, last_select, current_sel, resolved_item, dependent); } + return; } @@ -4098,6 +4112,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); return 0; } } @@ -4113,7 +4130,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference to a view field had been found and we substituted it instead of this Item (find_field_in_tables @@ -4215,6 +4234,10 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, rf, rf); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); + return 0; } else @@ -4222,6 +4245,9 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, (Item_ident*)*reference); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -5973,6 +5999,9 @@ refer_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* view reference found, we substituted it instead of this Item, so can quit @@ -6023,6 +6052,9 @@ thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, fld, fld); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6046,6 +6078,9 @@ DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + ref); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of === modified file 'sql/item.h' --- a/sql/item.h 2010-03-20 12:01:47 +0000 +++ b/sql/item.h 2010-05-24 17:29:56 +0000 @@ -1143,6 +1143,11 @@ { return Field::GEOM_GEOMETRY; }; String *check_well_formed_result(String *str, bool send_error= 0); bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); + + /** + Used to get reference on real item (not Item_ref) + */ + virtual Item **unref(Item **my_ref) { return my_ref; }; }; @@ -1922,8 +1927,31 @@ virtual void print(String *str, enum_query_type query_type); Item_num *neg (); uint decimal_precision() const { return max_length; } - bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} - bool check_vcol_func_processor(uchar *arg) { return FALSE;} +}; + + +/** + Item represent TRUE/FALSE/NULL for subquery values +*/ + +class Item_bool_cache: public Item_int +{ +public: + Item_bool_cache(): Item_int(0, 1) + { + unsigned_flag= maybe_null= null_value= TRUE; + name= (char *)"bool chache"; + } + Item_bool_cache(my_bool val, my_bool null): Item_int(val, 1) + { + unsigned_flag= maybe_null= TRUE; + null_value= null; + name= (char *)"bool chache"; + } + Item *clone_item() { return new Item_bool_cache(value, null_value); } + uint decimal_precision() const { return 1; } + virtual void print(String *str, enum_query_type query_type); + void set(my_bool val, my_bool null) {value= test(val); null_value= null;} }; @@ -2479,6 +2507,11 @@ { return trace_unsupported_by_check_vcol_func_processor("ref"); } + + /** + Used to get reference on real item (not Item_ref) + */ + virtual Item **unref(Item **my_ref) {return (*ref)->unref(ref); }; }; @@ -3146,7 +3179,8 @@ example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3188,7 @@ value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-24 17:29:56 +0000 @@ -1736,6 +1736,13 @@ used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); + DBUG_ASSERT(scache == NULL); + if (args[0]->cols() ==1 && + thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE) + { + sub->depends_on.push_front((Item**)&cache); + scache= new Subquery_cache_tmptable(thd, sub->depends_on, &result); + } fixed= 1; return FALSE; } @@ -1744,10 +1751,26 @@ longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + + /* check if result is in the cache */ + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + { + tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + } + if (cache->null_value) { /* @@ -1818,11 +1841,18 @@ for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + + /* put result in the cache */ + if (scache) + { + result.set(tmp, null_value); + scache->put_value(&result); + } + DBUG_RETURN(tmp); } @@ -1839,6 +1869,11 @@ Item_bool_func::cleanup(); if (!save_cache) cache= 0; + if (scache) + { + delete scache; + scache= 0; + } DBUG_VOID_RETURN; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.h 2010-05-24 17:29:56 +0000 @@ -215,6 +215,7 @@ class Item_cache; +class Subquery_cache; #define UNKNOWN ((my_bool)-1) @@ -237,6 +238,10 @@ { protected: Item_cache *cache; + /* Subquery cache */ + Subquery_cache *scache; + /* result representation for the subquery cache */ + Item_bool_cache result; bool save_cache; /* Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: @@ -247,7 +252,7 @@ my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), scache(NULL), save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.cc 2010-05-24 17:29:56 +0000 @@ -1,4 +1,4 @@ -/* Copyright (C) 2000 MySQL AB +/* Copyrigh (C) 2000 MySQL AB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -34,11 +34,10 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), - engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), - inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), - engine_changed(0), changed(0), is_correlated(FALSE) + engine(0), old_engine(0), scache(0), used_tables_cache(0), + have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0), + done_first_fix_fields(FALSE), eliminated(FALSE), engine_changed(0), + changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -116,6 +115,12 @@ } if (engine) engine->cleanup(); + depends_on.empty(); + if (scache) + { + delete scache; + scache= 0; + } reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -148,6 +153,8 @@ Item_subselect::~Item_subselect() { delete engine; + if (scache) + delete scache; } Item_subselect::trans_res @@ -746,9 +753,19 @@ void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); + if (depends_on.elements && optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, value); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } } else { @@ -765,6 +782,7 @@ */ if (engine->no_tables()) maybe_null= engine->may_be_null(); + DBUG_VOID_RETURN; } uint Item_singlerow_subselect::cols() @@ -797,77 +815,200 @@ exec(); } + +Item *Item_subselect::check_cache() +{ + DBUG_ENTER("Item_subselect::check_cache"); + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + double Item_singlerow_subselect::val_real() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_real"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_real(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_real()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } longlong Item_singlerow_subselect::val_int() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_int"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_int(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_int()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } String *Item_singlerow_subselect::val_str(String *str) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_str"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_str(str); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_str(str)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_decimal"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_decimal(decimal_value); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_decimal(decimal_value)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } bool Item_singlerow_subselect::val_bool() { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_bool"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + bool res= cached_value->val_bool(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_bool(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_bool()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } @@ -952,33 +1093,77 @@ void Item_exists_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_exists_subselect::fix_length_and_dec"); decimals= 0; max_length= 1; max_columns= engine->cols(); /* We need only 1 row to determine existence */ unit->global_parameters->select_limit= new Item_int((int32) 1); + if (substype() == EXISTS_SUBS && depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE)) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, &result); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } + DBUG_VOID_RETURN; } double Item_exists_subselect::val_real() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_int"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return (double) value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN((double) value); } longlong Item_exists_subselect::val_int() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + DBUG_ASSERT(fixed == 1); if (exec()) { reset(); - return 0; - } - return value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value); } @@ -997,11 +1182,31 @@ String *Item_exists_subselect::val_str(String *str) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_str"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + DBUG_RETURN(NULL); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + str->set((ulonglong)value,&my_charset_bin); - return str; + DBUG_RETURN(str); } @@ -1020,23 +1225,60 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_decvimal"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value); - return decimal_value; + DBUG_RETURN(decimal_value); } bool Item_exists_subselect::val_bool() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_bool res= cached_value->val_bool(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return value != 0; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value != 0); } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.h 2010-05-24 17:29:56 +0000 @@ -27,6 +27,7 @@ class subselect_hash_sj_engine; class Item_bool_func2; class Cached_item; +class Subquery_cache; /* base class for subselects */ @@ -57,6 +58,10 @@ subselect_engine *engine; /* old engine if engine was changed */ subselect_engine *old_engine; + /* subquery cache */ + Subquery_cache *scache; + /* null consrtant for caching */ + Item_null const_null_value; /* cache of used external tables */ table_map used_tables_cache; /* allowed number of columns (1 for single value subqueries) */ @@ -67,7 +72,7 @@ bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + bool inside_first_fix_fields; bool done_first_fix_fields; public: @@ -88,13 +93,18 @@ */ List<Ref_to_outside> upper_refs; st_select_lex *parent_select; - - /* + + /** + List of items subquery depends on (externally resolved); + */ + List<Item*> depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; - + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -178,6 +188,8 @@ return trace_unsupported_by_check_vcol_func_processor("subselect"); } + Item *check_cache(); + /** Get the SELECT_LEX structure associated with this Item. @return the SELECT_LEX structure associated with this Item @@ -202,6 +214,7 @@ { protected: Item_cache *value, **row; + public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} @@ -268,6 +281,8 @@ { protected: bool value; /* value of this item (boolean: exists/not-exists) */ + /* result representation for the subquery cache */ + Item_bool_cache result; public: Item_exists_subselect(st_select_lex *select_lex); === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ b/sql/mysql_priv.h 2010-05-24 17:29:56 +0000 @@ -568,12 +568,13 @@ #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -936,6 +939,7 @@ #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_subquery_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1273,10 @@ Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1296,7 @@ bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ b/sql/mysqld.cc 2010-05-24 17:29:56 +0000 @@ -305,6 +305,7 @@ "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,8 @@ {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8013,7 @@ abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_base.cc 2010-05-24 17:29:56 +0000 @@ -8062,6 +8062,10 @@ if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.cc 2010-05-24 17:29:56 +0000 @@ -3020,6 +3020,7 @@ table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.h 2010-05-24 17:29:56 +0000 @@ -2786,12 +2786,17 @@ that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; 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) + bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() { === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.cc 2010-05-24 17:29:56 +0000 @@ -1829,6 +1829,53 @@ } +/** + Registers reference on items on which the subqueries depends + + @param last pointer to last st_select_lex struct, before + which all st_select_lex have to be marked as + dependent + @param dependency reference on the item on which all this + subqueries depends + +*/ + +void st_select_lex::register_dependency_item(st_select_lex *last, + Item **dependency) +{ + SELECT_LEX *s= this; + DBUG_ENTER("st_select_lex::register_dependency_item"); + DBUG_ASSERT(this != last); + DBUG_ASSERT(*dependency); + dependency= (*dependency)->unref(dependency); + do + { + /* check duplicates */ + List_iterator_fast<Item*> li(s->master_unit()->item->depends_on); + Item **dep; + while ((dep= li++)) + { + if ((*dep)->eq(*dependency, FALSE)) + { + DBUG_PRINT("info", ("dependency %s already present", + ((*dependency)->name ? + (*dependency)->name : + "<no name>"))); + DBUG_VOID_RETURN; + } + } + + s->master_unit()->item->depends_on.push_back(dependency); + DBUG_PRINT("info", ("depends_on: Select: %d added: %s", + s->select_number, + ((*dependency)->name ? + (*dependency)->name : + "<no name>"))); + } while ((s= s->outer_select()) != last && s != 0); + DBUG_VOID_RETURN; +} + + /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent @@ -1843,7 +1890,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1872,11 +1919,11 @@ Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.h 2010-05-24 17:29:56 +0000 @@ -748,6 +748,7 @@ } bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency); + void register_dependency_item(st_select_lex *last, Item **dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-05-10 13:46:08 +0000 +++ b/sql/sql_select.cc 2010-05-24 17:29:56 +0000 @@ -151,7 +151,6 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -5209,7 +5208,7 @@ 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); @@ -7625,6 +7624,40 @@ /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + /*tab->read_record.unlock_row= rr_unlock_row;*/ + tab->next_select=0; + tab->sorted= 1; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + /*tab->read_record.unlock_row= join_read_key_unlock_row;*/ + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10778,6 +10811,7 @@ case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11552,7 +11586,8 @@ &param->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11700,16 +11735,17 @@ bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12540,7 +12576,8 @@ else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13323,61 +13360,61 @@ static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record && table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === modified file 'sql/table.cc' --- a/sql/table.cc 2010-03-20 12:01:47 +0000 +++ b/sql/table.cc 2010-05-24 17:29:56 +0000 @@ -20,6 +20,7 @@ #include "sql_trigger.h" #include <m_ctype.h> #include "my_md5.h" +#include "my_bit.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5097,115 @@ file->column_bitmaps_signal(); } + +/** + @brief + Allocate space for keys + + @param key_count number of keys to allocate. + + @details + Allocate space enough to fit 'key_count' keys for this table. + + @return FALSE space was successfully allocated. + @return TRUE an error occur. +*/ + +bool TABLE::alloc_keys(uint key_count) +{ + DBUG_ASSERT(!s->keys); + key_info= s->key_info= (KEY*) my_malloc(sizeof(KEY)*key_count, MYF(0)); + max_keys= key_count; + return !(key_info); +} + + +/** + @brief Adds one key to a temporary table. + + @param key_parts bitmap of fields that take a part in the key. + @param key_name name of the key + + @details + Creates a key for this table from fields which corresponds the bits set to 1 + in the 'key_parts' bitmap. The 'key_name' name is given to the newly created + key. + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +int TABLE::add_tmp_key(ulonglong key_parts, const char *key_name) +{ + DBUG_ASSERT(s->keys< max_keys); + + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + uint key_part_count= my_count_bits(key_parts); + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) my_malloc(sizeof(KEY_PART_INFO)* key_part_count, MYF(0)); + if (!key_part_info) + return -1; + keyinfo= key_info + s->keys; + keyinfo->key_part=key_part_info; + keyinfo->usable_key_parts=keyinfo->key_parts= key_part_count; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->name= (char *)key_name; + keyinfo->flags= HA_GENERATED_KEY; + keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0)); + if (!keyinfo->rec_per_key) + return -1; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_part_count); + for (i= 0, reg_field=field ; + *reg_field; + i++, reg_field++) + { + if (!(key_parts & (1 << i))) + continue; + if (key_start) + (*reg_field)->key_start.set_bit(s->keys); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(s->keys); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + /* TODO: + The below method of computing the key format length of the + key part is a copy/paste from opt_range.cc, and table.cc. + This should be factored out, e.g. as a method of Field. + In addition it is not clear if any of the Field::*_length + methods is supposed to compute the same length. If so, it + might be reused. + */ + key_part_info->store_length= key_part_info->length; + + if ((*reg_field)->real_maybe_null()) + key_part_info->store_length+= HA_KEY_NULL_LENGTH; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part_info->store_length+= HA_KEY_BLOB_LENGTH; + + key_part_info->type= (uint8) (*reg_field)->key_type(); + key_part_info->key_type = + ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? + 0 : FIELDFLAG_BINARY; + key_part_info++; + } + set_if_bigger(s->max_key_length, keyinfo->key_length); + return ++s->keys - 1; +} + + /** @brief Check if this is part of a MERGE table with attached children. === modified file 'sql/table.h' --- a/sql/table.h 2010-03-20 12:01:47 +0000 +++ b/sql/table.h 2010-05-24 17:29:56 +0000 @@ -781,6 +781,7 @@ uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -914,6 +915,8 @@ inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } bool is_children_attached(void); + bool alloc_keys(uint key_count); + int add_tmp_key(ulonglong key_parts, const char *key_name); }; enum enum_schema_table_state === modified file 'storage/maria/ha_maria.cc' --- a/storage/maria/ha_maria.cc 2010-03-20 12:01:47 +0000 +++ b/storage/maria/ha_maria.cc 2010-05-24 17:29:56 +0000 @@ -995,6 +995,8 @@ { MARIA_HA *tmp= file; file= 0; + if (!tmp) + return 0; return maria_close(tmp); }

1 0

[Maria-developers] internals of page row format
by Igor Kozyrev 19 May '10

19 May '10

Could anyone please explain data representation, both on disk and in memory? Especially about BLOB fields What was the reasons to move hot data from OS file cache to MySQL memory? Is it great improvement of efficiency(to avoid kernel calls) or necessity for future transaction support? -- This message was sent from Launchpad by the user Igor Kozyrev (https://launchpad.net/~ikozyrev36) using the "Contact this team" link on the Maria developers team page to each member directly. For more information see https://help.launchpad.net/YourAccount/ContactingPeople

1 0

[Maria-developers] Segmented Key Cache - About section
by Daniel Bartholomew 19 May '10

19 May '10

Hello everyone, I'm working on the documentation for the Segmented Key Cache, one of the new features in MariaDB 5.2. The documentation page will have "about", "syntax", and "example" sections. I've based the "About" section on the High-Level Description from the worklog ( http://askmonty.org/worklog/Server-Sprint/?tid=85 ) and from the description in the mysys/mf_keycache.c file in the source. Here is my first draft. I've changed the wording a bit to help it flow better and I've changed "partition" to "segment" in keeping with the official name. ---------------------------------------------------------------------- == About Segmented Key Cache == A segmented key cache is a collection of structures for regular MyISAM key caches called key cache segments. Segmented key caches mitigate one of the major problem of the simple key cache: thread contention for key cache lock (mutex). With regular key caches, every call of a key cache interface function must acquire this lock. Threads compete for this lock even when the file and pages they have acquired shared locks for are in the key cache buffers. When working with a segmented key cache any key cache interface function that needs only one page has to acquire the key cache lock only for the segment the page is assigned to. This makes the chances for threads not having to compete for the same key cache lock better. Any page from a file can be placed into a buffer of only one segment. The number of the segment is calculated from the file number and the position of the page in the file, and it's always the same for the page. Pages are evenly distributed among segments. The idea and the original code of the segmented key cache was provided by Fredrik Nylander from Stardoll.com. The code was extensively reworked, improved, and eventually merged into MariaDB by Igor Babaev from Monty Program. ---------------------------------------------------------------------- If there are any factual errors with the above, let me know. Improvements and suggestions are also welcome. One thing I'm not happy with is this sentence: "Threads compete for this lock even when the file and pages they have acquired shared locks for are in the key cache buffers." The original sentence from the worklog reads: So threads compete for this lock even in the case when they have acquired shared locks for the file and pages they want read from are in the key cache buffers. ... which is very confusing to me. If anyone has a better or more accurate rewording, please send it my way. Also, there's a note in the source code (lines 5018-5023 of mysys/mf_keycache.c) which states: Unfortunately if we use a partitioned key cache with N partitions for B-tree indexes we can't say that the chances becomes N times less. The fact is that any index lookup operation requires reading from the root page that, for any index, is always ascribed to the same partition. To resolve this problem we should have employed more sophisticated mechanisms of working with root pages. Do any of you have any opinions on whether or not this should be mentioned somewhere in the documentation? The only issue I have with mentioning it is that if I do, the first question that comes to my mind is: "If it is not N-times less, how much less is it?" I don't have an answer to that question (and math isn't one of my strengths). Next up will be the "Syntax" section. Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

3 6

Re: [Maria-developers] MySQL 5.1.46 diff of sql/sql_select.cc for review
by Kristian Nielsen 19 May '10

19 May '10

Igor Babaev <igor(a)askmonty.org> writes: > Here's my review. Thanks a lot for your help! I checked through all your points (few detailed comments below), all your proposed solutions seem reasonable to me. In summary, we should as you suggest: - Rework patches for bugs 39022, 48483, and 49324 as you described (1,7,8). - Revert bug 45640 patch (5) and instead apply your proposed patch. - Revert patches for bugs 51242 and 52336, and instead apply your proposed fix (15,18). - Reject (eg. revert in 5.1-release) patch for bug 39653 (2) - Do nothing for bugs 40277, 45195, 45989, 49902, 50995, and 51494 (3,4,6,10,14,16), as necessary changes were already pushed to 5.1-release. - Do nothing for bugs 49829, 50335, 50591, 50843, and 52177 (9,11,12,13,17), as the patches for those are ok. You proposed on the call yesterday that you could prepare a patch with these changes for our merge tree: lp:~maria-captains/maria/5.1-release Please do so. I will try to get hold of Monty and discuss with him, but I think he will agree with making your proposed changes. If you need a full review for your changes, you will probably need to ask someone else than me, as I am unfamiliar with this part of the code. Thanks, - Kristian. > 1. Bug #39022 (by Georgi Kodinov) > ---------------------------------- > > SYNOPSIS > Any call of SQL_SELECT::skip record ignore the fact that > during evaluation of an expression (item) an error may occur. > > CONCLUSION > The patch cannot be applied as it is, requires some rework. > > REASONS: > The patch is incomplete: > There are 5 calls of SQL_SELECT::skip_record in total: > two - in sql_select.cc, and three others in > filesort.cc, sql_delete.cc, sql_update.cc. > Only the calls in sql_select.cc are handled in an error aware > manner. The second call in sql_select is handled incorrectly: > the error occurred at the evaluation of the select condition > is caught only if the function returns false. > > POSSIBLE SOLUTION: > change the synopsis of SQL_SELECT::skip_record: > int SQL_SELECT::skip_record() > { > int rc= test(cond && !cond->val_int()); > if thd->is_error() rc=-1; > return rc; > } > > (thd must be added to SQL_SELECT) > > then after each call SQL_SELECT::retain_record > add error handling for the cases when > SQL_SELECT::skip_record returns -1: > int rc= 0; > if (!select || (rc= select->skip_record()) != 0) > { > if (rc < 0) > { > /* handle error returned by skip_record() */ > ... > } > ... > } Agree with your proposed solution. > 2. Bug #39653 (by Gleb Shchepa) > > SYNOPSIS > InnoDB covering primary index is used when using another > covering index is more beneficial. > > CONCLUSION > The patch must be rejected. > > REASONS: > The patch is based on completely wrong idea that any > covering secondary index is better than primary covering index > for scanning in InnoDB. > > Here an example demonstrating that it's not so: > CREATE TABLE t1 ( > a int, b int, c int, > PRIMARY KEY (a, b), > KEY idx (a,c) > ); > Both primary key and the secondary key here are covering for the query > SELECT a FROM t1 WHERE a BETWEEN 1000 and 5000; > Apparently scanning by the primary key will be faster here as it does > not use random seeks. > > The patch completely ignores non-InnoDB engines. > > POSSIBLE SOLUTION: > Cost based choice that takes into account that sequential access is > C times faster then random access. I am ok with rejecting the patch. It's hard to tell whether primary key or index will be better to use, in some cases secondary index may require no random seeks if it is not fragmented while primary key could be. In any case this seems a dangerous change in a stable release (user can force index of choice if he/she has more information about which will be best). > 3. Bug #40277 (by Davi Arnaut) > ------------------------------- > see the notes from Monty's review Ok, Monty already fixed this in 5.1-release. Agree. > 4. 45195 (by Sergey Glukhov) > ----------------------------- > SYNOPSIS > Reading uninitialized bytes from join cache buffer. > (Valgrind's complain) > > CONCLUSION > The patch should be accepted as it is > (Monty has some a comment on this patch though). Agree, Monty already pushed his fixes to 5.1-release. > 5. Bug 45640 (by Gleb Shchepa) > ------------------------------- > SYNOPSYS. > Building Item_ref objects of a wrong type for outer references used > in aliased expressions in a select with group by causes wrong > results. Group by expressions containing references to aliases may > cause wrong results. > > CONCLUSION. > The patch cannot be accepted as it is, requires a serious re-work. > > REASON. > Although the basic ideas behind the fix appear to be be valid their > implementation is quite clumsy: > -an unnecessary parameter is added to the function fix_inner_refs > -the info about the syntax context of a field referenced is passed > into Item_field::fix_fields in an unconventional and ugly manner > -the group expression are traversed for each reference of the list > of inner references > > POSSIBLE SOLUTION > See the patch at the very end of the post. Agree with proposed solution, using the patch at end. > 6. Bug #45989 (by Georgi Kodinov) > ---------------------------------- > This bug has been already fixed in MariaDB 5.1.44. > Our fix is correct, the fix by Georgi is not quite correct > (but not harmful). Agree, yes the better fix is already pushed. > 7. Bug #48483 (by Sergey Glukhov) > ---------------------------------- > (No public access) > > SYNOPSIS > Wrong calculation of table dependencies for join queries with > outer join operation causes a crash. > > CONCLUSION > The patch can be accepted with one change that matters > - if (!((prev_table->on_expr->used_tables() & ~RAND_TABLE_BIT) & > - ~prev_used_tables)) > + if (!((prev_table->on_expr->used_tables() & > + ~(OUTER_REF_BIT | RAND_TABLE_BIT)) & > + ~prev_used_tables)) I assume you mean OUTER_REF_TABLE_BIT here. Ok. > 8. Bug #49324 (by Georgi Kodinov) > ---------------------------------- > SYNOPSIS > With InnoDB a GROUP BY / ORDER BY can use an index extended by some > number of major components of the primary. The value of rec_per_key > for such extended indexes must be calculated in a special way. > > CONCLUSION > The patch could be accepted after changing the formula that calculates > the value of rec_per_key for an extended index: > > - rec_per_key= used_key_parts && > - used_key_parts <= keyinfo->key_parts ? > - keyinfo->rec_per_key[used_key_parts-1] : 1; > + int used_index_parts= keyinfo->key_parts; > + int used_pk_parts= 0; > + set_if_bigger(used_pk_parts, > + used_key_parts-used_index_parts); > + rec_per_key= keyinfo->rec_per_key[used_key_parts-1]; > + if (used_pk_parts) > + { > + KEY *pkinfo= tab->table->key_info+table->s->primary_key; > + rec_per_key*= pkinfo->rec_per_key[used_pk_parts-1]; > + rec_per_key/= pkinfo->rec_per_key[0]; > + } > > REASONS > The formula in the patch does not take into account how many > components of the of the primary key is used in the extended index. Ok (I don't understand the calculation, but not knowing the code I'm willing to take your word for it). > 9. Bug #49829 (by Staale Smedseng) > ---------------------------------- > SYNOPSYS > Compiler problems (warnings) for a platform > > CONCLUSION > THe patch is ok. Agree (it was kind of nice to see the explanation for these warnings, which I think I saw before but didn't know what meant). > 10. Bug #49902 (by Sergey Vojtovich) > ----------------------------------- > See the comments/suggestions from Monty's review Yes, this is pushed to 5.1-release (and I agree with Monty's comment). > 11. Bug #50335 (by Alexey Kopytov) > ----------------------------------- > (No public access) > > SYNOPSYS > Failure of a wrong assertion > > CONCLUSION > The patch is ok. Ok. > 12. Bug #50591 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Wrong result for a grouping query over a table with a BIT field > > CONCLUSION > The patch is ok. Ok. > 13. Bug #50843 (by Evgeny Potemkin) > ------------------------------------ > SYNOPSIS > Performance degradation problem when join cache + filesort > are used intead of a full index scan by a primary key. > > CONCLUSION > The patch looks ok. Ok. > 14. Bug #50995 (by Sergey Glukhov) > ------------------------------------ > SYNOPSIS > A badly formed list for conditions causes wrong query results. > > CONCLUSION > The patch looks ok for me. > See also Monty's recommendation from his review. Yes. As far as I can see, Monty pushed his changes from his review to 5.1-release. He did however not update the comments for eliminate_item_equal() as per his suggestion (maybe he forgot): "Note that we should update the function comment for eliminate_item_equal() as this can't return 0. (If it would, then other things would break, just look at how this function is used)." > 15. Bug #51242 (by Sergey Glukhov) > ----------------------------------- > SYNOPSYS > The conjuncts that become false after substitution of the constant > tables are ignored. > > CONCLUSION > The fix should be turned down (but not the test case). > > REASON > See the reasons for turning the fix for bug #52336 that is a > correction for this patch. > > POSSIBLE SOLUTION > See the solution for bug #52336. > 16. Bug #51494 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Crash with explain of a query with outer join > > CONCLUSION > The patch must be turned down. > > REASONS > The patch triggers bug #53334 - a failure of a base join > query for InnoDB. > The bug is actually fixed by the patch for bug #52177 > (see my comment for bug #53334). Yes, it is already reverted in our tree. > 17. Bug #52177 (by Sergey Glukhov) > ----------------------------------- > SYNOPSIS > Crash with exaplain for a query with an outer join. > > CONCLUSION > The fix is correct and the patch should be applied. > The patch also fixes the bug #51494. Ok. > 18. Bug #52336 (by Sergey Glukhov) > ----------------------------------- > SYNOPSYS > A crash caused by an invalid fix for bug #51242. > > CONCLUSION > The patch rather should be turned down. > > REASON. > The patch does not fix the real cause of the problem: > a wrong value is passed as a parameter in the call > Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > used_tables, > used_tables); > The patch actually suggest a work-around that hides the bug. > This work-around simultaneously adds a new feature. > that catches impossible HAVINGs after constant table substitution. > Yet impossible WHEREs appeared after this optimization remain > uncaught. So the feature is introduced half-baked. > > POSSIBLE SOLUTION > - Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > - used_tables, > - used_tables); > + Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, > + used_tables, > + (table_map) 0); Ok. This possible solution is not yet applied to our tree (we only discussed it so far).

1 0