#At file:///data0/martin/bzrroot/wl3724/n-mr-o-t/ based on revid:jorgen.loland@stripped
3299 Martin Hansson 2011-03-16
WL#3724: Short-Cutting Join Execution: Speeding up star queries
added:
mysql-test/r/shortcut.result
mysql-test/t/shortcut.test
modified:
mysql-test/r/func_in_icp.result
mysql-test/r/func_in_icp_mrr.result
mysql-test/r/func_in_mrr.result
mysql-test/r/func_in_mrr_cost.result
mysql-test/r/func_in_none.result
mysql-test/r/greedy_optimizer.result
mysql-test/r/join.result
mysql-test/r/join_cache_jcl1.result
mysql-test/r/join_cache_jcl2.result
mysql-test/r/join_cache_jcl3.result
mysql-test/r/join_cache_jcl4.result
mysql-test/r/join_nested.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_none.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/r/subselect_innodb.result
mysql-test/t/subselect_innodb.test
sql/item.cc
sql/item.h
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/r/func_in_icp.result'
--- a/mysql-test/r/func_in_icp.result 2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_icp.result 2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
SELECT STRAIGHT_JOIN * FROM t3
JOIN t1 ON t3.a=t1.a
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
SELECT STRAIGHT_JOIN
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
=== modified file 'mysql-test/r/func_in_icp_mrr.result'
--- a/mysql-test/r/func_in_icp_mrr.result 2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_icp_mrr.result 2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
SELECT STRAIGHT_JOIN * FROM t3
JOIN t1 ON t3.a=t1.a
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
SELECT STRAIGHT_JOIN
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
=== modified file 'mysql-test/r/func_in_mrr.result'
--- a/mysql-test/r/func_in_mrr.result 2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_mrr.result 2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
SELECT STRAIGHT_JOIN * FROM t3
JOIN t1 ON t3.a=t1.a
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
SELECT STRAIGHT_JOIN
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
=== modified file 'mysql-test/r/func_in_mrr_cost.result'
--- a/mysql-test/r/func_in_mrr_cost.result 2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_mrr_cost.result 2011-03-16 14:01:21 +0000
@@ -372,7 +372,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
SELECT STRAIGHT_JOIN * FROM t3
JOIN t1 ON t3.a=t1.a
@@ -390,7 +390,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
SELECT STRAIGHT_JOIN
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
=== modified file 'mysql-test/r/func_in_none.result'
--- a/mysql-test/r/func_in_none.result 2011-01-14 08:20:08 +0000
+++ b/mysql-test/r/func_in_none.result 2011-03-16 14:01:21 +0000
@@ -371,7 +371,7 @@ JOIN t4 WHERE t4.a IN (t1.b, t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 7 Range checked for each record (index map: 0x1)
SELECT STRAIGHT_JOIN * FROM t3
JOIN t1 ON t3.a=t1.a
@@ -389,7 +389,7 @@ WHERE t3.a=t1.a AND t3.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Shortcut(t3)
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 7 Using where; Using index
SELECT STRAIGHT_JOIN
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result 2011-02-17 11:38:07 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2011-03-16 14:01:21 +0000
@@ -175,8 +175,8 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 289.430791
@@ -198,8 +198,8 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 289.430791
@@ -308,8 +308,8 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -331,8 +331,8 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -355,7 +355,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Shortcut(t3)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -375,7 +375,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Shortcut(t3)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -447,8 +447,8 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 289.430791
@@ -470,8 +470,8 @@ id select_type table type possible_keys
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using join buffer (BNL, incremental buffers)
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 289.430791
@@ -720,8 +720,8 @@ EXPLAIN select t1.c11 from t1, t2, t3, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -743,8 +743,8 @@ EXPLAIN select t1.c11 from t7, t6, t5, t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index; Shortcut(t1)
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index; Shortcut(t1)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -767,7 +767,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Shortcut(t3)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
@@ -787,7 +787,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
-1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where; Shortcut(t3)
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (BNL, incremental buffers)
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (BNL, incremental buffers)
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result 2010-12-16 17:38:26 +0000
+++ b/mysql-test/r/join.result 2011-03-16 14:01:21 +0000
@@ -848,7 +848,7 @@ explain select * from t1, t2, t3 where t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index; Shortcut(t1)
We expect rnd_next=5, and read_key must be 0 because of short-cutting:
show status like 'Handler_read%';
Variable_name Value
=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
-1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
-1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Shortcut(t1)
+1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index; Shortcut(t1)
+1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Shortcut(t4)
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Shortcut(t7)
1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, regular buffers)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Shortcut(t1)
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.b 1
-1 SIMPLE t3 ref idx idx 5 test.t1.d 1
-1 SIMPLE t4 ref idx idx 5 test.t1.c 1
+1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Shortcut(t1)
+1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Shortcut(t1)
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a b c d e f g
@@ -1866,8 +1866,8 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 349 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where
-1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Shortcut(t1)
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Shortcut(t1)
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND
=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result 2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl2.result 2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
-1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
-1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Shortcut(t1)
+1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index; Shortcut(t1)
+1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Shortcut(t4)
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Shortcut(t7)
1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Shortcut(t1)
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.b 1
-1 SIMPLE t3 ref idx idx 5 test.t1.d 1
-1 SIMPLE t4 ref idx idx 5 test.t1.c 1
+1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Shortcut(t1)
+1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Shortcut(t1)
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a b c d e f g
@@ -1866,8 +1866,8 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 349 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where
-1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Shortcut(t1)
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Shortcut(t1)
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND
=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result 2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl3.result 2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
-1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
-1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Shortcut(t1)
+1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index; Shortcut(t1)
+1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Shortcut(t4)
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Shortcut(t7)
1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, regular buffers)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Shortcut(t1)
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.b 1
-1 SIMPLE t3 ref idx idx 5 test.t1.d 1
-1 SIMPLE t4 ref idx idx 5 test.t1.c 1
+1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Shortcut(t1)
+1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Shortcut(t1)
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a b c d e f g
@@ -1866,8 +1866,8 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 349 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where
-1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Shortcut(t1)
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Shortcut(t1)
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND
=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result 2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/join_cache_jcl4.result 2011-03-16 14:01:21 +0000
@@ -1309,10 +1309,10 @@ id select_type table type possible_keys
1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where
1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
-1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
-1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
-1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Shortcut(t1)
+1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index; Shortcut(t1)
+1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Shortcut(t4)
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Shortcut(t7)
1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
@@ -1614,7 +1614,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b I
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Shortcut(t1)
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
@@ -1816,8 +1816,8 @@ where t2.b=t1.b and t3.d=t1.d and t4.c=t
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
1 SIMPLE t2 ref idx idx 5 test.t1.b 1
-1 SIMPLE t3 ref idx idx 5 test.t1.d 1
-1 SIMPLE t4 ref idx idx 5 test.t1.c 1
+1 SIMPLE t3 ref idx idx 5 test.t1.d 1 Shortcut(t1)
+1 SIMPLE t4 ref idx idx 5 test.t1.c 1 Shortcut(t1)
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a b c d e f g
@@ -1866,8 +1866,8 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 349 Using where
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.id3 1 Using where
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.id4 1 Using where
-1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1
-1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 8 test.t1.id2 1 Shortcut(t1)
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 16 test.t1.id1,test.t1.id2 1 Using where; Shortcut(t1)
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result 2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/join_nested.result 2011-03-16 14:01:21 +0000
@@ -1465,7 +1465,7 @@ id select_type table type possible_keys
1 SIMPLE t3 ref a a 5 test.t2.b X
1 SIMPLE t4 ref a a 5 test.t3.b X
1 SIMPLE t6 ref a a 5 test.t4.b X
-1 SIMPLE t5 ref a a 5 test.t3.b X
+1 SIMPLE t5 ref a a 5 test.t3.b X Shortcut(t3)
drop table t0, t1, t2, t3, t4, t5, t6, t7;
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1733,7 +1733,7 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1
1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
-1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
+1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index; Shortcut(t1)
SELECT COUNT(*)
FROM ((t2 JOIN t1 ON t2.package_id = t1.id)
JOIN t3 ON t3.package_id = t1.id)
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2011-01-31 11:56:15 +0000
+++ b/mysql-test/r/myisam_icp.result 2011-03-16 14:01:21 +0000
@@ -511,7 +511,7 @@ WHERE table3.col_int_key != SOME ( SELEC
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2 Shortcut(table3)
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2011-01-31 11:56:15 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2011-03-16 14:01:21 +0000
@@ -510,7 +510,7 @@ WHERE table3.col_int_key != SOME ( SELEC
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table3 ALL col_varchar_key NULL NULL NULL 19 Using where
1 PRIMARY table2 ref col_varchar_key col_varchar_key 4 test.table3.col_varchar_key 2 Using index
-1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2
+1 PRIMARY table1 ref col_int_key col_int_key 5 test.table3.col_int_nokey 2 Shortcut(table3)
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT table1.col_int_nokey
FROM t1 AS table1 STRAIGHT_JOIN (
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2011-03-16 14:01:21 +0000
@@ -498,7 +498,7 @@ EXPLAIN select t1.gid, t2.sid, t3.uid fr
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index; Shortcut(t2)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
@@ -507,7 +507,7 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid fr
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index; Shortcut(t2)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2011-02-07 09:46:53 +0000
+++ b/mysql-test/r/order_by_none.result 2011-03-16 14:01:21 +0000
@@ -497,7 +497,7 @@ EXPLAIN select t1.gid, t2.sid, t3.uid fr
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index; Shortcut(t2)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
@@ -506,7 +506,7 @@ EXPLAIN SELECT t1.gid, t2.sid, t3.uid fr
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL PRIMARY,uid NULL NULL NULL 6 Using temporary; Using filesort
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.gid 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 test.t2.uid 1 Using where; Using index; Shortcut(t2)
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 6 Using temporary; Using filesort
=== added file 'mysql-test/r/shortcut.result'
--- a/mysql-test/r/shortcut.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/shortcut.result 2011-03-16 14:01:21 +0000
@@ -0,0 +1,854 @@
+#
+# wl3724: Short-Cutting Join Execution: Speeding up star queries
+#
+#
+# Test 1. Basic tests.
+#
+# Test 1a. Tests of EXPLAIN.
+#
+CREATE TABLE t1 (
+a INT,
+b INT
+);
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 (
+a INT,
+KEY( a )
+);
+INSERT INTO t2 VALUES (1), (1), (1), (1), (1);
+CREATE TABLE t3 (
+b INT,
+KEY( b )
+);
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1);
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+b a
+# Test that the short-cut does not cross an outer join boundary
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2, t3 ) USING( b );
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index
+1 SIMPLE t2 index NULL a 5 NULL 5 Using index
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON a = b ) USING( b );
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index
+1 SIMPLE t2 ref a a 5 test.t3.b 2 Using index
+# Test that the optimization is robust wrt reordering of tables
+EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t1, t3, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t2, t1, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t2, t3, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t3, t1, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref b b 5 test.t1.b 2 Using index; Shortcut(t1)
+DROP TABLE t1, t2, t3;
+# Test that short-cuts show up in EXPLAIN properly with "Using index"
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT KEY );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t3 ( c INT KEY );
+INSERT INTO t3 VALUES (1), (2), (3);
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index; Shortcut(t1)
+# Test that we don't take a short-cut if we have successfully read a
+# row already.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+a b c
+2 2 2
+3 3 3
+DROP TABLE t1, t2, t3;
+#
+# Test 1b.
+# Tests that short-cuts work properly with join buffers.
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t2key ( b INT KEY );
+INSERT INTO t2key VALUES (1), (2), (3);
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3 VALUES (1, -1), (2, 1), (3, 1);
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a b c d
+2 2 2 1
+3 3 3 1
+SET @@optimizer_join_cache_level = 0;
+# Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a b c d
+2 2 2 1
+3 3 3 1
+SET @@optimizer_join_cache_level = DEFAULT;
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2key eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+a b c d
+2 2 2 1
+3 3 3 1
+SET @@optimizer_join_cache_level = 0;
+# Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2key eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+a b c d
+2 2 2 1
+3 3 3 1
+SET @@optimizer_join_cache_level = DEFAULT;
+DROP TABLE t1, t2, t2key, t3;
+# Test that we reject a short-cut properly. We have a cartesian product
+# of a table and the result of an outer join. The optimizer places the
+# table between the outer and inner table of the outer join and hence
+# there is a short-cut from the inner table to the outer table. This
+# short-cut should be rejected.
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (2), (3);
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+a a a
+1 1 1
+2 1 1
+3 1 1
+1 2 2
+2 2 2
+3 2 2
+DROP TABLE t1, t2, t3;
+# Test 2
+# That the optimization actually works, i.e. that unneccesary reads are
+# indeed short-cut.
+#
+# Test 2a: We have two tables between giving and receiving end of short-cut:
+# t2 and t3.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index condition
+1 SIMPLE t3 ref b b 5 test.t2.a 2 Using where; Using index
+1 SIMPLE t4 ref b b 5 test.t1.b 4 Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+a b b b b
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 4
+Handler_read_last 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 3
+DROP TABLE t1, t2, t3, t4;
+# Test 2b
+# Short-cuts followed by successful keys. This tests that we perform the
+# optimal number of reads after the short-cut is taken without missing
+# rows.
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (2), (3), (4), (4), (4), (5);
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (3), (5), (-1), (-1), (-1), (-1), (-1), (-1);
+FLUSH STATUS;
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref a a 5 test.t1.a 2 Using index; Shortcut(t1)
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+a
+1
+3
+5
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 10
+Handler_read_last 0
+Handler_read_next 6
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 6
+DROP TABLE t1, t2, t3;
+# Test 2c
+# Test that we don't erroneously take a short-cut at the end of a table
+# scan.
+set optimizer_join_cache_level = 0;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (1), (2);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (3);
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using index
+1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Shortcut(t1)
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+a
+1
+1
+DROP TABLE t1, t2, t3;
+SET @@optimizer_join_cache_level = DEFAULT;
+#
+# Test 3.
+# Test for outer joins. We may not take short-cuts across outer join
+# boundaries. An outer join boundary goes before the first inner table for an
+# outer join.
+#
+# Test 3a.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+CREATE TABLE t5 ( b INT, KEY( b ) );
+INSERT INTO t5 VALUES (1), (1), (1), (1), (1), (1), (1), (1);
+INSERT INTO t5 SELECT * FROM t5;
+# Test of two outer join boundaries in one join.
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING ( a )
+LEFT JOIN t4 ON t1.a = t4.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ref a a 5 test.t1.a 2
+1 SIMPLE t3 ref b b 5 test.t2.a 2 Using index
+1 SIMPLE t4 ref b b 5 test.t1.a 4 Using index
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING(a)
+LEFT JOIN (t4 JOIN t5 USING( b )) ON t1.a = t4.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ref a a 5 test.t1.a 2
+1 SIMPLE t3 ref b b 5 test.t2.a 2 Using index
+1 SIMPLE t4 ref b b 5 test.t1.a 4 Using index
+1 SIMPLE t5 ref b b 5 test.t4.b 2 Using index
+DROP TABLE t1, t2, t3, t4, t5;
+# Test 3b.
+# Tests that we do not miss any data due to short-cutting.
+CREATE TABLE t1 ( a INT, b INT );
+CREATE TABLE t2 ( a INT, b INT, PRIMARY KEY (a,b) );
+CREATE TABLE t3 ( a INT, b INT, PRIMARY KEY (a,b) );
+INSERT INTO t1 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+INSERT INTO t3 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+# Test of the short-cut detection algorithm. When traversing the
+# push-down conditions the algorithm will first find that the
+# prospective short-cut t3->t1, but this must be invalidated due to the
+# later found dependency t3->t2.
+EXPLAIN
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b,test.t1.b 1 Using index
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+a b a b a b
+1 1 1 1 1 1
+2 1 2 2 2 1
+1 3 1 1 1 3
+# Test of internal representation of pushdown conditions.
+# The join conditions will be encoded in ref access, while the WHERE
+# condition remains in the pushdown condition.
+EXPLAIN
+SELECT *
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a
+WHERE t3.b + 1 = t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index
+# Short-cut is actually applicable in this case, thanks to equality
+# propagation. The WHERE condition t3.a + 1 = t2.a is rewritten into
+# t1.b + 1 = t1.a, and hence we have an optimizable star query.
+EXPLAIN
+SELECT *
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a
+WHERE t3.a + 1 = t2.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t1.b 1 Using index; Shortcut(t1)
+DROP TABLE t1, t2, t3;
+# Test 3c
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1), (1), (1);
+CREATE TABLE t2 ( a INT, KEY (a), b INT, c INT );
+INSERT INTO t2 VALUES (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1);
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (3);
+CREATE TABLE t4 ( a INT, KEY (a), b INT );
+INSERT INTO t4 VALUES (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
+INSERT INTO t4 SELECT * FROM t4;
+#
+# Test that the short-cut t4->t1 is rejected in favor of t4->t2.
+#
+EXPLAIN
+SELECT STRAIGHT_JOIN *
+FROM t1 JOIN t2 ON t1.a = t2.a
+JOIN t3 ON t2.b = t3.a
+JOIN t4 ON t1.a = t4.a AND t2.c = t4.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 2 Using where
+1 SIMPLE t3 ref a a 5 test.t2.b 2 Using index
+1 SIMPLE t4 ref a a 5 test.t1.a 2 Using where; Shortcut(t2)
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 4. More tests of reordering of tables.
+#
+CREATE TABLE t1( a INT, b INT );
+INSERT INTO t1 VALUES ( 2, 2 );
+INSERT INTO t1 VALUES ( 2, 2 );
+CREATE TABLE t2( a INT PRIMARY KEY );
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 2 );
+CREATE TABLE t3( a INT, INDEX( a ) );
+INSERT INTO t3 VALUES ( 6 );
+INSERT INTO t3 VALUES ( 5 );
+INSERT INTO t3 VALUES ( 2 );
+EXPLAIN
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index; Shortcut(t1)
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+a
+2
+2
+EXPLAIN
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index; Shortcut(t1)
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+a a b a
+2 2 2 2
+2 2 2 2
+DROP TABLE t1, t2, t3;
+#
+# Test 5.
+# Tests that a short-cut does not cross a plan node that is using join
+# buffering.
+#
+# Test 5a.
+#
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a ), KEY( b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+CREATE TABLE t3 ( a INT, KEY( a ) );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( a INT, KEY( a ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a,b NULL NULL NULL 5 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 ref a a 5 test.t2.b 4 Using index
+1 SIMPLE t4 ref a a 5 test.t1.c 9 Using index
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a c a b a a
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ALL a,b NULL NULL NULL 5 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 ref a a 5 test.t2.b 4 Using index
+1 SIMPLE t4 ref a a 5 test.t1.c 9 Using index
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a c a b a a
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 410
+Handler_read_last 0
+Handler_read_next 400
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 18
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 5b.
+#
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+CREATE TABLE t2 ( a INT, b INT, KEY( a, b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+FLUSH STATUS;
+# Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 ref a a 5 test.t1.a 1 Using index
+1 SIMPLE t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+a c a b a a
+SHOW STATUS LIKE 'handler_read_%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 2
+Handler_read_last 0
+Handler_read_next 5
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 35
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 6. Test of short-cuts in conjunction with outer join.
+#
+# Test 6a. Test of outer join detection.
+# In this case the execution order is t1, t2, t3, t4.
+# t2, t3 and t4 will form an 'outer join nest' with which t1 is left
+# outer joined. But (t3, t2) is left joined with t4. Since t4 is just one
+# table, however, the 'outer join nest' of t4 is only implicitly
+# represented in the query plan. The only way to detect it is by looking
+# at t4's first_inner pointer, which would be null for the last inner table
+# in an outer join nest. But since it points to itseld, t4 is in its own
+# nest. But there is no NESTED_JOIN to represent it, since it follows the
+# 'normal' left-deep tree representation.
+#
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (d INT, e INT, f INT);
+CREATE TABLE t3 (g INT, h INT, i INT);
+CREATE TABLE t4 (j INT, k INT, l INT);
+INSERT INTO t1 VALUES (3,1,0), (2,2,0), (3,3,0);
+INSERT INTO t2 VALUES (1,1,0), (2,2,0);
+INSERT INTO t3 VALUES (3,2,0), (6,2,0), (6,1,0);
+INSERT INTO t4 VALUES (0,2,0), (1,2,0);
+EXPLAIN
+SELECT a, b, c, d, e, f, g, h, i, j, k, l
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
+1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+SELECT *
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+a b c g h i d e f j k l
+2 2 0 3 2 0 2 2 0 0 2 0
+2 2 0 6 2 0 2 2 0 0 2 0
+2 2 0 6 1 0 2 2 0 0 2 0
+2 2 0 3 2 0 2 2 0 1 2 0
+2 2 0 6 2 0 2 2 0 1 2 0
+2 2 0 6 1 0 2 2 0 1 2 0
+3 1 0 3 2 0 1 1 0 NULL NULL NULL
+3 1 0 6 2 0 1 1 0 NULL NULL NULL
+3 1 0 6 1 0 1 1 0 NULL NULL NULL
+3 3 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL
+DROP TABLE t1, t2, t3, t4;
+#
+# Test 6b.
+# We may take short-cuts across or inside an outer join, as long as we
+# don't fail due to an outer join predicate. It goes as follows.
+#
+# - We may take a short-cut across a nested outer join.
+# - We may take short-cuts inside a nested inner join sequence.
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2 ( b INT, KEY ( b ) );
+INSERT INTO t2 VALUES (1), (2), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3 ( c INT, KEY( c ) );
+INSERT INTO t3 VALUES (1), (2), (3), (4), (6), (7), (8), (9), (10);
+CREATE TABLE t4 ( d INT, KEY( d ) );
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (10);
+INSERT INTO t4 VALUES (10), (10), (10), (10);
+CREATE TABLE t5 ( e INT, KEY( e ) );
+INSERT INTO t5 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+# Outer join followed by a table that is inner joined with the first.
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t1.a 2 Using index; Shortcut(t1)
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+a b c
+1 1 1
+2 2 2
+3 NULL 3
+4 4 4
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+10 10 10
+# Short-cut across a nested outer join
+EXPLAIN
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t1.a 2 Using index
+1 SIMPLE t4 ref d d 5 test.t1.a 2 Using index; Shortcut(t1)
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+a b c d
+1 1 1 1
+2 2 2 2
+4 4 4 4
+5 5 NULL 5
+6 6 6 6
+7 7 7 7
+8 8 8 8
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+# Short-cut across a nested outer join with a nested inner join
+EXPLAIN
+SELECT * FROM t1
+JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+JOIN t5 ON a = e;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using where; Using index
+1 SIMPLE t3 ref c c 5 test.t2.b 2 Using where; Using index
+1 SIMPLE t4 ref d d 5 test.t3.c 2 Using where; Using index
+1 SIMPLE t5 ref e e 5 test.t2.b 2 Using where; Using index; Shortcut(t2)
+SELECT * FROM t1
+JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+JOIN t5 ON a = e;
+a b c d e
+1 1 1 1 1
+2 2 2 2 2
+4 4 4 4 4
+5 5 NULL NULL 5
+6 6 6 6 6
+7 7 7 7 7
+8 8 8 8 8
+9 9 NULL NULL 9
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+# Short-cut from last inner table inside an inner join that is nested
+# within an outer join
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t2.b 2 Using index
+1 SIMPLE t4 ref d d 5 test.t2.b 2 Using index; Shortcut(t2)
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+a b c d
+1 1 1 1
+2 2 2 2
+3 NULL NULL NULL
+4 4 4 4
+5 NULL NULL NULL
+6 6 6 6
+7 7 7 7
+8 8 8 8
+9 NULL NULL NULL
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+# Short-cut from not last inner table inside an inner join that is nested
+# within an outer join
+EXPLAIN
+SELECT * FROM t1
+LEFT JOIN
+(t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ON a = b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t2.b 2 Using index
+1 SIMPLE t4 ref d d 5 test.t2.b 2 Using index; Shortcut(t2)
+1 SIMPLE t5 ref e e 5 test.t4.d 2 Using index
+SELECT * FROM t1
+LEFT JOIN
+(t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ON a = b;
+a b c d e
+1 1 1 1 1
+2 2 2 2 2
+3 NULL NULL NULL NULL
+4 4 4 4 4
+5 NULL NULL NULL NULL
+6 6 6 6 6
+7 7 7 7 7
+8 8 8 8 8
+9 NULL NULL NULL NULL
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+10 10 10 10 10
+# Illegal scenario for short-cut. Should not be taken.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t1.a 2 Using index
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+a b c
+1 1 1
+2 2 2
+4 4 4
+5 5 NULL
+6 6 6
+7 7 7
+8 8 8
+9 9 9
+10 10 10
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ref b b 5 test.t1.a 2 Using index
+1 SIMPLE t3 ref c c 5 test.t1.a 2 Using index
+1 SIMPLE t4 ref d d 5 test.t1.a 2 Using index
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+a b c d
+1 1 1 1
+2 2 2 2
+4 4 4 4
+5 5 NULL NULL
+6 6 6 6
+7 7 7 7
+8 8 8 8
+9 9 NULL NULL
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+10 10 10 10
+DROP TABLE t1, t2, t3, t4, t5;
+#
+# Test 6c.
+# Tests that short-cuts work even if a row was read and then rejected.
+# This happens for table scan without join buffering.
+#
+set optimizer_join_cache_level = 0;;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (3);
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3(c) VALUES (1), (3), (4), (4), (4), (4);
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+a b c d
+1 1 1 NULL
+3 3 3 NULL
+SHOW STATUS LIKE 'handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_last 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 41
+FLUSH STATUS;
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Shortcut(t1)
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+a b c d
+SHOW STATUS LIKE 'handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_last 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 34
+SET @@optimizer_join_cache_level = DEFAULT;
+DROP TABLE t1, t2, t3;
+#
+# Test 7.
+# Combination test. This test contains multiple nesting levels, outer
+# join, table reordering, and NULL comparison. Short-cut should not be
+# used.
+#
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1,1 ), ( 2,2 ), ( 3,3 );
+CREATE TABLE t2( a INT, b INT, filler CHAR( 200 ), KEY( a ) );
+INSERT INTO t2( a, b ) VALUES ( 0, 0 ), ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t3 SELECT * FROM t2;
+CREATE TABLE t4 LIKE t3;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t4 SELECT * FROM t3;
+EXPLAIN
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ref a a 5 test.t1.b 2
+1 SIMPLE t4 ref a a 5 test.t2.b 2
+1 SIMPLE t3 ref a a 5 test.t2.b 1 Using index condition
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+a b filler a b a b filler a b filler
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+1 1 NULL 1 1 1 1 NULL 1 1 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+2 2 NULL 2 2 2 2 NULL 2 2 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+3 3 NULL 3 3 3 3 NULL 3 3 NULL
+DROP TABLE t1, t2, t3, t4;
=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result 2011-03-02 09:28:23 +0000
+++ b/mysql-test/r/subselect_innodb.result 2011-03-16 14:01:21 +0000
@@ -278,7 +278,7 @@ EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPE
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 100.00 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00
-1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00 Shortcut(t2)
2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
3 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
4 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 34 func 1 100.00 Using where
@@ -286,6 +286,9 @@ id select_type table type possible_keys
6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where
Warnings:
Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and <in_optimizer>(`test`.`t2`.`FOLDERID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`FOLDERID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level3') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level2') and <in_optimizer>(`test`.`t3`.`PARENTID`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`PARENTID`) in t3 on PRIMARY where ((`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t3`.`PARENTID`) = `test`.`t3`.`FOLDERID`))))) and (<cache>(`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`))))))
+SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
+DOCID DOCNAME DOCTYPEID FOLDERID AUTHOR CREATED TITLE SUBTITLE DOCABSTRACT PUBLISHDATE EXPIRATIONDATE LOCKEDBY STATUS PARENTDOCID REPID MODIFIED MODIFIER PUBLISHSTATUS ORIGINATOR DOCTYPENAME CONTENTSIZE MIMETYPE
+c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL
drop table t1, t2, t3, t4;
CREATE TABLE t1 (
school_name varchar(45) NOT NULL,
=== added file 'mysql-test/t/shortcut.test'
--- a/mysql-test/t/shortcut.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/shortcut.test 2011-03-16 14:01:21 +0000
@@ -0,0 +1,580 @@
+--echo #
+--echo # wl3724: Short-Cutting Join Execution: Speeding up star queries
+--echo #
+
+--echo #
+--echo # Test 1. Basic tests.
+--echo #
+--echo # Test 1a. Tests of EXPLAIN.
+--echo #
+CREATE TABLE t1 (
+ a INT,
+ b INT
+);
+
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 (
+ a INT,
+ KEY( a )
+);
+INSERT INTO t2 VALUES (1), (1), (1), (1), (1);
+
+CREATE TABLE t3 (
+ b INT,
+ KEY( b )
+);
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1);
+
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+
+SELECT * FROM t1 JOIN t2 USING( a ) JOIN t3 USING( b );
+
+--echo # Test that the short-cut does not cross an outer join boundary
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2, t3 ) USING( b );
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( t2 JOIN t3 ON a = b ) USING( b );
+
+--echo # Test that the optimization is robust wrt reordering of tables
+EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t1, t3, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+EXPLAIN SELECT * FROM t2, t1, t3 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t2, t3, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+EXPLAIN SELECT * FROM t3, t1, t2 WHERE t1.a = t2.a AND t1.b = t3.b;
+EXPLAIN SELECT * FROM t3, t2, t1 WHERE t1.a = t2.a AND t1.b = t3.b;
+
+DROP TABLE t1, t2, t3;
+
+--echo # Test that short-cuts show up in EXPLAIN properly with "Using index"
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT KEY );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( c INT KEY );
+INSERT INTO t3 VALUES (1), (2), (3);
+
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+
+--echo # Test that we don't take a short-cut if we have successfully read a
+--echo # row already.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE c > 1;
+
+DROP TABLE t1, t2, t3;
+--echo #
+--echo # Test 1b.
+--echo # Tests that short-cuts work properly with join buffers.
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t2key ( b INT KEY );
+INSERT INTO t2key VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3 VALUES (1, -1), (2, 1), (3, 1);
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = 0;
+
+--echo # Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = 0;
+
+--echo # Should now use short-cuts.
+EXPLAIN
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2key ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+DROP TABLE t1, t2, t2key, t3;
+
+--echo # Test that we reject a short-cut properly. We have a cartesian product
+--echo # of a table and the result of an outer join. The optimizer places the
+--echo # table between the outer and inner table of the outer join and hence
+--echo # there is a short-cut from the inner table to the outer table. This
+--echo # short-cut should be rejected.
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1), (2), (3);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (2), (3);
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+SELECT * FROM t2, t1 LEFT OUTER JOIN t3 ON (t3.a = t1.a);
+DROP TABLE t1, t2, t3;
+--echo # Test 2
+--echo # That the optimization actually works, i.e. that unneccesary reads are
+--echo # indeed short-cut.
+--echo #
+--echo # Test 2a: We have two tables between giving and receiving end of short-cut:
+--echo # t2 and t3.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+SELECT * FROM t1 JOIN t2 USING(a) JOIN t3 ON t2.a = t3.b JOIN t4 ON t1.b = t4.b;
+
+SHOW STATUS LIKE 'handler_read_%';
+DROP TABLE t1, t2, t3, t4;
+--echo # Test 2b
+--echo # Short-cuts followed by successful keys. This tests that we perform the
+--echo # optimal number of reads after the short-cut is taken without missing
+--echo # rows.
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (2), (3), (4), (4), (4), (5);
+
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (3), (5), (-1), (-1), (-1), (-1), (-1), (-1);
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3;
+--echo # Test 2c
+--echo # Test that we don't erroneously take a short-cut at the end of a table
+--echo # scan.
+let $requires_join_cache_level = 0;
+--source include/have_join_cache_level.inc
+--eval set optimizer_join_cache_level = $requires_join_cache_level
+
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 ( a INT, KEY (a) );
+INSERT INTO t2 VALUES (1), (1), (2);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (1), (3);
+
+EXPLAIN
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 USING(a) JOIN t3 USING(a);
+
+DROP TABLE t1, t2, t3;
+
+SET @@optimizer_join_cache_level = DEFAULT;
+--echo #
+--echo # Test 3.
+--echo # Test for outer joins. We may not take short-cuts across outer join
+--echo # boundaries. An outer join boundary goes before the first inner table for an
+--echo # outer join.
+--echo #
+--echo # Test 3a.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( b INT, KEY( b ) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( b INT, KEY( b ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+CREATE TABLE t5 ( b INT, KEY( b ) );
+INSERT INTO t5 VALUES (1), (1), (1), (1), (1), (1), (1), (1);
+INSERT INTO t5 SELECT * FROM t5;
+
+--echo # Test of two outer join boundaries in one join.
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING ( a )
+ LEFT JOIN t4 ON t1.a = t4.b;
+
+EXPLAIN
+SELECT *
+FROM t1 LEFT JOIN (t2 JOIN t3 ON t2.a = t3.b) USING(a)
+ LEFT JOIN (t4 JOIN t5 USING( b )) ON t1.a = t4.b;
+
+DROP TABLE t1, t2, t3, t4, t5;
+--echo # Test 3b.
+--echo # Tests that we do not miss any data due to short-cutting.
+
+CREATE TABLE t1 ( a INT, b INT );
+CREATE TABLE t2 ( a INT, b INT, PRIMARY KEY (a,b) );
+CREATE TABLE t3 ( a INT, b INT, PRIMARY KEY (a,b) );
+
+INSERT INTO t1 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+INSERT INTO t3 VALUES ( 1, 1 ), ( 2, 1 ), ( 1, 3 );
+
+--echo # Test of the short-cut detection algorithm. When traversing the
+--echo # push-down conditions the algorithm will first find that the
+--echo # prospective short-cut t3->t1, but this must be invalidated due to the
+--echo # later found dependency t3->t2.
+EXPLAIN
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+SELECT * FROM t1, t2, t3 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.b = t3.b;
+
+--echo # Test of internal representation of pushdown conditions.
+--echo # The join conditions will be encoded in ref access, while the WHERE
+--echo # condition remains in the pushdown condition.
+EXPLAIN
+SELECT *
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a
+WHERE t3.b + 1 = t2.b;
+
+--echo # Short-cut is actually applicable in this case, thanks to equality
+--echo # propagation. The WHERE condition t3.a + 1 = t2.a is rewritten into
+--echo # t1.b + 1 = t1.a, and hence we have an optimizable star query.
+EXPLAIN
+SELECT *
+FROM t1 JOIN t2 ON t1.a = t2.a JOIN t3 ON t1.b = t3.a
+WHERE t3.a + 1 = t2.a;
+
+DROP TABLE t1, t2, t3;
+--echo # Test 3c
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (1), (1), (1);
+
+CREATE TABLE t2 ( a INT, KEY (a), b INT, c INT );
+INSERT INTO t2 VALUES (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1), (1, 1, 1);
+INSERT INTO t2 SELECT * FROM t2;
+INSERT INTO t2 SELECT * FROM t2;
+
+CREATE TABLE t3 ( a INT, KEY (a) );
+INSERT INTO t3 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (3);
+
+CREATE TABLE t4 ( a INT, KEY (a), b INT );
+INSERT INTO t4 VALUES (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1);
+INSERT INTO t4 SELECT * FROM t4;
+
+--echo #
+--echo # Test that the short-cut t4->t1 is rejected in favor of t4->t2.
+--echo #
+EXPLAIN
+SELECT STRAIGHT_JOIN *
+FROM t1 JOIN t2 ON t1.a = t2.a
+ JOIN t3 ON t2.b = t3.a
+ JOIN t4 ON t1.a = t4.a AND t2.c = t4.b;
+
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # Test 4. More tests of reordering of tables.
+--echo #
+CREATE TABLE t1( a INT, b INT );
+
+INSERT INTO t1 VALUES ( 2, 2 );
+INSERT INTO t1 VALUES ( 2, 2 );
+
+CREATE TABLE t2( a INT PRIMARY KEY );
+
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 2 );
+
+CREATE TABLE t3( a INT, INDEX( a ) );
+
+INSERT INTO t3 VALUES ( 6 );
+INSERT INTO t3 VALUES ( 5 );
+INSERT INTO t3 VALUES ( 2 );
+
+EXPLAIN
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+SELECT t2.* FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+
+EXPLAIN
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+SELECT * FROM t2, t1, t3 WHERE t2.a = t1.a AND t1.b = t3.a;
+
+DROP TABLE t1, t2, t3;
+
+
+--echo #
+--echo # Test 5.
+--echo # Tests that a short-cut does not cross a plan node that is using join
+--echo # buffering.
+--echo #
+--echo # Test 5a.
+--echo #
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a ), KEY( b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+
+CREATE TABLE t3 ( a INT, KEY( a ) );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( a INT, KEY( a ) );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3, t4;
+--echo #
+--echo # Test 5b.
+--echo #
+CREATE TABLE t1 ( a INT, c INT );
+INSERT INTO t1 VALUES (1, 2), (-1, -1);
+
+CREATE TABLE t2 ( a INT, b INT, KEY( a, b ) );
+INSERT INTO t2 VALUES (1, 2),(1, 2),(1, 2),(1, 2),(1, 2);
+
+CREATE TABLE t3 ( a INT );
+INSERT INTO t3 VALUES (2), (2), (2), (2), (2);
+INSERT INTO t3 SELECT * FROM t3;
+
+CREATE TABLE t4 ( a INT );
+INSERT INTO t4 VALUES (1), (1), (1), (1), (1);
+INSERT INTO t4 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t4;
+
+FLUSH STATUS;
+
+--echo # Should not use short-cuts.
+EXPLAIN
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+SELECT * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.b = t3.a AND t1.c = t4.a;
+
+SHOW STATUS LIKE 'handler_read_%';
+
+DROP TABLE t1, t2, t3, t4;
+--echo #
+--echo # Test 6. Test of short-cuts in conjunction with outer join.
+--echo #
+--echo # Test 6a. Test of outer join detection.
+--echo # In this case the execution order is t1, t2, t3, t4.
+--echo # t2, t3 and t4 will form an 'outer join nest' with which t1 is left
+--echo # outer joined. But (t3, t2) is left joined with t4. Since t4 is just one
+--echo # table, however, the 'outer join nest' of t4 is only implicitly
+--echo # represented in the query plan. The only way to detect it is by looking
+--echo # at t4's first_inner pointer, which would be null for the last inner table
+--echo # in an outer join nest. But since it points to itseld, t4 is in its own
+--echo # nest. But there is no NESTED_JOIN to represent it, since it follows the
+--echo # 'normal' left-deep tree representation.
+--echo #
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (d INT, e INT, f INT);
+CREATE TABLE t3 (g INT, h INT, i INT);
+CREATE TABLE t4 (j INT, k INT, l INT);
+
+INSERT INTO t1 VALUES (3,1,0), (2,2,0), (3,3,0);
+INSERT INTO t2 VALUES (1,1,0), (2,2,0);
+INSERT INTO t3 VALUES (3,2,0), (6,2,0), (6,1,0);
+INSERT INTO t4 VALUES (0,2,0), (1,2,0);
+
+EXPLAIN
+SELECT a, b, c, d, e, f, g, h, i, j, k, l
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+SELECT *
+FROM t1 LEFT JOIN ( (t3, t2) LEFT JOIN t4 ON t2.e = t4.k AND t3.h < 10 ) ON t1.b = t2.e;
+
+DROP TABLE t1, t2, t3, t4;
+--echo #
+--echo # Test 6b.
+--echo # We may take short-cuts across or inside an outer join, as long as we
+--echo # don't fail due to an outer join predicate. It goes as follows.
+--echo #
+--echo # - We may take a short-cut across a nested outer join.
+--echo # - We may take short-cuts inside a nested inner join sequence.
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+CREATE TABLE t2 ( b INT, KEY ( b ) );
+INSERT INTO t2 VALUES (1), (2), (4), (5), (6), (7), (8), (9), (10);
+
+CREATE TABLE t3 ( c INT, KEY( c ) );
+INSERT INTO t3 VALUES (1), (2), (3), (4), (6), (7), (8), (9), (10);
+
+CREATE TABLE t4 ( d INT, KEY( d ) );
+INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (10);
+INSERT INTO t4 VALUES (10), (10), (10), (10);
+
+CREATE TABLE t5 ( e INT, KEY( e ) );
+INSERT INTO t5 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+--echo # Outer join followed by a table that is inner joined with the first.
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+SELECT * FROM t1 LEFT JOIN t2 ON a = b JOIN t3 ON a = c;
+
+--echo # Short-cut across a nested outer join
+EXPLAIN
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+SELECT * FROM t1 JOIN (t2 LEFT JOIN t3 ON b = c) ON a = b JOIN t4 ON a = d;
+
+--echo # Short-cut across a nested outer join with a nested inner join
+EXPLAIN
+SELECT * FROM t1
+ JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+ JOIN t5 ON a = e;
+SELECT * FROM t1
+ JOIN (t2 LEFT JOIN (t3 JOIN t4 ON c = d) ON b = d) ON a = b
+ JOIN t5 ON a = e;
+
+--echo # Short-cut from last inner table inside an inner join that is nested
+--echo # within an outer join
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 ON b = c JOIN t4 ON b = d) on a = b;
+
+--echo # Short-cut from not last inner table inside an inner join that is nested
+--echo # within an outer join
+EXPLAIN
+SELECT * FROM t1
+ LEFT JOIN
+ (t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ ON a = b;
+SELECT * FROM t1
+ LEFT JOIN
+ (t2 JOIN t3 ON b = c JOIN t4 ON b = d JOIN t5 ON d = e)
+ ON a = b;
+
+--echo # Illegal scenario for short-cut. Should not be taken.
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN t3 ON a = c;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b LEFT JOIN (t3 JOIN t4 ON c = d) ON a = c;
+
+DROP TABLE t1, t2, t3, t4, t5;
+--echo #
+--echo # Test 6c.
+--echo # Tests that short-cuts work even if a row was read and then rejected.
+--echo # This happens for table scan without join buffering.
+--echo #
+let $requires_join_cache_level = 0;
+--source include/have_join_cache_level.inc
+--eval set optimizer_join_cache_level = $requires_join_cache_level;
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+
+CREATE TABLE t2 ( b INT );
+INSERT INTO t2 VALUES (1), (2), (2), (2), (2), (3);
+
+CREATE TABLE t3 ( c INT, d INT );
+INSERT INTO t3(c) VALUES (1), (3), (4), (4), (4), (4);
+
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c;
+
+SHOW STATUS LIKE 'handler_read%';
+FLUSH STATUS;
+
+EXPLAIN
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+SELECT * FROM t1 JOIN t2 ON a = b JOIN t3 ON a = c WHERE d > 0;
+
+SHOW STATUS LIKE 'handler_read%';
+
+SET @@optimizer_join_cache_level = DEFAULT;
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Test 7.
+--echo # Combination test. This test contains multiple nesting levels, outer
+--echo # join, table reordering, and NULL comparison. Short-cut should not be
+--echo # used.
+--echo #
+CREATE TABLE t1 ( a INT, b INT );
+INSERT INTO t1 VALUES ( 1,1 ), ( 2,2 ), ( 3,3 );
+
+CREATE TABLE t2( a INT, b INT, filler CHAR( 200 ), KEY( a ) );
+INSERT INTO t2( a, b ) VALUES ( 0, 0 ), ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t3 SELECT * FROM t2;
+
+CREATE TABLE t4 LIKE t3;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t4 SELECT * FROM t3;
+
+EXPLAIN
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+
+SELECT *
+FROM t3 JOIN t1 LEFT JOIN ( t2 JOIN t4 ON t4.a = t2.b ) ON t2.a = t1.b
+WHERE t3.a <=> t2.b;
+
+DROP TABLE t1, t2, t3, t4;
=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test 2010-12-17 09:41:21 +0000
+++ b/mysql-test/t/subselect_innodb.test 2011-03-16 14:01:21 +0000
@@ -302,7 +302,7 @@ ALTER TABLE t3 ADD FOREIGN KEY FK_FLDRS
SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
-
+SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
drop table t1, t2, t3, t4;
# End of 4.1 tests
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2011-01-31 10:37:16 +0000
+++ b/sql/item.cc 2011-03-16 14:01:21 +0000
@@ -2294,6 +2294,9 @@ bool Item_field::eq(const Item *item, bo
}
+/**
+ Returns simply the table to which the field belongs.
+*/
table_map Item_field::used_tables() const
{
if (field->table->const_table)
=== modified file 'sql/item.h'
--- a/sql/item.h 2011-02-16 09:12:52 +0000
+++ b/sql/item.h 2011-03-16 14:01:21 +0000
@@ -868,7 +868,14 @@ public:
virtual bool val_bool_result() { return val_bool(); }
virtual bool is_null_result() { return is_null(); }
- /* bit map of tables used by item */
+ /**
+ This is the set of tables referenced by this Item.
+
+ Returns the set of tables referenced by this item. An item can be
+ recursively defined in terms of other items, and the set of tables is
+ generally calculated by forming the union of the set of tables referenced
+ by the embedded items.
+ */
virtual table_map used_tables() const { return (table_map) 0L; }
/*
Return table map of tables that can't be NULL tables (tables that are
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-03-01 14:57:53 +0000
+++ b/sql/sql_select.cc 2011-03-16 14:01:21 +0000
@@ -1731,6 +1731,98 @@ static int clear_sj_tmp_tables(JOIN *joi
}
+/**
+ @defgroup Shortcut Short-cut
+ @{
+*/
+
+
+/**
+ Sets the short-cut from one plan node to another, if one is found.
+
+ @param join The execution plan.
+
+ @param join_tab The plan node from which a short-cut will potentially be
+ taken.
+*/
+static void set_star_dependency(const JOIN *join, JOIN_TAB *join_tab)
+{
+ if (join_tab->use_join_cache != JOIN_CACHE::ALG_NONE)
+ return;
+
+ table_map where_clause_tables;
+ if (join_tab->select_cond != NULL)
+ {
+ join_tab->select_cond->update_used_tables();
+ where_clause_tables= join_tab->select_cond->used_tables();
+ }
+ else
+ where_clause_tables= (table_map)0L;
+
+ table_map tables_depended_upon=
+ (where_clause_tables | join_tab->ref.depend_map) & ~PSEUDO_TABLE_BITS;
+
+ if (tables_depended_upon == (table_map)0)
+ return;
+
+ for (JOIN_TAB *prospect= join_tab - 1; prospect >= join->join_tab; --prospect)
+ {
+ if (prospect->use_join_cache != JOIN_CACHE::ALG_NONE)
+ return;
+
+ if ((prospect->table->map & tables_depended_upon) != (table_map)0)
+ {
+ if (join->are_inner_joined(prospect, join_tab) && prospect < join_tab - 1)
+ {
+ DBUG_PRINT("info", ("Found a join short-cut from %s to %s",
+ join_tab->table->alias,
+ prospect->table->alias));
+
+ join_tab->shortcut= prospect;
+ }
+ return;
+ }
+ }
+}
+
+
+/**
+ Pre-compute short-cuts for the join short-cut optimization.
+
+ This function adorns the query exeution plan with star join short-cuts
+ where applicable. Short-cuts are triggered by the executioner. The
+ algorithm is implemented as a nested loop, where the outer loop walks the
+ nodes (JOIN_TAB's) in the query execution plan, and the inner loop iterates
+ through all tables that this table depends upon. Valid short-cuts have the
+ following properties.
+
+ - The short-cut has a minimum length of 2. If a table is dependent on the
+ table directly preceding it, the short-cut would be of length 1, and it
+ would indicate that there are no valid short-cuts from this table.
+
+ - The short-cut does not cross an outer join boundary. In this case the
+ optimization is not applicable, because a valid result would be missed.
+
+ - The short-cut does not cross a node using join buffers. Short-cuts are
+ not compatible with join buffering.
+
+ - If there are several applicable shortcuts by the above requirements, the
+ shortest one is chosen.
+
+ @param join The query execution plan. The plan is assumed to be complete
+ and ready for execution when this method is invoked.
+*/
+static void setup_shortcuts(JOIN *join)
+{
+ if (join->join_tab == NULL)
+ return;
+
+ for (uint i= join->const_tables + 2; i < join->tables; ++i)
+ set_star_dependency(join, &join->join_tab[i]);
+}
+
+/* @} */
+
/**
global select optimisation.
@@ -2470,6 +2562,9 @@ JOIN::optimize()
}
tmp_having= having;
+
+ setup_shortcuts(this);
+
if (select_options & SELECT_DESCRIBE)
{
error= 0;
@@ -17272,6 +17367,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *j
DBUG_ASSERT(cache != NULL);
cache->reset_join(join);
+ join->return_tab= join_tab;
DBUG_ENTER("sub_select_cache");
@@ -17444,6 +17540,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
DBUG_ENTER("sub_select");
join_tab->table->null_row=0;
+ join->return_tab= join_tab;
if (end_of_records)
{
enum_nested_loop_state nls=
@@ -17459,7 +17556,6 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
do_sj_reset(join_tab->flush_weedout_table);
}
- join->return_tab= join_tab;
join_tab->not_null_compl= TRUE;
if (join_tab->last_inner)
@@ -17474,6 +17570,7 @@ sub_select(JOIN *join,JOIN_TAB *join_tab
}
join->thd->warning_info->reset_current_row_for_warning();
+ join_tab->number_produced_rows= 0;
error= (*join_tab->read_first_record)(join_tab);
if (join_tab->keep_current_rowid)
@@ -17650,7 +17747,11 @@ evaluate_join_record(JOIN *join, JOIN_TA
if (error > 0 || (join->thd->is_error())) // Fatal error
DBUG_RETURN(NESTED_LOOP_ERROR);
if (error < 0)
+ {
+ if (join_tab->shortcut != NULL && join_tab->number_produced_rows == 0)
+ join->return_tab= join_tab->shortcut;
DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
+ }
if (join->thd->killed) // Aborted by user
{
join->thd->send_kill_message();
@@ -17796,6 +17897,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
if (found)
{
+ ++join_tab->number_produced_rows;
enum enum_nested_loop_state rc;
/* A match from join_tab is found for the current partial join. */
rc= (*join_tab->next_select)(join, join_tab+1, 0);
@@ -23256,6 +23358,13 @@ void select_describe(JOIN *join, bool ne
extra.append(STRING_WITH_LEN(", incremental buffers)"));
}
+ if (tab->shortcut != NULL)
+ {
+ extra.append(STRING_WITH_LEN("; Shortcut("));
+ extra.append(tab->shortcut->table->alias);
+ extra.append(STRING_WITH_LEN(")"));
+ }
+
/* Skip initial "; "*/
const char *str= extra.ptr();
uint32 len= extra.length();
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-03-01 14:57:53 +0000
+++ b/sql/sql_select.h 2011-03-16 14:01:21 +0000
@@ -237,6 +237,17 @@ typedef struct st_join_table : public Sq
TABLE *table;
Key_use *keyuse; /**< pointer to first used key */
SQL_SELECT *select;
+ /**
+ The condition to be evaluated when a row from this table has been made
+ available inside query execution. It may reference a row from all tables
+ in the current join prefix (ie this table and all tables preceeding it in
+ the join plan). Generally, a condition consists of a set of predicates
+ combined using AND, OR and XOR.
+
+ @note Predicates that can be fully evaluated using a ref access condition
+ or that have been pushed down to the storage engine are excluded from this
+ condition.
+ */
Item *select_cond;
QUICK_SELECT_I *quick;
Item **on_expr_ref; /**< pointer to the associated on expression */
@@ -378,6 +389,28 @@ typedef struct st_join_table : public Sq
/* NestedOuterJoins: Bitmap of nested joins this table is part of */
nested_join_map embedding_map;
+ /**
+ The number of rows produced at this plan node with a common prefix
+ produced by the preceding plan nodes.
+
+ The counter is reset each time a partial row has been produced for which
+ pushdown conditions are true. The partial row is comprised of the
+ concatenation of partial rows from all previous tables in the join.
+ */
+ ha_rows number_produced_rows;
+
+ /**
+ Destination for join short-cuts. Short-cuts are set up by
+ setup_shortcuts() and enable skipping of read operations if it is
+ discovered that none of the rows produced by a certain plan node can
+ satisfy the search condition. In this case execution needs to
+ immediatedly revert back to a previous plan node and commence execution
+ from there.
+
+ @see setup_shortcuts(), evaluate_join_record(), set_star_dependency()
+ */
+ st_join_table *shortcut;
+
void cleanup();
inline bool is_using_loose_index_scan()
{
@@ -506,7 +539,8 @@ st_join_table::st_join_table()
found_match(FALSE),
keep_current_rowid(0),
- embedding_map(0)
+ embedding_map(0),
+ shortcut(NULL)
{
/**
@todo Add constructor to READ_RECORD.
@@ -1808,12 +1842,15 @@ public:
TABLE_LIST *tables_list; ///<hold 'tables' parameter of mysql_select
List<TABLE_LIST> *join_list; ///< list of joined tables in reverse order
COND_EQUAL *cond_equal;
- /*
- Join tab to return to. Points to an element of join->join_tab array, or to
- join->join_tab[-1].
- This is used at execution stage to shortcut join enumeration. Currently
- shortcutting is done to handle outer joins or handle semi-joins with
- FirstMatch strategy.
+ /**
+ Interface for taking short-cuts through join execution. Join tab to return
+ to. Points to an element of join->join_tab array, or to
+ join->join_tab[-1]. This is used at execution stage to shortcut join
+ enumeration. Shortcutting is done to
+
+ - handle outer joins, and
+ - handle semi-joins with FirstMatch strategy, and
+ - optimize execution of star joins.
*/
JOIN_TAB *return_tab;
Item **ref_pointer_array; ///<used pointer reference for this select
@@ -1981,6 +2018,26 @@ public:
((group || tmp_table_param.sum_func_count) && !group_list)) ?
NULL : join_tab+const_tables;
}
+
+
+ /**
+ Returns true if JOIN_TAB's a and b participate on the same nesting level
+ within a chain of inner joined tables.
+
+ @note The optimizer execution plan is considered, and outer joins may
+ have been rewritten into inner joins.
+ */
+ static bool are_inner_joined(const JOIN_TAB *a, const JOIN_TAB *b)
+ {
+ DBUG_ASSERT(a < b);
+ DBUG_ASSERT(b->on_expr_ref != NULL);
+ return
+ a->table->pos_in_table_list->embedding ==
+ b->table->pos_in_table_list->embedding &&
+ *b->on_expr_ref == NULL;
+ }
+
+
private:
/**
TRUE if the query contains an aggregate function but has no GROUP
Attachment: [text/bzr-bundle] bzr/martin.hansson@oracle.com-20110316140121-i1bgrzp3x19cswyj.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (martin.hansson:3299) WL#3724 | Martin Hansson | 16 Mar |
