3460 Ole John Aske 2012-03-29
More WL5940 addaption of ORDER/GROUP BY handling in order to address
comments from reviewers:
In the case of a 'simple'(*) order by expression which can not
be executed by reading from an ordered index, there is a conflict of
interests between the optimizer and the pushed join integration:
For any 'simple' order by expression the optimizer will establish
the correct ordering of the first non-const table before starting the
join. If there is no suitable ordered index, the table itself will be
filesorted into an intermediate record store. As the table is now
stored outside NDB, such a presorted table can not pe part of a
pushed join.
Before this fix this conflict was resolved in favour of maximizing
usage of pushed joins: The Query Execution Plan (QEP) from the optimizer was
changes such that the presort was dropped. Instead the entire resultset
was calculated (with pushed joins) and then written to a temporary file
which was later filesorted.
The reviewer commented that changing the QEP (above) as part of the pushed join
integration was 'questionable, at best'.
This fix changes this by extending our AQP (Abstract Query Plan) with
functionality to detect when the optimizer has decided to do
'filesort_before_join'. If that condition is detected, this presorted
table is excluded as non-pushable.
To sum up:
- We don't any longer modify QEP as part of pushed join integration.
- This comes at the cost of some order/group by queries being less pushable.
(*) A 'simple' ordering expression contain only column references to the
first non-const table in the join_tab's.
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown_default.result
mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result
mysql-test/suite/ndb/r/ndb_join_pushdown_none.result
mysql-test/suite/ndb/t/ndb_join_pushdown.inc
sql/abstract_query_plan.cc
sql/abstract_query_plan.h
sql/ha_ndbcluster.cc
sql/ha_ndbcluster_push.cc
sql/handler.cc
sql/handler.h
sql/sql_executor.cc
sql/sql_executor.h
sql/sql_optimizer.cc
3459 Ole John Aske 2012-03-23
SPJ: Addapt 'Abstract query plan' interface and SPJ-optimizer integration to latest
review comments. These changes has been made possible by rewrites introduced by
WL5558 and adding native sorted-scan-scan support to the SPJ interface:
- AQP::Join_plan* argument to several methods can now be made 'const'
as AQP will not any longer modify the query plan.
- Join_plan::group_by_filesort_is_skippable() & ::order_by_filesort_is_skippable()
has become obsolete.
- Integrate optimizer code which sets up the pushed join version
of *read_first_record accessor functions into pick_table_access_method().
- Change optimizer such that pick_table_access_method() is called at
the end of the optimizer phase when the complete query plan is known
(Including pushed join execution)
modified:
mysql-test/suite/ndb/t/ndb_join_pushdown.inc
sql/abstract_query_plan.cc
sql/abstract_query_plan.h
sql/ha_ndbcluster.cc
sql/handler.cc
sql/handler.h
sql/sql_executor.cc
sql/sql_executor.h
sql/sql_optimizer.cc
sql/sql_select.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-23 12:15:24 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-29 10:38:31 +0000
@@ -1707,10 +1707,12 @@ join t1 as t3 on t3.a = t2.a and t3.b =
order by t1.c,t1.d,
t1.a, t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b`
select *
from t1
@@ -1867,10 +1869,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b,t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a`
select *
from t1
@@ -1910,10 +1914,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`
explain extended
select t1.a, t1.b, count(*)
@@ -5513,11 +5519,11 @@ and spj_counts_at_end.counter_name not i
'SCAN_BATCHES_RETURNED');
counter_name spj_counts_at_end.val - spj_counts_at_startup.val
CONST_PRUNED_RANGE_SCANS_RECEIVED 8
-LOCAL_TABLE_SCANS_SENT 256
+LOCAL_TABLE_SCANS_SENT 252
PRUNED_RANGE_SCANS_RECEIVED 27
RANGE_SCANS_RECEIVED 728
READS_RECEIVED 54
-TABLE_SCANS_RECEIVED 256
+TABLE_SCANS_RECEIVED 252
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
select new.variable_name, new.variable_value - old.variable_value
@@ -5527,9 +5533,9 @@ where new.variable_name = old.variable_n
order by new.variable_name;
variable_name new.variable_value - old.variable_value
NDB_PRUNED_SCAN_COUNT 8
-NDB_PUSHED_QUERIES_DEFINED 403
+NDB_PUSHED_QUERIES_DEFINED 398
NDB_PUSHED_QUERIES_DROPPED 7
-NDB_PUSHED_QUERIES_EXECUTED 547
+NDB_PUSHED_QUERIES_EXECUTED 545
NDB_SORTED_SCAN_COUNT 11
drop table server_counts_at_startup;
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result 2012-03-23 12:15:24 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_nobnl.result 2012-03-29 10:38:31 +0000
@@ -1704,10 +1704,12 @@ join t1 as t3 on t3.a = t2.a and t3.b =
order by t1.c,t1.d,
t1.a, t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b`
select *
from t1
@@ -1864,10 +1866,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b,t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a`
select *
from t1
@@ -1907,10 +1911,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`
explain extended
select t1.a, t1.b, count(*)
@@ -5508,11 +5514,11 @@ and spj_counts_at_end.counter_name not i
'SCAN_BATCHES_RETURNED');
counter_name spj_counts_at_end.val - spj_counts_at_startup.val
CONST_PRUNED_RANGE_SCANS_RECEIVED 8
-LOCAL_TABLE_SCANS_SENT 298
+LOCAL_TABLE_SCANS_SENT 294
PRUNED_RANGE_SCANS_RECEIVED 27
RANGE_SCANS_RECEIVED 734
READS_RECEIVED 54
-TABLE_SCANS_RECEIVED 298
+TABLE_SCANS_RECEIVED 294
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
select new.variable_name, new.variable_value - old.variable_value
@@ -5522,9 +5528,9 @@ where new.variable_name = old.variable_n
order by new.variable_name;
variable_name new.variable_value - old.variable_value
NDB_PRUNED_SCAN_COUNT 8
-NDB_PUSHED_QUERIES_DEFINED 411
+NDB_PUSHED_QUERIES_DEFINED 406
NDB_PUSHED_QUERIES_DROPPED 7
-NDB_PUSHED_QUERIES_EXECUTED 571
+NDB_PUSHED_QUERIES_EXECUTED 569
NDB_SORTED_SCAN_COUNT 11
drop table server_counts_at_startup;
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_none.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_none.result 2012-03-23 12:15:24 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_none.result 2012-03-29 10:38:31 +0000
@@ -1707,10 +1707,12 @@ join t1 as t3 on t3.a = t2.a and t3.b =
order by t1.c,t1.d,
t1.a, t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`c`,`test`.`t1`.`d`,`test`.`t1`.`a`,`test`.`t1`.`b`
select *
from t1
@@ -1867,10 +1869,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b,t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`,`test`.`t1`.`a`
select *
from t1
@@ -1910,10 +1914,12 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 Child of 't1' in pushed join@1
+1 SIMPLE t1 ALL NULL NULL NULL NULL 16 100.00 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t1.b,test.t1.c 1 100.00 NULL
Warnings:
+Note 9999 Table 't1' is not pushable, need filesort before joining child tables
+Note 9999 Can't push table 't3' as child of 't2', their dependency is 'const'
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t1` `t2` join `test`.`t1` `t3` where ((`test`.`t2`.`b` = `test`.`t1`.`c`) and (`test`.`t3`.`b` = `test`.`t1`.`c`) and (`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) order by `test`.`t1`.`b`
explain extended
select t1.a, t1.b, count(*)
@@ -5513,11 +5519,11 @@ and spj_counts_at_end.counter_name not i
'SCAN_BATCHES_RETURNED');
counter_name spj_counts_at_end.val - spj_counts_at_startup.val
CONST_PRUNED_RANGE_SCANS_RECEIVED 8
-LOCAL_TABLE_SCANS_SENT 254
+LOCAL_TABLE_SCANS_SENT 250
PRUNED_RANGE_SCANS_RECEIVED 27
RANGE_SCANS_RECEIVED 735
READS_RECEIVED 58
-TABLE_SCANS_RECEIVED 254
+TABLE_SCANS_RECEIVED 250
drop table spj_counts_at_startup;
drop table spj_counts_at_end;
select new.variable_name, new.variable_value - old.variable_value
@@ -5527,9 +5533,9 @@ where new.variable_name = old.variable_n
order by new.variable_name;
variable_name new.variable_value - old.variable_value
NDB_PRUNED_SCAN_COUNT 11
-NDB_PUSHED_QUERIES_DEFINED 403
+NDB_PUSHED_QUERIES_DEFINED 398
NDB_PUSHED_QUERIES_DROPPED 11
-NDB_PUSHED_QUERIES_EXECUTED 555
+NDB_PUSHED_QUERIES_EXECUTED 553
NDB_SORTED_SCAN_COUNT 11
drop table server_counts_at_startup;
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.inc'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2012-03-23 13:33:58 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.inc 2012-03-29 10:38:31 +0000
@@ -787,16 +787,24 @@ explain extended
y.a=x.d and y.b=x.b;
# Test ORDER BY expressions
-# Filesort on pushed joins are not possible as the
+# If it is a 'simple' order, i.e all order by's are plain column refs
+# to the first non-const table, the optimizer will (by heuristic)
+# make the first table 'ordered' beforing joining in the siblings.
+# This may involve presorting of the first table into intermediate storage.
+#
+# This will make this (parent-) table non-pushable as
# read of rows to be filesorted will also prefetch rows from pushed child
# operands. These are not cached by the filesort buffer mechanisnm and are
# effectively lost.
-# With pushed joins we either has to:
+#
+# Non simple ordering will always writte entire resultset to temp.
+# table and filesort that -> No extra push restrictions on these.
+#
+# Integrating pushed joins we either has to:
# 1) find a suitable ordered index which we can create an ordered indexscan on
# (-> joinType() -> JT_NEXT, or type: 'index' w/ explain)
# or:
-# 2) Use a temporary result file for the result, which is then filesort'ed
-# (-> 'Using temporary; Using filesort')
+# 2) Reject pushing of the this parent table.
#
# Comment1: As 'order by' correctness is part of what we want to test,
# '--sorted_result' is *not* specified. Instead we aim at
@@ -806,7 +814,7 @@ explain extended
# to get a deterministic sorting order wo/ '--sorted_result'
#
-## pushed join w/ 'simple order' on non_PK - Need temp table + filesort
+## Non-pushed join w/ 'simple order' on non_PK - Presorts parent table.
explain extended
select *
from t1
@@ -882,7 +890,7 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a desc,t1.b desc;
-## PK column in incorrect order, -> filesort to tempfile
+## Simple-PK column in incorrect order, -> Presort parent table, (no-push)
explain extended
select *
from t1
@@ -906,7 +914,7 @@ join t1 as t2 on t2.a = t1.b and t2.b =
join t1 as t3 on t3.a = t2.a and t3.b = t2.b
order by t1.a;
-# PK columns not including first part -> filesort to tempfile
+# PK columns not including first part -> Presort parent table, (no-push)
explain extended
select *
from t1
@@ -3498,7 +3506,10 @@ connection ddl;
drop table t1;
###
-# Test that sorted scan with sub scan is *not* pushed.
+# Sorted scan with sub scan used to not being pushable.
+# However since v7.2.6(?) we support this by enforcing
+# batchRowSize=1 for the parent table in the scan-scan.
+# (At the cost of more roundrtrips)
###
connection ddl;
create table t1 (pk int primary key, a int, b int) engine=ndb;
@@ -3517,7 +3528,7 @@ insert into t1 values (8,10,20);
insert into t1 values (9,10,10);
-# Results would be sorted wrongly if pushed.
+# This sorted scan-scan is pushed since V7.2.6
explain extended select x1.pk,x1.a,x1.b from t1 as x1
join t1 as x2 on x1.a=x2.b
join t1 as x3 on x2.a=x3.b
@@ -3591,6 +3602,8 @@ drop table t;
# bug#57601 'Optimizer is overly eager to request ordered access.'
# When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
# which required result to be read as an ordered index access
+# Note: Before V7.2.6, and as a result of WL5558, such 'turn of sorting'
+# is not expected to happen any more.
#######
connection ddl;
create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc 2012-03-23 13:33:58 +0000
+++ b/sql/abstract_query_plan.cc 2012-03-29 10:38:31 +0000
@@ -482,5 +482,46 @@ namespace AQP
return get_join_tab()->next_select == sub_select_cache;
}
+ /**
+ Check if this table will be presorted to an intermediate record storage
+ before it is joined with its siblings.
+ */
+ bool Table_access::filesort_before_join() const
+ {
+ if (m_access_type == AT_PRIMARY_KEY ||
+ m_access_type == AT_UNIQUE_KEY)
+ {
+ return false;
+ }
+
+ const JOIN_TAB* const join_tab= get_join_tab();
+ JOIN* const join= join_tab->join;
+
+ /**
+ Table will be presorted before joining with child tables, if:
+ 1) This is the first non-const table
+ 2) There are more tables to be joined
+ 3) It is not already decide to write entire join result to temp.
+ 4a) The GROUP BY is 'simple' and does not match an orderd index
+ 4b) The ORDER BY is 'simple' and does not match an orderd index
+
+ A 'simple' order/group by contain only column references to
+ the first non-const table
+ */
+ if (join_tab == join->join_tab+join->const_tables && // First non-const table
+ join->const_tables < join->tables) // There are more tables
+ {
+ if (join->need_tmp)
+ return false;
+ else if (join->group_list && join->simple_group)
+ return (join->ordered_index_usage!=JOIN::ordered_index_group_by);
+ else if (join->order && join->simple_order)
+ return (join->ordered_index_usage!=JOIN::ordered_index_order_by);
+ else
+ return false;
+ }
+ return false;
+ }
+
};
// namespace AQP
=== modified file 'sql/abstract_query_plan.h'
--- a/sql/abstract_query_plan.h 2012-03-23 13:33:58 +0000
+++ b/sql/abstract_query_plan.h 2012-03-29 10:38:31 +0000
@@ -199,6 +199,8 @@ namespace AQP
bool uses_join_cache() const;
+ bool filesort_before_join() const;
+
private:
/** Backref. to the Join_plan which this Table_access is part of */
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2012-03-23 13:33:58 +0000
+++ b/sql/ha_ndbcluster.cc 2012-03-29 10:38:31 +0000
@@ -813,8 +813,7 @@ SHOW_VAR ndb_status_index_stat_variables
#ifndef NDB_WITHOUT_JOIN_PUSHDOWN
static int ndbcluster_make_pushed_join(handlerton *,
THD*,
- const AQP::Join_plan*,
- uint*);
+ const AQP::Join_plan*);
#endif
/*
@@ -14829,12 +14828,10 @@ ha_ndbcluster::read_multi_range_fetch_ne
static
int ndbcluster_make_pushed_join(handlerton *hton,
THD* thd,
- const AQP::Join_plan* plan,
- uint* pushed)
+ const AQP::Join_plan* plan)
{
DBUG_ENTER("ndbcluster_make_pushed_join");
(void)ha_ndb_ext; // prevents compiler warning.
- *pushed= 0;
if (THDVAR(thd, join_pushdown))
{
@@ -14871,7 +14868,6 @@ int ndbcluster_make_pushed_join(handlert
handler->print_error(error, MYF(0));
DBUG_RETURN(error);
}
- *pushed= handler->number_of_pushed_joins();
}
}
}
=== modified file 'sql/ha_ndbcluster_push.cc'
--- a/sql/ha_ndbcluster_push.cc 2012-03-23 12:15:24 +0000
+++ b/sql/ha_ndbcluster_push.cc 2012-03-29 10:38:31 +0000
@@ -523,6 +523,14 @@ ndb_pushed_builder_ctx::is_pushable_with
DBUG_RETURN(false);
}
+ if (root->filesort_before_join())
+ {
+ EXPLAIN_NO_PUSH("Table '%s' is not pushable, "
+ "need filesort before joining child tables",
+ root->get_table()->alias);
+ DBUG_RETURN(false);
+ }
+
/**
* Past this point we know at least root to be pushable as parent
* operation. Search remaining tables appendable if '::is_pushable_as_child()'
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2012-03-23 13:33:58 +0000
+++ b/sql/handler.cc 2012-03-29 10:38:31 +0000
@@ -4348,7 +4348,6 @@ int ha_table_exists_in_engine(THD* thd,
struct st_make_pushed_join_args
{
const AQP::Join_plan* plan; // Query plan provided by optimizer
- uint pushed; // #operations which was pushed.
int err; // Error code to return.
};
@@ -4360,26 +4359,23 @@ static my_bool make_pushed_join_handlert
if (hton && hton->make_pushed_join)
{
- uint pushed= 0;
- const int error= hton->make_pushed_join(hton, thd, vargs->plan, &pushed);
+ const int error= hton->make_pushed_join(hton, thd, vargs->plan);
if (unlikely(error))
{
vargs->err = error;
return TRUE;
}
- vargs->pushed+= pushed;
}
return FALSE;
}
-int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan, uint* pushed)
+int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan)
{
DBUG_ENTER("ha_make_pushed_joins");
- st_make_pushed_join_args args= {plan, 0, 0};
+ st_make_pushed_join_args args= {plan, 0};
plugin_foreach(thd, make_pushed_join_handlerton,
MYSQL_STORAGE_ENGINE_PLUGIN, &args);
- *pushed = args.pushed;
- DBUG_PRINT("exit", ("pushed cnt: %d, error: %d", args.pushed, args.err));
+ DBUG_PRINT("exit", ("error: %d", args.err));
DBUG_RETURN(args.err);
}
#endif
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2012-03-23 13:33:58 +0000
+++ b/sql/handler.h 2012-03-29 10:38:31 +0000
@@ -863,8 +863,7 @@ struct handlerton
#ifndef MCP_WL4784
int (*make_pushed_join)(handlerton *hton, THD* thd,
- const AQP::Join_plan* plan,
- uint* pushed);
+ const AQP::Join_plan* plan);
#endif
#ifndef MCP_GLOBAL_SCHEMA_LOCK
int (*global_schema_func)(THD* thd, bool lock, void* args);
@@ -2790,7 +2789,7 @@ int ha_release_savepoint(THD *thd, SAVEP
#ifndef MCP_WL4784
/* Build pushed joins in handlers implementing this feature */
-int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan, uint* pushed);
+int ha_make_pushed_joins(THD *thd, const AQP::Join_plan* plan);
#endif
/* these are called by storage engines */
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-03-23 13:33:58 +0000
+++ b/sql/sql_executor.cc 2012-03-29 10:38:31 +0000
@@ -3386,10 +3386,11 @@ join_read_next_same_or_null(READ_RECORD
@param tab Table reference to put access method
*/
-#ifndef MCP_WL4784
void
-pick_table_access_method(JOIN_TAB *tab, int *active_pushed_joins)
+pick_table_access_method(JOIN_TAB *tab)
{
+
+#ifndef MCP_WL4784
uint pushed_joins= tab->table->file->number_of_pushed_joins();
/**
@@ -3397,17 +3398,14 @@ pick_table_access_method(JOIN_TAB *tab,
*/
if (pushed_joins > 0)
{
- active_pushed_joins--;
- if (tab->table->file->root_of_pushed_join() == tab->table)
+ if (tab->table->file->root_of_pushed_join() != tab->table)
{
- *active_pushed_joins += pushed_joins;
- }
- else
- {
- // Is child of a pushed join operation:
- // Replace 'read_key' access with its linked counterpart
- // ... Which is effectively a NOOP as the row is read
- // as part of the linked operation
+ /*
+ Is child of a pushed join operation:
+ Replace access functions with its linked counterpart.
+ ... Which is effectively a NOOP as the row is already fetched
+ together with the root of the linked operation.
+ */
DBUG_ASSERT(tab->type != JT_REF_OR_NULL);
tab->read_first_record= join_read_linked_first;
tab->read_record.read_record= join_read_linked_next;
@@ -3416,15 +3414,15 @@ pick_table_access_method(JOIN_TAB *tab,
}
}
- // Already set to some non-default value in sql_select.cc
- // TODO: Move these settings into pick_table_access_method() also
+ /*
+ Already set to some non-default value in sql_select.cc
+ TODO: Move these settings into pick_table_access_method() also
+ */
else if (tab->read_first_record != NULL)
return;
-#else
-void
-pick_table_access_method(JOIN_TAB *tab)
-{
+ // Fall through to set default access functions:
+
#endif // MCP_WL4784
switch (tab->type)
=== modified file 'sql/sql_executor.h'
--- a/sql/sql_executor.h 2012-03-23 13:33:58 +0000
+++ b/sql/sql_executor.h 2012-03-29 10:38:31 +0000
@@ -125,12 +125,7 @@ int report_error(TABLE *table, int error
int safe_index_read(JOIN_TAB *tab);
SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length,
SORT_FIELD *sortorder);
-
-#ifndef MCP_WL4784
-void pick_table_access_method(JOIN_TAB *tab, int *active_pushed_joins);
-#else
void pick_table_access_method(JOIN_TAB *tab);
-#endif
int join_read_const_table(JOIN_TAB *tab, POSITION *pos);
void join_read_key_unlock_row(st_join_table *tab);
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-23 13:33:58 +0000
+++ b/sql/sql_optimizer.cc 2012-03-29 10:38:31 +0000
@@ -107,11 +107,6 @@ only_eq_ref_tables(JOIN *join, ORDER *or
table_map *cached_eq_ref_tables, table_map
*eq_ref_tables);
-#ifndef MCP_WL4784
-static int make_pushed_join(THD *thd, JOIN *join);
-#endif
-
-
/**
global select optimisation.
@@ -948,18 +943,29 @@ JOIN::optimize()
}
#ifndef MCP_WL4784
- if (make_pushed_join(thd, this))
- DBUG_RETURN(1);
+ /**
+ * Push joins to handler(s) whenever possible.
+ * The handlers will inspect the QEP through the
+ * AQP (Abstract Query Plan), and extract from it
+ * whatewer it might implement of pushed execution.
+ * It is the responsibility if the handler to store any
+ * information it need for later execution of pushed queries.
+ *
+ * Currently this is only supported by NDB.
+ */
+ {
+ const AQP::Join_plan plan(this);
+ if (ha_make_pushed_joins(thd, &plan))
+ DBUG_RETURN(1);
+ }
+ /**
+ * Set up access functions for the tables as
+ * required by the selected access type.
+ */
+ for (uint i= const_tables; i < tables; i++)
{
- int active_pushed_joins= 0;
- for (uint i= this->const_tables; i < this->tables; i++)
- {
- JOIN_TAB *const tab= this->join_tab+i;
- pick_table_access_method (tab, &active_pushed_joins);
- }
- /* Ensure all (if any) pushed joins were processed. */
- DBUG_ASSERT(!active_pushed_joins);
+ pick_table_access_method (&join_tab[i]);
}
#endif
@@ -994,56 +1000,6 @@ setup_subq_exit:
DBUG_RETURN(0);
}
-#ifndef MCP_WL4784
-/**
- Push join to handler, if possible. Currently this is only supported by
- NDB.
-
- @note If it happens so that handler can't do ordering this function
- will force tmp table creation and resolving ORDER/GROUP BY by filesort.
-
- @returns
- 0 ok, join isn't pushed or pushed without errors.
- error handler's error otherwise
-*/
-static int
-make_pushed_join(THD *thd, JOIN *join)
-{
- // Let handler extract whatever it might implement of pushed joins
- AQP::Join_plan plan(join);
- uint pushed;
-
- const int error= ha_make_pushed_joins(thd, &plan, &pushed);
- if (unlikely(error))
- return error;
- if (pushed==0)
- return 0; // Didn't push anything
-
- /* If we just pushed a join containing an ORDER BY and/or a GROUP BY clause,
- * we have to ensure that we either can skip the sort by scanning an ordered index,
- * or write to a temp. table later being filesorted.
- */
- if (join->const_tables < join->tables &&
- join->join_tab[join->const_tables].table->file->number_of_pushed_joins() > 0)
- {
- if (join->group_list && join->simple_group &&
- join->ordered_index_usage!=JOIN::ordered_index_group_by)
- {
- join->need_tmp= 1;
- join->simple_order= join->simple_group= 0;
- }
- else if (join->order && join->simple_order &&
- join->ordered_index_usage!=JOIN::ordered_index_order_by)
- {
- join->need_tmp= 1;
- join->simple_order= join->simple_group= 0;
- }
- }
-
- return 0;
-}
-#endif // MCP_WL4784
-
/**
Set NESTED_JOIN::counter=0 in all nested joins in passed list.
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk-cluster branch (ole.john.aske:3459 to 3460) | Ole John Aske | 29 Mar |
