diff options
Diffstat (limited to 'mysql-test/suite/pbxt/r/greedy_optimizer.result')
-rw-r--r-- | mysql-test/suite/pbxt/r/greedy_optimizer.result | 155 |
1 files changed, 79 insertions, 76 deletions
diff --git a/mysql-test/suite/pbxt/r/greedy_optimizer.result b/mysql-test/suite/pbxt/r/greedy_optimizer.result index aeed76ab167..0dab29e2e59 100644 --- a/mysql-test/suite/pbxt/r/greedy_optimizer.result +++ b/mysql-test/suite/pbxt/r/greedy_optimizer.result @@ -1,4 +1,6 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7; +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=1; create table t1 ( c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer, primary key (c11) @@ -202,28 +204,28 @@ select @@optimizer_search_depth; 0 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; 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; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; 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; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -231,11 +233,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -243,11 +245,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -255,11 +257,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -267,11 +269,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 set optimizer_search_depth=1; select @@optimizer_search_depth; @@optimizer_search_depth @@ -287,7 +289,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -299,7 +301,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -311,7 +313,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -323,7 +325,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -335,7 +337,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -347,35 +349,35 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=62; select @@optimizer_search_depth; @@optimizer_search_depth 62 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; 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; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; 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; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1372.225316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -383,11 +385,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -395,11 +397,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 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 t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -407,11 +409,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where @@ -419,11 +421,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 290.146368 +Last_query_cost 363.346368 set optimizer_prune_level=1; select @@optimizer_prune_level; @@optimizer_prune_level @@ -443,7 +445,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -455,55 +457,55 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; 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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; 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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; 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 +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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; 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 +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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=1; select @@optimizer_search_depth; @@optimizer_search_depth @@ -519,7 +521,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -531,7 +533,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -543,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -555,7 +557,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -567,7 +569,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 Using where @@ -579,7 +581,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 set optimizer_search_depth=62; select @@optimizer_search_depth; @@optimizer_search_depth @@ -595,7 +597,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 @@ -607,53 +609,54 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 822.625316 +Last_query_cost 1694.425316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; 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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71; 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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index -1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index -1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; 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 +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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76; 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 +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 t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where 1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where show status like 'Last_query_cost'; Variable_name Value -Last_query_cost 795.625316 +Last_query_cost 844.825316 drop table t1,t2,t3,t4,t5,t6,t7; +set join_cache_level=@save_join_cache_level; |