Update cost for hash and cached joins

The old code did not't correctly add TIME_FOR_COMPARE to rows that are
part of the scan that will be compared with the attached where clause.

Now the cost calculation for hash join and full join cache join are
identical except for HASH_FANOUT (10%)

The cost for a join with keys is now also uniform.
The total cost for a using a key for lookup is calculated in one place as:

(cost_of_finding_rows_through_key(records) + records/TIME_FOR_COMPARE)*
record_count_of_previous_row_combinations + startup_cost

startup_cost is the cost of a creating a temporary table (if needed)

Best_cost now includes the cost of comparing all WHERE clauses and also
cost of joining with previous row combinations.

Other things:
- Optimizer trace is now printing the total costs, including testing the
  WHERE clause (TIME_FOR_COMPARE) and comparing with all previous rows.
- In optimizer trace, include also total cost of query together with the
  final join order. This makes it easier to find out where the cost was
  calculated.
- Old code used filter even if the cost for it was higher than not using a
  filter. This is not corrected.
- When rebasing on 10.11, I noticed some changes to access_cost_factor
  calculation. These changes was not picked as the coming changes
  to filtering will make that code obsolete.

1 files changed, 9 insertions, 10 deletions

diff --git a/mysql-test/main/subselect_sj2_jcl6.result b/mysql-test/main/subselect_sj2_jcl6.result
index 83abb68ca51..0539ab03a84 100644
--- a/mysql-test/main/subselect_sj2_jcl6.result
+++ b/mysql-test/main/subselect_sj2_jcl6.result
@@ -142,9 +142,8 @@ set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * from t1 where a in (select b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t2	index	b	b	5	NULL	20	Using index
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ref	b	b	5	test.t1.a	1	Using index; FirstMatch(t1)
 select * from t1;
 a	b
 1	1
@@ -853,9 +852,9 @@ SELECT * FROM t3
 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	alias1	const	PRIMARY	PRIMARY	4	const	#	Using index
-1	PRIMARY	alias2	index	f12	f12	7	NULL	#	Using index; LooseScan
-1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	#	Using index; FirstMatch(alias2)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	#	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	alias2	index	f12	f12	7	NULL	#	Using index; Start temporary
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	#	Using index; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	3	test.alias2.f12	#	Using where; End temporary; Using join buffer (incremental, BNLH join)
 SELECT * FROM t3 
 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24);
 f12
@@ -924,9 +923,9 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	4	test.t3.a	1	Using where; Using join buffer (flat, BNLH join)
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
@@ -1238,9 +1237,9 @@ explain
 SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	PRIMARY	PRIMARY	5	const	1	Using where; Using index
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Start temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	t4	hash_index	NULL	#hash#$hj:PRIMARY	54:59	test.t3.t3_c	2	Using where; Using index; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	hash_index	NULL	#hash#$hj:PRIMARY	54:59	test.t2.t2_c	2	Using where; Using index; Using join buffer (incremental, BNLH join)
+1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	38	test.t2.t2_c	2	Using where; End temporary; Using join buffer (incremental, BNLH join)
 DROP TABLE t1,t2,t3,t4;
 #
 # MDEV-6263: Wrong result when using IN subquery with order by