Fixed cost calculations for materialized tables

One effect of this change in the test suite is that tests with very few
rows changed to use sub queries instead of materialization. This is
correct and expected as for these the materialization overhead is too high.

A lot of tests where fixed to still use materialization by adding a
few rows to the tables (most tests has only 2-3 rows and are thus easily
affected when cost computations are changed).

Other things:
- Added more variables to TMPTABLE_COSTS for better cost calculation
- Added cost of copying rows to TMPTABLE_COSTS lookup and write
- Added THD::optimizer_cache_hit_ratio for easier cost calculations
- Added DISK_FAST_READ_SIZE to be used when calculating costs when
  reading big blocks from a disk

1 files changed, 15 insertions, 5 deletions

diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result
index fe6ef646886..36063f3e594 100644
--- a/mysql-test/main/subselect3.result
+++ b/mysql-test/main/subselect3.result
@@ -1180,9 +1180,8 @@ create table t3 ( a int , filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	End temporary
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -1336,6 +1335,9 @@ insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 as select a as a, a as b, a as c from t0 where a < 3;
 create table t2 as select a as a, a as b from t0 where a < 3;
 insert into t2 select * from t2;
+select count(*) from t2;
+count(*)
+6
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
@@ -1391,7 +1393,7 @@ drop table t0, t1;
 create table t1 (
 idIndividual int primary key
 );
-insert into t1 values (1),(2);
+insert into t1 values (1),(2),(1000);
 create table t2 (
 idContact int primary key,
 contactType int,
@@ -1403,7 +1405,7 @@ idAddress int primary key,
 idContact int,
 postalStripped varchar(100)
 );
-insert into t3 values (1,1, 'foo'), (2,2,'bar');
+insert into t3 values (1,1, 'foo'), (2,2,'T2H3B2');
 The following must be converted to a semi-join:
 set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch='materialization=off';
@@ -1419,6 +1421,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	a	eq_ref	PRIMARY	PRIMARY	4	test.c.idObj	1	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where `test`.`cona`.`postalStripped` = 'T2H3B2' and `test`.`a`.`idIndividual` = `test`.`c`.`idObj` and `test`.`c`.`idContact` = `test`.`cona`.`idContact`
+SELECT a.idIndividual FROM t1 a 
+WHERE a.idIndividual IN 
+(	SELECT c.idObj FROM t3 cona 
+INNER JOIN t2 c ON c.idContact=cona.idContact 
+WHERE cona.postalStripped='T2H3B2'
+	);
+idIndividual
+2
 set @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #