summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMonty <monty@mariadb.org>2022-06-30 15:43:58 +0300
committerMonty <monty@mariadb.org>2022-07-10 15:09:32 +0300
commitf4863621300cbd40ef32be6771e32cf1f15598bd (patch)
tree807a96cc4f5de2623118b2570fa5e173b208cb66
parentf8484e5fa3c93bb087cc8d216bae8648703521b4 (diff)
downloadmariadb-git-f4863621300cbd40ef32be6771e32cf1f15598bd.tar.gz
Adjust cost for re-creating a row from the JOIN CACHE
Creating a record from the join cache is faster than getting a row from the engine (less and simpler code to execute). Added JOIN_CACHE_ROW_COPY_COST_FACTOR (0.5 for now) as the factor to take this into account. This is multiplied with ROW_COPY_COST. Other things: - Added cost of copying rows to hash join, similar to join_cache joins.
Diffstat
-rw-r--r--mysql-test/main/greedy_optimizer.result84
-rw-r--r--mysql-test/main/opt_trace.result492
-rw-r--r--mysql-test/main/select_safe.result1
-rw-r--r--mysql-test/main/select_safe.test1
-rw-r--r--mysql-test/main/status.result2
-rw-r--r--mysql-test/main/subselect.result4
-rw-r--r--mysql-test/main/subselect4.result5
-rw-r--r--mysql-test/main/subselect_no_exists_to_in.result4
-rw-r--r--mysql-test/main/subselect_no_opts.result4
-rw-r--r--mysql-test/main/subselect_no_scache.result4
-rw-r--r--mysql-test/main/subselect_no_semijoin.result8
-rw-r--r--mysql-test/main/subselect_sj.result10
-rw-r--r--mysql-test/main/subselect_sj_jcl6.result12
-rw-r--r--sql/sql_const.h7
-rw-r--r--sql/sql_select.cc6
15 files changed, 352 insertions, 292 deletions
diff --git a/mysql-test/main/greedy_optimizer.result b/mysql-test/main/greedy_optimizer.result
index 1122316b23c..371dbfd8abb 100644
--- a/mysql-test/main/greedy_optimizer.result
+++ b/mysql-test/main/greedy_optimizer.result
@@ -127,7 +127,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -139,7 +139,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -151,7 +151,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -163,7 +163,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -175,7 +175,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 1563.335143
+Last_query_cost 1334.341393
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
@@ -187,7 +187,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 1563.335143
+Last_query_cost 1334.341393
set optimizer_prune_level=0;
select @@optimizer_prune_level;
@@optimizer_prune_level
@@ -207,7 +207,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 2693.038350
+Last_query_cost 2437.438350
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 t2 ALL NULL NULL NULL NULL 6 Using where
@@ -219,7 +219,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 2693.038350
+Last_query_cost 2437.438350
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,7 +231,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 751.472751
+Last_query_cost 653.372751
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,7 +243,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 751.472751
+Last_query_cost 653.372751
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,7 +255,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 760.922751
+Last_query_cost 662.822751
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,7 +267,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 760.922751
+Last_query_cost 662.822751
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -283,7 +283,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -295,7 +295,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -307,7 +307,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 2068.798851
+Last_query_cost 1762.798851
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
@@ -319,7 +319,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 2068.798851
+Last_query_cost 1762.798851
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
@@ -331,7 +331,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 2073.523851
+Last_query_cost 1767.523851
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
@@ -343,7 +343,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 2073.523851
+Last_query_cost 1767.523851
set optimizer_search_depth=62;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -359,7 +359,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 2693.038350
+Last_query_cost 2437.438350
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 t2 ALL NULL NULL NULL NULL 6 Using where
@@ -371,7 +371,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 2693.038350
+Last_query_cost 2437.438350
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,7 +383,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 751.472751
+Last_query_cost 653.372751
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,7 +395,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 751.472751
+Last_query_cost 653.372751
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,7 +407,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 760.922751
+Last_query_cost 662.822751
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,7 +419,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 760.922751
+Last_query_cost 662.822751
set optimizer_prune_level=1;
select @@optimizer_prune_level;
@@optimizer_prune_level
@@ -439,7 +439,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -451,7 +451,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -463,7 +463,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -475,7 +475,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -487,7 +487,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 1563.335143
+Last_query_cost 1334.341393
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
@@ -499,7 +499,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 1563.335143
+Last_query_cost 1334.341393
set optimizer_search_depth=1;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -515,7 +515,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -527,7 +527,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -539,7 +539,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 2068.798851
+Last_query_cost 1762.798851
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
@@ -551,7 +551,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 2068.798851
+Last_query_cost 1762.798851
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
@@ -563,7 +563,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 2073.523851
+Last_query_cost 1767.523851
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
@@ -575,7 +575,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 2073.523851
+Last_query_cost 1767.523851
set optimizer_search_depth=62;
select @@optimizer_search_depth;
@@optimizer_search_depth
@@ -591,7 +591,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -603,7 +603,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 2696.338350
+Last_query_cost 2445.013350
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
@@ -615,7 +615,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -627,7 +627,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 1559.791393
+Last_query_cost 1330.797643
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
@@ -639,7 +639,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 1563.335143
+Last_query_cost 1334.341393
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
@@ -651,7 +651,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 1563.335143
+Last_query_cost 1334.341393
drop table t1,t2,t3,t4,t5,t6,t7;
CREATE TABLE t1 (a int, b int, d int, i int);
INSERT INTO t1 VALUES (1,1,1,1);
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index f3bc5e40ea9..46c6b52bc83 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -1125,7 +1125,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"rows": 100,
"rows_after_scan": 75,
"rows_after_filter": 75,
- "cost": 1901.158691,
+ "cost": 1807.408691,
"index_only": false,
"chosen": false
}
@@ -1195,7 +1195,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b {
"rows": 100,
"rows_after_scan": 75,
"rows_after_filter": 75,
- "cost": 1901.158691,
+ "cost": 1807.408691,
"index_only": false,
"chosen": false
}
@@ -3224,7 +3224,7 @@ explain extended select * from t1 where a in (select pk from t10) {
"rows": 10,
"rows_after_scan": 10,
"rows_after_filter": 10,
- "cost": 11.01098633,
+ "cost": 10.63598633,
"index_only": false,
"chosen": true
}
@@ -3233,17 +3233,17 @@ explain extended select * from t1 where a in (select pk from t10) {
"type": "scan",
"records_read": 10,
"records_out": 10,
- "cost": 11.01098633,
+ "cost": 10.63598633,
"uses_join_buffering": true
}
},
"rows_for_plan": 30,
- "cost_for_plan": 12.76428223,
+ "cost_for_plan": 12.38928223,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
"records": 3,
- "cost": 12.76428223
+ "cost": 12.38928223
},
{
"strategy": "SJ-Materialization",
@@ -3253,10 +3253,10 @@ explain extended select * from t1 where a in (select pk from t10) {
{
"strategy": "DuplicateWeedout",
"records": 3,
- "dups_cost": 12.76428223,
+ "dups_cost": 12.38928223,
"write_cost": 2.5,
"full_lookup_cost": 15,
- "total_cost": 30.26428223
+ "total_cost": 29.88928223
},
{
"chosen_strategy": "SJ-Materialization"
@@ -4886,7 +4886,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -4895,12 +4895,12 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953
+ "cost_for_plan": 5.642626953
}
]
},
@@ -4984,7 +4984,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -4993,12 +4993,12 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5014,7 +5014,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"index_only": false,
"chosen": true
}
@@ -5023,12 +5023,12 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 15.60769043,
+ "cost_for_plan": 14.82019043,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
@@ -5038,15 +5038,15 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
{
"strategy": "SJ-Materialization",
"records": 3,
- "cost": 10.75769043
+ "cost": 10.64519043
},
{
"strategy": "DuplicateWeedout",
"records": 3,
- "dups_cost": 15.60769043,
+ "dups_cost": 14.82019043,
"write_cost": 1.45,
"full_lookup_cost": 4.05,
- "total_cost": 21.10769043
+ "total_cost": 20.32019043
},
{
"chosen_strategy": "SJ-Materialization"
@@ -5068,7 +5068,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -5077,12 +5077,12 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -5171,7 +5171,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
"best_join_order": ["t1", "<subquery2>"],
"best_access_method": {
"rows": 3,
- "cost": 10.75769043
+ "cost": 10.64519043
}
},
{
@@ -5493,7 +5493,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -5502,12 +5502,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5523,7 +5523,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -5532,12 +5532,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
@@ -5547,10 +5547,10 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
{
"strategy": "DuplicateWeedout",
"records": 3,
- "dups_cost": 33.31281738,
+ "dups_cost": 31.17531738,
"write_cost": 1.45,
"full_lookup_cost": 12.15,
- "total_cost": 46.91281738
+ "total_cost": 44.77531738
},
{
"chosen_strategy": "FirstMatch"
@@ -5570,7 +5570,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"index_only": false,
"chosen": true
}
@@ -5579,12 +5579,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 49.0371582,
+ "cost_for_plan": 48.0246582,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5605,7 +5605,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 34.15256348,
+ "cost": 30.10256348,
"index_only": false,
"chosen": true
}
@@ -5614,12 +5614,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 34.15256348,
+ "cost": 30.10256348,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 83.18972168,
+ "cost_for_plan": 78.12722168,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5641,7 +5641,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"index_only": false,
"chosen": true
}
@@ -5650,25 +5650,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 414.4974121,
+ "cost_for_plan": 363.8724121,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
"records": 27,
- "cost": 360.2292969
+ "cost": 359.2167969
},
{
"strategy": "DuplicateWeedout",
"records": 27,
- "dups_cost": 414.4974121,
+ "dups_cost": 363.8724121,
"write_cost": 5.05,
"full_lookup_cost": 109.35,
- "total_cost": 528.8974121
+ "total_cost": 478.2724121
},
{
"chosen_strategy": "FirstMatch"
@@ -5695,7 +5695,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 100.4576904,
+ "cost": 88.30769043,
"index_only": false,
"chosen": true
}
@@ -5704,12 +5704,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 100.4576904,
+ "cost": 88.30769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 149.4948486,
+ "cost_for_plan": 136.3323486,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -5728,7 +5728,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.902563477,
+ "cost": 4.565063477,
"index_only": false,
"chosen": true
}
@@ -5737,12 +5737,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.902563477,
+ "cost": 4.565063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 41.23203125,
+ "cost_for_plan": 40.89453125,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5763,7 +5763,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"index_only": false,
"chosen": true
}
@@ -5772,12 +5772,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 76.88972168,
+ "cost_for_plan": 72.50222168,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5799,7 +5799,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"index_only": false,
"chosen": true
}
@@ -5808,20 +5808,20 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 408.1974121,
+ "cost_for_plan": 358.2474121,
"semijoin_strategy_choice": [
{
"strategy": "DuplicateWeedout",
"records": 27,
- "dups_cost": 408.1974121,
+ "dups_cost": 358.2474121,
"write_cost": 5.05,
"full_lookup_cost": 109.35,
- "total_cost": 522.5974121
+ "total_cost": 472.6474121
},
{
"chosen_strategy": "DuplicateWeedout"
@@ -5849,7 +5849,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"index_only": false,
"chosen": true
}
@@ -5858,12 +5858,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 76.88972168,
+ "cost_for_plan": 72.50222168,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -5882,7 +5882,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"index_only": false,
"chosen": true
}
@@ -5891,12 +5891,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 49.0371582,
+ "cost_for_plan": 48.0246582,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -5915,7 +5915,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -5924,12 +5924,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -5945,7 +5945,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"index_only": false,
"chosen": true
}
@@ -5954,20 +5954,20 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 291.7205078,
+ "cost_for_plan": 262.2455078,
"semijoin_strategy_choice": [
{
"strategy": "DuplicateWeedout",
"records": 27,
- "dups_cost": 291.7205078,
+ "dups_cost": 262.2455078,
"write_cost": 5.05,
"full_lookup_cost": 109.35,
- "total_cost": 406.1205078
+ "total_cost": 376.6455078
},
{
"chosen_strategy": "DuplicateWeedout"
@@ -5988,7 +5988,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 86.80256348,
+ "cost": 77.69006348,
"index_only": false,
"chosen": true
}
@@ -5997,12 +5997,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 86.80256348,
+ "cost": 77.69006348,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 120.1153809,
+ "cost_for_plan": 108.8653809,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6023,7 +6023,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6032,12 +6032,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 998.1730713,
+ "cost_for_plan": 877.5730713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -6059,7 +6059,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6068,12 +6068,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 998.1730713,
+ "cost_for_plan": 877.5730713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -6092,7 +6092,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"index_only": false,
"chosen": true
}
@@ -6101,12 +6101,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 291.7205078,
+ "cost_for_plan": 262.2455078,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -6125,7 +6125,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"index_only": false,
"chosen": true
}
@@ -6134,12 +6134,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 15.60769043,
+ "cost_for_plan": 14.82019043,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6155,7 +6155,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -6164,12 +6164,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6190,7 +6190,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6199,12 +6199,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 981.9730713,
+ "cost_for_plan": 862.7230713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -6226,7 +6226,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6235,12 +6235,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 981.9730713,
+ "cost_for_plan": 862.7230713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -6259,7 +6259,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -6268,12 +6268,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -6290,7 +6290,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -6299,12 +6299,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -6323,7 +6323,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -6332,12 +6332,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6353,7 +6353,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"index_only": false,
"chosen": true
}
@@ -6362,12 +6362,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 291.7205078,
+ "cost_for_plan": 262.2455078,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6388,7 +6388,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 2627.65769,
+ "cost": 2299.60769,
"index_only": false,
"chosen": true
}
@@ -6397,12 +6397,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 2627.65769,
+ "cost": 2299.60769,
"uses_join_buffering": true
}
},
"rows_for_plan": 6561,
- "cost_for_plan": 2919.378198,
+ "cost_for_plan": 2561.853198,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -6424,7 +6424,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 876.5525635,
+ "cost": 767.2025635,
"index_only": false,
"chosen": true
}
@@ -6433,12 +6433,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 876.5525635,
+ "cost": 767.2025635,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 1168.273071,
+ "cost_for_plan": 1029.448071,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -6457,7 +6457,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"index_only": false,
"chosen": true
}
@@ -6466,12 +6466,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 258.4076904,
+ "cost": 231.0701904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 291.7205078,
+ "cost_for_plan": 262.2455078,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -6488,7 +6488,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 86.80256348,
+ "cost": 77.69006348,
"index_only": false,
"chosen": true
}
@@ -6497,12 +6497,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 86.80256348,
+ "cost": 77.69006348,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 120.1153809,
+ "cost_for_plan": 108.8653809,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -6523,7 +6523,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6532,12 +6532,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 998.1730713,
+ "cost_for_plan": 877.5730713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -6559,7 +6559,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"index_only": false,
"chosen": true
}
@@ -6568,12 +6568,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 878.0576904,
+ "cost": 768.7076904,
"uses_join_buffering": true
}
},
"rows_for_plan": 2187,
- "cost_for_plan": 998.1730713,
+ "cost_for_plan": 877.5730713,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -6596,7 +6596,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -6605,12 +6605,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -6627,7 +6627,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -6636,12 +6636,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -6658,7 +6658,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -6667,12 +6667,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -6689,7 +6689,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -6698,12 +6698,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -6995,7 +6995,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
],
"best_access_method": {
"rows": 27,
- "cost": 360.2292969
+ "cost": 359.2167969
}
},
{
@@ -7319,7 +7319,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -7328,12 +7328,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391
+ "cost_for_plan": 11.42275391
}
]
},
@@ -7413,7 +7413,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -7422,12 +7422,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391
+ "cost_for_plan": 11.42275391
}
]
},
@@ -7511,7 +7511,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -7520,12 +7520,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -7541,7 +7541,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -7550,12 +7550,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
@@ -7565,15 +7565,15 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
{
"strategy": "SJ-Materialization",
"records": 3,
- "cost": 19.46281738
+ "cost": 19.12531738
},
{
"strategy": "DuplicateWeedout",
"records": 3,
- "dups_cost": 33.31281738,
+ "dups_cost": 31.17531738,
"write_cost": 1.45,
"full_lookup_cost": 12.15,
- "total_cost": 46.91281738
+ "total_cost": 44.77531738
},
{
"chosen_strategy": "SJ-Materialization"
@@ -7593,7 +7593,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"index_only": false,
"chosen": true
}
@@ -7602,12 +7602,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 32.17050781,
+ "cost_for_plan": 30.82050781,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -7628,7 +7628,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 34.15256348,
+ "cost": 30.10256348,
"index_only": false,
"chosen": true
}
@@ -7637,12 +7637,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 34.15256348,
+ "cost": 30.10256348,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 66.32307129,
+ "cost_for_plan": 60.92307129,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -7664,7 +7664,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"index_only": false,
"chosen": true
}
@@ -7673,30 +7673,30 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 331.3076904,
+ "cost": 285.7451904,
"uses_join_buffering": true
}
},
"rows_for_plan": 729,
- "cost_for_plan": 397.6307617,
+ "cost_for_plan": 346.6682617,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
"records": 27,
- "cost": 343.3626465
+ "cost": 342.0126465
},
{
"strategy": "SJ-Materialization",
"records": 27,
- "cost": 57.08076172
+ "cost": 55.39326172
},
{
"strategy": "DuplicateWeedout",
"records": 27,
- "dups_cost": 397.6307617,
+ "dups_cost": 346.6682617,
"write_cost": 5.05,
"full_lookup_cost": 109.35,
- "total_cost": 512.0307617
+ "total_cost": 461.0682617
},
{
"chosen_strategy": "SJ-Materialization"
@@ -7723,7 +7723,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 100.4576904,
+ "cost": 88.30769043,
"index_only": false,
"chosen": true
}
@@ -7732,12 +7732,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 100.4576904,
+ "cost": 88.30769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 132.6281982,
+ "cost_for_plan": 119.1281982,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -7756,7 +7756,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.902563477,
+ "cost": 4.565063477,
"index_only": false,
"chosen": true
}
@@ -7765,12 +7765,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.902563477,
+ "cost": 4.565063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 24.36538086,
+ "cost_for_plan": 23.69038086,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -7791,7 +7791,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"index_only": false,
"chosen": true
}
@@ -7800,14 +7800,64 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 60.02307129,
+ "cost_for_plan": 55.29807129,
"semijoin_strategy_choice": [],
- "pruned_by_cost": true
+ "rest_of_plan": [
+ {
+ "plan_prefix": [
+ "t_outer_1",
+ "t_inner_1",
+ "t_inner_2",
+ "t_inner_4",
+ "t_outer_2"
+ ],
+ "table": "t_inner_3",
+ "best_access_path": {
+ "plan_details": {
+ "record_count": 81
+ },
+ "considered_access_paths": [
+ {
+ "access_type": "scan_with_join_cache",
+ "rows": 9,
+ "rows_after_scan": 9,
+ "rows_after_filter": 9,
+ "cost": 285.7451904,
+ "index_only": false,
+ "chosen": true
+ }
+ ],
+ "chosen_access_method": {
+ "type": "scan",
+ "records_read": 9,
+ "records_out": 9,
+ "cost": 285.7451904,
+ "uses_join_buffering": true
+ }
+ },
+ "rows_for_plan": 729,
+ "cost_for_plan": 341.0432617,
+ "semijoin_strategy_choice": [
+ {
+ "strategy": "DuplicateWeedout",
+ "records": 27,
+ "dups_cost": 341.0432617,
+ "write_cost": 5.05,
+ "full_lookup_cost": 109.35,
+ "total_cost": 455.4432617
+ },
+ {
+ "chosen_strategy": "DuplicateWeedout"
+ }
+ ],
+ "pruned_by_cost": true
+ }
+ ]
},
{
"plan_prefix": [
@@ -7827,7 +7877,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"index_only": false,
"chosen": true
}
@@ -7836,14 +7886,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 35.65769043,
+ "cost": 31.60769043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 60.02307129,
+ "cost_for_plan": 55.29807129,
"semijoin_strategy_choice": [],
- "pruned_by_cost": true
+ "pruned_by_heuristic": true
}
]
},
@@ -7860,7 +7910,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"index_only": false,
"chosen": true
}
@@ -7869,12 +7919,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 12.70769043,
+ "cost": 11.69519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 32.17050781,
+ "cost_for_plan": 30.82050781,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -7893,7 +7943,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -7902,12 +7952,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -7924,7 +7974,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"index_only": false,
"chosen": true
}
@@ -7933,12 +7983,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 9.852563477,
+ "cost": 9.177563477,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 15.60769043,
+ "cost_for_plan": 14.82019043,
"semijoin_strategy_choice": [],
"rest_of_plan": [
{
@@ -7954,7 +8004,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -7963,12 +8013,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -7985,7 +8035,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -7994,12 +8044,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
},
@@ -8016,7 +8066,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"index_only": false,
"chosen": true
}
@@ -8025,12 +8075,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 88.30769043,
+ "cost": 79.19519043,
"uses_join_buffering": true
}
},
"rows_for_plan": 243,
- "cost_for_plan": 103.9153809,
+ "cost_for_plan": 94.01538086,
"semijoin_strategy_choice": [],
"pruned_by_cost": true
}
@@ -8049,7 +8099,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"index_only": false,
"chosen": true
}
@@ -8058,12 +8108,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 27.55769043,
+ "cost": 25.53269043,
"uses_join_buffering": true
}
},
"rows_for_plan": 81,
- "cost_for_plan": 33.31281738,
+ "cost_for_plan": 31.17531738,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -8082,7 +8132,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -8091,12 +8141,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -8113,7 +8163,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -8122,12 +8172,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -8144,7 +8194,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 3,
"rows_after_scan": 3,
"rows_after_filter": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"index_only": false,
"chosen": true
}
@@ -8153,12 +8203,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 3,
"records_out": 3,
- "cost": 4.002563477,
+ "cost": 3.890063477,
"uses_join_buffering": true
}
},
"rows_for_plan": 9,
- "cost_for_plan": 5.755126953,
+ "cost_for_plan": 5.642626953,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
},
@@ -8175,7 +8225,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"rows": 9,
"rows_after_scan": 9,
"rows_after_filter": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"index_only": false,
"chosen": true
}
@@ -8184,12 +8234,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
"type": "scan",
"records_read": 9,
"records_out": 9,
- "cost": 10.00769043,
+ "cost": 9.67019043,
"uses_join_buffering": true
}
},
"rows_for_plan": 27,
- "cost_for_plan": 11.76025391,
+ "cost_for_plan": 11.42275391,
"semijoin_strategy_choice": [],
"pruned_by_heuristic": true
}
@@ -8387,7 +8437,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) {
],
"best_access_method": {
"rows": 27,
- "cost": 57.08076172
+ "cost": 55.39326172
}
},
{
@@ -9221,7 +9271,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"rows": 1000,
"rows_after_scan": 603.515625,
"rows_after_filter": 603.515625,
- "cost": 1760.887695,
+ "cost": 1685.448242,
"index_only": false,
"chosen": false
}
@@ -9337,9 +9387,9 @@ insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
explain
select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where
-1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Using where
+1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout'))
[
@@ -9469,7 +9519,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
"rows": 100,
"rows_after_scan": 75,
"rows_after_filter": 75,
- "cost": 213.6098633,
+ "cost": 204.2348633,
"index_only": false,
"chosen": false
}
@@ -10836,7 +10886,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and
"rows": 10000,
"rows_after_scan": 353.71875,
"rows_after_filter": 353.71875,
- "cost": 2782.158203,
+ "cost": 2768.89375,
"index_only": false,
"chosen": false
}
diff --git a/mysql-test/main/select_safe.result b/mysql-test/main/select_safe.result
index 90ed1a4e785..7ad5d58150d 100644
--- a/mysql-test/main/select_safe.result
+++ b/mysql-test/main/select_safe.result
@@ -67,6 +67,7 @@ test.t1 analyze status OK
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e");
insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v");
+set @@optimizer_where_compare_cost=0.3;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL b NULL NULL NULL 11
diff --git a/mysql-test/main/select_safe.test b/mysql-test/main/select_safe.test
index c76e337cd10..a32405278c2 100644
--- a/mysql-test/main/select_safe.test
+++ b/mysql-test/main/select_safe.test
@@ -60,6 +60,7 @@ analyze table t1;
insert into t1 values (null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a"),(null,"a");
insert into t1 values (null,"b"),(null,"b"),(null,"c"),(null,"c"),(null,"d"),(null,"d"),(null,"e"),(null,"e"),(null,"a"),(null,"e");
insert into t1 values (null,"x"),(null,"x"),(null,"y"),(null,"y"),(null,"z"),(null,"z"),(null,"v"),(null,"v"),(null,"a"),(null,"v");
+set @@optimizer_where_compare_cost=0.3;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
set MAX_SEEKS_FOR_KEY=1;
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
diff --git a/mysql-test/main/status.result b/mysql-test/main/status.result
index 22f40392609..b34713a4c73 100644
--- a/mysql-test/main/status.result
+++ b/mysql-test/main/status.result
@@ -173,7 +173,7 @@ a a
1 1
SHOW SESSION STATUS LIKE 'Last_query_cost';
Variable_name Value
-Last_query_cost 4.003418
+Last_query_cost 3.953418
DROP TABLE t1;
connect con1,localhost,root,,;
show status like 'com_show_status';
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 6b1ba12cea1..dcd1174caf9 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -7045,8 +7045,8 @@ SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index 7c12b2f1aa5..04b917c93ab 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -1277,10 +1277,9 @@ EXPLAIN
SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 Const row not found
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3
-1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
-2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
c1 c1 c1
EXPLAIN
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index e2ab6ae8a69..4c71c1aee2a 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -7046,8 +7046,8 @@ SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index b3d382889a3..4b5e745211a 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -7039,8 +7039,8 @@ SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 9246e09074d..020b7f40398 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -7051,8 +7051,8 @@ SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index 0c5a21303c7..a5a62a2b65d 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -928,8 +928,8 @@ a t1.a in (select t2.a from t2,t3 where t3.a=t2.a)
explain extended SELECT t1.a, t1.a in (select t2.a from t2,t3 where t3.a=t2.a) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
-2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00 Using where
-2 MATERIALIZED t2 ref a a 5 test.t3.a 2 100.00 Using index
+2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 100.00
+2 MATERIALIZED t2 index a a 5 NULL 3 75.00 Using where; Using index; Using join buffer (flat, BNL join)
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a` from `test`.`t2` join `test`.`t3` where `test`.`t2`.`a` = `test`.`t3`.`a` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`a`)))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from `test`.`t1`
drop table t1,t2,t3;
@@ -7039,8 +7039,8 @@ SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index a a 5 NULL 2 Using where; Using index
-2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 test.t2.c 1
+2 SUBQUERY <subquery3> ALL distinct_key NULL NULL NULL 1
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2
SELECT * FROM t1
WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index 67df72d2209..97e35cf99b4 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -205,10 +205,10 @@ a b a b
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
+1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where `test`.`t10`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t10`.`pk` and `test`.`t10`.`pk` < 3
drop table t0, t1, t2;
drop table t10, t11, t12;
@@ -2091,9 +2091,9 @@ explain
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Start temporary
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; End temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
f1 f2 f3 f3
2 0 0 0
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index dafb6de702d..1693da4810e 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -216,10 +216,10 @@ a b a b
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
+1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where `test`.`t10`.`pk` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3
+Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t10`.`pk` and `test`.`t10`.`pk` < 3
drop table t0, t1, t2;
drop table t10, t11, t12;
@@ -2102,9 +2102,9 @@ explain
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Start temporary; Using join buffer (flat, BNL join)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (incremental, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary; Using join buffer (incremental, BNL join)
SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
f1 f2 f3 f3
2 0 0 0
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 4327e12394c..0e2e158c639 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -261,6 +261,13 @@ static inline double cache_hit_ratio(uint ratio)
#define ROW_COPY_COST_THD(THD) ((THD)->variables.optimizer_row_copy_cost)
/*
+ Creating a record from the join cache is faster than getting a row from
+ the engine. JOIN_CACHE_ROW_COPY_COST_FACTOR is the factor used to
+ take this into account. This is multiplied with ROW_COPY_COST.
+*/
+#define JOIN_CACHE_ROW_COPY_COST_FACTOR 0.75
+
+/*
Cost of finding the next key during index scan and copying it to
'table->record'
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 179ae33d00f..d3326cf72a4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8594,7 +8594,8 @@ best_access_path(JOIN *join,
row combinations, only a HASH_FANOUT (10%) rows in the cache.
*/
cmp_time= (rnd_records * record_count * HASH_FANOUT *
- WHERE_COMPARE_COST_THD(thd));
+ (ROW_COPY_COST_THD(thd) * JOIN_CACHE_ROW_COPY_COST_FACTOR +
+ WHERE_COMPARE_COST_THD(thd)));
tmp= COST_ADD(tmp, cmp_time);
best_cost= tmp;
@@ -8824,7 +8825,8 @@ best_access_path(JOIN *join,
row.
*/
cmp_time= (rnd_records * record_count *
- (ROW_COPY_COST_THD(thd) * (idx - join->const_tables) +
+ (ROW_COPY_COST_THD(thd) * (idx - join->const_tables) *
+ JOIN_CACHE_ROW_COPY_COST_FACTOR +
WHERE_COMPARE_COST_THD(thd)));
tmp= COST_ADD(tmp, cmp_time);
}
View Lorry Controller Status