The pilot implementation of mwl#250: Use the statistics from persistent

statistical tables instead of the statistics provided by engine.

15 files changed, 706 insertions, 52 deletions

diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result
new file mode 100644
index 00000000000..089c7871506
--- /dev/null
+++ b/mysql-test/r/stat_tables.result
@@ -0,0 +1,229 @@
+set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
+set optimizer_use_stat_tables='preferably';
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+select * from mysql.table_stat;
+db_name	table_name	cardinality
+dbt3_s001	customer	150
+dbt3_s001	lineitem	6005
+dbt3_s001	nation	25
+dbt3_s001	orders	1500
+dbt3_s001	part	200
+dbt3_s001	partsupp	700
+dbt3_s001	region	5
+dbt3_s001	supplier	10
+select * from mysql.index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	customer	PRIMARY	1	1
+dbt3_s001	customer	i_c_nationkey	1	6
+dbt3_s001	lineitem	PRIMARY	1	4.003333333333333
+dbt3_s001	lineitem	PRIMARY	2	1
+dbt3_s001	lineitem	i_l_shipdate	1	2.650044130626655
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.57857142857143
+dbt3_s001	lineitem	i_l_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey	1	600.5
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477072310405645
+dbt3_s001	lineitem	i_l_orderkey	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0403672903672905
+dbt3_s001	lineitem	i_l_commitdate	1	2.7159656264133876
+dbt3_s001	nation	PRIMARY	1	1
+dbt3_s001	nation	i_n_regionkey	1	5
+dbt3_s001	orders	PRIMARY	1	1
+dbt3_s001	orders	i_o_orderdate	1	1.3321492007104796
+dbt3_s001	orders	i_o_custkey	1	15
+dbt3_s001	part	PRIMARY	1	1
+dbt3_s001	part	i_p_retailprice	1	1
+dbt3_s001	partsupp	PRIMARY	1	3.5
+dbt3_s001	partsupp	PRIMARY	2	1
+dbt3_s001	partsupp	i_ps_partkey	1	3.5
+dbt3_s001	partsupp	i_ps_suppkey	1	70
+dbt3_s001	region	PRIMARY	1	1
+dbt3_s001	supplier	PRIMARY	1	1
+dbt3_s001	supplier	i_s_nationkey	1	1.1111111111111112
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	range	PRIMARY,i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	179	Using where; Using temporary; Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
+select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+n_name	revenue
+PERU	321915.8715
+ARGENTINA	69817.1451
+set optimizer_switch=@save_optimizer_switch;
+EXPLAIN select o_year,
+sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+l_extendedprice * (1-l_discount) as volume, 
+n2.n_name as nation
+from part, supplier, lineitem, orders, customer, 
+nation n1, nation n2, region
+where p_partkey = l_partkey and s_suppkey = l_suppkey
+and l_orderkey = o_orderkey and o_custkey = c_custkey
+and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	ALL	PRIMARY,i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where; Using temporary; Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
+1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+select o_year,
+sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+l_extendedprice * (1-l_discount) as volume, 
+n2.n_name as nation
+from part, supplier, lineitem, orders, customer, 
+nation n1, nation n2, region
+where p_partkey = l_partkey and s_suppkey = l_suppkey
+and l_orderkey = o_orderkey and o_custkey = c_custkey
+and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+o_year	mkt_share
+1995	0.4495521838895718
+1996	0.024585468215352495
+EXPLAIN select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+from part, supplier, lineitem, partsupp, orders, nation
+where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey and p_partkey = l_partkey
+and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
+1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+1	SIMPLE	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey	8	
+1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
+select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+from part, supplier, lineitem, partsupp, orders, nation
+where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey and p_partkey = l_partkey
+and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+nation	o_year	sum_profit
+ARGENTINA	1997	18247.873399999993
+ARGENTINA	1996	7731.089399999995
+ARGENTINA	1995	134490.5697
+ARGENTINA	1994	36767.101500000004
+ARGENTINA	1993	35857.08
+ARGENTINA	1992	35740
+ETHIOPIA	1998	2758.7801999999992
+ETHIOPIA	1997	19419.294599999997
+ETHIOPIA	1995	51231.87439999999
+ETHIOPIA	1994	3578.9478999999974
+ETHIOPIA	1992	1525.8234999999986
+IRAN	1998	37817.229600000006
+IRAN	1997	52643.77359999999
+IRAN	1996	70143.7761
+IRAN	1995	84094.58260000001
+IRAN	1994	18140.925599999995
+IRAN	1993	78655.1676
+IRAN	1992	87142.23960000002
+IRAQ	1998	22860.8082
+IRAQ	1997	93676.24359999999
+IRAQ	1996	45103.3242
+IRAQ	1994	36010.728599999995
+IRAQ	1993	33221.9399
+IRAQ	1992	47755.05900000001
+KENYA	1998	44194.831999999995
+KENYA	1997	57578.36259999999
+KENYA	1996	59195.90210000001
+KENYA	1995	79262.6278
+KENYA	1994	102360.66609999999
+KENYA	1993	128422.0196
+KENYA	1992	181517.2089
+MOROCCO	1998	41797.823199999984
+MOROCCO	1997	23685.801799999994
+MOROCCO	1996	62115.19579999998
+MOROCCO	1995	42442.64300000001
+MOROCCO	1994	48655.878000000004
+MOROCCO	1993	22926.744400000003
+MOROCCO	1992	32239.8088
+PERU	1998	86999.36459999997
+PERU	1997	121110.41070000001
+PERU	1996	177040.40759999995
+PERU	1995	122247.94520000002
+PERU	1994	88046.25329999998
+PERU	1993	49379.813799999996
+PERU	1992	80646.86050000001
+UNITED KINGDOM	1998	50577.25560000001
+UNITED KINGDOM	1997	114288.8605
+UNITED KINGDOM	1996	147684.46480000002
+UNITED KINGDOM	1995	225267.65759999998
+UNITED KINGDOM	1994	140595.5864
+UNITED KINGDOM	1993	322548.49210000003
+UNITED KINGDOM	1992	67747.88279999999
+UNITED STATES	1998	3957.0431999999996
+UNITED STATES	1997	94729.5704
+UNITED STATES	1996	79297.85670000002
+UNITED STATES	1995	62201.23360000001
+UNITED STATES	1994	43075.629899999985
+UNITED STATES	1993	27168.486199999996
+UNITED STATES	1992	34092.366
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+EXPLAIN select o_orderkey, p_partkey
+from part, lineitem, orders
+where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+and o_orderkey=l_orderkey and p_partkey=l_partkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	1	
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	part	eq_ref	PRIMARY,i_p_retailprice	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+select o_orderkey, p_partkey
+from part, lineitem, orders
+where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+and o_orderkey=l_orderkey and p_partkey=l_partkey;
+o_orderkey	p_partkey
+5895	200
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+use test;
+set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result
new file mode 100644
index 00000000000..005e4c4828e
--- /dev/null
+++ b/mysql-test/r/stat_tables_innodb.result
@@ -0,0 +1,231 @@
+SET SESSION STORAGE_ENGINE='InnoDB';
+set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
+set optimizer_use_stat_tables='preferably';
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+select * from mysql.table_stat;
+db_name	table_name	cardinality
+dbt3_s001	customer	150
+dbt3_s001	lineitem	6005
+dbt3_s001	nation	25
+dbt3_s001	orders	1500
+dbt3_s001	part	200
+dbt3_s001	partsupp	700
+dbt3_s001	region	5
+dbt3_s001	supplier	10
+select * from mysql.index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	customer	PRIMARY	1	1
+dbt3_s001	customer	i_c_nationkey	1	6
+dbt3_s001	lineitem	PRIMARY	1	4.003333333333333
+dbt3_s001	lineitem	PRIMARY	2	1
+dbt3_s001	lineitem	i_l_shipdate	1	2.650044130626655
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.57857142857143
+dbt3_s001	lineitem	i_l_partkey	1	30.025
+dbt3_s001	lineitem	i_l_suppkey	1	600.5
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477072310405645
+dbt3_s001	lineitem	i_l_orderkey	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.003333333333333
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0403672903672905
+dbt3_s001	lineitem	i_l_commitdate	1	2.7159656264133876
+dbt3_s001	nation	PRIMARY	1	1
+dbt3_s001	nation	i_n_regionkey	1	5
+dbt3_s001	orders	PRIMARY	1	1
+dbt3_s001	orders	i_o_orderdate	1	1.3321492007104796
+dbt3_s001	orders	i_o_custkey	1	15
+dbt3_s001	part	PRIMARY	1	1
+dbt3_s001	part	i_p_retailprice	1	1
+dbt3_s001	partsupp	PRIMARY	1	3.5
+dbt3_s001	partsupp	PRIMARY	2	1
+dbt3_s001	partsupp	i_ps_partkey	1	3.5
+dbt3_s001	partsupp	i_ps_suppkey	1	70
+dbt3_s001	region	PRIMARY	1	1
+dbt3_s001	supplier	PRIMARY	1	1
+dbt3_s001	supplier	i_s_nationkey	1	1.1111111111111112
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	range	PRIMARY,i_o_orderdate,i_o_custkey	i_o_orderdate	4	NULL	211	Using where; Using temporary; Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	nation	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.customer.c_nationkey	1	Using index
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+n_name	revenue
+PERU	321915.8715
+ARGENTINA	69817.1451
+set optimizer_switch=@save_optimizer_switch;
+EXPLAIN select o_year,
+sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+l_extendedprice * (1-l_discount) as volume, 
+n2.n_name as nation
+from part, supplier, lineitem, orders, customer, 
+nation n1, nation n2, region
+where p_partkey = l_partkey and s_suppkey = l_suppkey
+and l_orderkey = o_orderkey and o_custkey = c_custkey
+and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	orders	ALL	PRIMARY,i_o_orderdate,i_o_custkey	NULL	NULL	NULL	1500	Using where; Using temporary; Using filesort
+1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
+1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	
+1	SIMPLE	region	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	dbt3_s001.orders.o_orderkey	4	Using where
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_partkey	1	Using where
+1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
+1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+select o_year,
+sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+l_extendedprice * (1-l_discount) as volume, 
+n2.n_name as nation
+from part, supplier, lineitem, orders, customer, 
+nation n1, nation n2, region
+where p_partkey = l_partkey and s_suppkey = l_suppkey
+and l_orderkey = o_orderkey and o_custkey = c_custkey
+and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+o_year	mkt_share
+1995	0.4495521838895718
+1996	0.024585468215352495
+EXPLAIN select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+from part, supplier, lineitem, partsupp, orders, nation
+where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey and p_partkey = l_partkey
+and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	supplier	index	PRIMARY,i_s_nationkey	i_s_nationkey	5	NULL	10	Using where; Using index; Using temporary; Using filesort
+1	SIMPLE	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
+1	SIMPLE	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	
+1	SIMPLE	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	Using where
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey	8	
+1	SIMPLE	orders	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	
+select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+extract(year from o_orderdate) as o_year,
+l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+from part, supplier, lineitem, partsupp, orders, nation
+where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+and ps_partkey = l_partkey and p_partkey = l_partkey
+and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+nation	o_year	sum_profit
+ARGENTINA	1997	18247.873399999993
+ARGENTINA	1996	7731.089399999995
+ARGENTINA	1995	134490.5697
+ARGENTINA	1994	36767.101500000004
+ARGENTINA	1993	35857.08
+ARGENTINA	1992	35740
+ETHIOPIA	1998	2758.7801999999992
+ETHIOPIA	1997	19419.294599999997
+ETHIOPIA	1995	51231.87439999999
+ETHIOPIA	1994	3578.9478999999974
+ETHIOPIA	1992	1525.8234999999986
+IRAN	1998	37817.229600000006
+IRAN	1997	52643.77359999999
+IRAN	1996	70143.7761
+IRAN	1995	84094.58260000001
+IRAN	1994	18140.925599999995
+IRAN	1993	78655.1676
+IRAN	1992	87142.23960000002
+IRAQ	1998	22860.8082
+IRAQ	1997	93676.24359999999
+IRAQ	1996	45103.3242
+IRAQ	1994	36010.728599999995
+IRAQ	1993	33221.9399
+IRAQ	1992	47755.05900000001
+KENYA	1998	44194.831999999995
+KENYA	1997	57578.36259999999
+KENYA	1996	59195.90210000001
+KENYA	1995	79262.6278
+KENYA	1994	102360.66609999999
+KENYA	1993	128422.0196
+KENYA	1992	181517.2089
+MOROCCO	1998	41797.823199999984
+MOROCCO	1997	23685.801799999994
+MOROCCO	1996	62115.19579999998
+MOROCCO	1995	42442.64300000001
+MOROCCO	1994	48655.878000000004
+MOROCCO	1993	22926.744400000003
+MOROCCO	1992	32239.8088
+PERU	1998	86999.36459999997
+PERU	1997	121110.41070000001
+PERU	1996	177040.40759999995
+PERU	1995	122247.94520000002
+PERU	1994	88046.25329999998
+PERU	1993	49379.813799999996
+PERU	1992	80646.86050000001
+UNITED KINGDOM	1998	50577.25560000001
+UNITED KINGDOM	1997	114288.8605
+UNITED KINGDOM	1996	147684.46480000002
+UNITED KINGDOM	1995	225267.65759999998
+UNITED KINGDOM	1994	140595.5864
+UNITED KINGDOM	1993	322548.49210000003
+UNITED KINGDOM	1992	67747.88279999999
+UNITED STATES	1998	3957.0431999999996
+UNITED STATES	1997	94729.5704
+UNITED STATES	1996	79297.85670000002
+UNITED STATES	1995	62201.23360000001
+UNITED STATES	1994	43075.629899999985
+UNITED STATES	1993	27168.486199999996
+UNITED STATES	1992	34092.366
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+EXPLAIN select o_orderkey, p_partkey
+from part, lineitem, orders
+where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+and o_orderkey=l_orderkey and p_partkey=l_partkey;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	part	range	PRIMARY,i_p_retailprice	i_p_retailprice	9	NULL	1	Using where; Using index
+1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	1	Using index
+1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity	i_l_partkey	9	dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey	1	Using index
+select o_orderkey, p_partkey
+from part, lineitem, orders
+where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+and o_orderkey=l_orderkey and p_partkey=l_partkey;
+o_orderkey	p_partkey
+5895	200
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+use test;
+set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test
new file mode 100644
index 00000000000..cd896abebe1
--- /dev/null
+++ b/mysql-test/t/stat_tables.test
@@ -0,0 +1,108 @@
+
+set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
+
+set optimizer_use_stat_tables='preferably';
+
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+ 
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+create index i_p_retailprice on part(p_retailprice);
+ANALYZE TABLE
+customer, lineitem, nation, orders, part, partsupp, region, supplier;
+FLUSH TABLES customer, lineitem, nation, orders, part, partsupp, region, supplier;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+select * from mysql.table_stat;
+select * from mysql.index_stat;
+
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+
+let $Q5=
+select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
+from customer, orders, lineitem, supplier, nation, region
+where c_custkey = o_custkey and l_orderkey = o_orderkey
+      and l_suppkey = s_suppkey and c_nationkey = s_nationkey
+      and s_nationkey = n_nationkey and n_regionkey = r_regionkey
+      and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
+      and o_orderdate < date '1995-01-01' + interval '1' year
+group by n_name
+order by revenue desc;
+
+eval EXPLAIN $Q5;
+eval $Q5;
+
+set optimizer_switch=@save_optimizer_switch;
+
+
+let $Q8=
+select o_year,
+       sum(case when nation = 'UNITED STATES' then volume else 0 end) /
+       sum(volume) as mkt_share
+from (select extract(year from o_orderdate) as o_year,
+             l_extendedprice * (1-l_discount) as volume, 
+             n2.n_name as nation
+      from part, supplier, lineitem, orders, customer, 
+           nation n1, nation n2, region
+      where p_partkey = l_partkey and s_suppkey = l_suppkey
+            and l_orderkey = o_orderkey and o_custkey = c_custkey
+            and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
+            and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
+            and o_orderdate between date '1995-01-01' and date '1996-12-31'
+            and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
+group by o_year
+order by o_year;
+
+eval EXPLAIN $Q8;
+eval $Q8;
+
+
+let $Q9=
+select nation, o_year, sum(amount) as sum_profit
+from (select n_name as nation, 
+      extract(year from o_orderdate) as o_year,
+      l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+      from part, supplier, lineitem, partsupp, orders, nation
+      where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
+            and ps_partkey = l_partkey and p_partkey = l_partkey
+            and o_orderkey = l_orderkey and s_nationkey = n_nationkey
+            and p_name like '%green%') as profit
+group by nation, o_year
+order by nation, o_year desc;
+
+eval EXPLAIN $Q9;
+eval $Q9;
+
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=on';
+
+let $QQ1=
+select o_orderkey, p_partkey
+  from part, lineitem, orders
+  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
+        and o_orderkey=l_orderkey and p_partkey=l_partkey;
+
+eval EXPLAIN $QQ1;
+eval $QQ1;
+
+set optimizer_switch=@save_optimizer_switch;
+
+
+DROP DATABASE dbt3_s001;
+
+use test;
+
+set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;
diff --git a/mysql-test/t/stat_tables_innodb.test b/mysql-test/t/stat_tables_innodb.test
new file mode 100644
index 00000000000..e2ed647f49f
--- /dev/null
+++ b/mysql-test/t/stat_tables_innodb.test
@@ -0,0 +1,7 @@
+--source include/have_innodb.inc
+
+SET SESSION STORAGE_ENGINE='InnoDB';
+
+--source stat_tables.test
+
+SET SESSION STORAGE_ENGINE=DEFAULT;
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index 7268491c0f4..036775cdf3e 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -1201,7 +1201,7 @@ bool DsMrr_impl::setup_buffer_sharing(uint key_size_in_keybuf,
   uint parts= my_count_bits(key_tuple_map);
   ulong rpc;
   ulonglong rowids_size= rowid_buf_elem_size;
-  if ((rpc= key_info->rec_per_key[parts - 1]))
+  if ((rpc= key_info->real_rec_per_key(parts - 1)))
     rowids_size= rowid_buf_elem_size * rpc;
 
   double fraction_for_rowids=
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 4b6df56d9a8..42a70094e2c 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -4869,11 +4869,6 @@ int mysqld_main(int argc, char **argv)
     check_performance_schema();
 #endif
 
-#if 0
-  if (! opt_bootstrap)
-    init_stat_tables_usage();
-#endif
-
   initialize_information_schema_acl();
 
   execute_ddl_log_recovery();
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 2e8b00982fc..61f73b95050 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2875,7 +2875,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   DBUG_PRINT("enter",("keys_to_use: %lu  prev_tables: %lu  const_tables: %lu",
 		      (ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables,
 		      (ulong) const_tables));
-  DBUG_PRINT("info", ("records: %lu", (ulong) head->file->stats.records));
+  DBUG_PRINT("info", ("records: %lu", (ulong) head->stat_records()));
   delete quick;
   quick=0;
   needed_reg.clear_all();
@@ -2883,7 +2883,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   DBUG_ASSERT(!head->is_filled_at_execution());
   if (keys_to_use.is_clear_all() || head->is_filled_at_execution())
     DBUG_RETURN(0);
-  records= head->file->stats.records;
+  records= head->stat_records();
   if (!records)
     records++;					/* purecov: inspected */
   scan_time= (double) records / TIME_FOR_COMPARE + 1;
@@ -3020,7 +3020,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
     if (group_trp)
     {
       param.table->quick_condition_rows= min(group_trp->records,
-                                             head->file->stats.records);
+                                             head->stat_records());
       if (group_trp->read_cost < best_read_time)
       {
         best_trp= group_trp;
@@ -4618,7 +4618,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
   DBUG_PRINT("info", ("index_merge scans cost %g", imerge_cost));
   if (imerge_too_expensive || (imerge_cost > read_time) ||
       ((non_cpk_scan_records+cpk_scan_records >=
-        param->table->file->stats.records) &&
+        param->table->stat_records()) &&
        read_time != DBL_MAX))
   {
     /*
@@ -4689,7 +4689,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick(PARAM *param, SEL_IMERGE *imerge,
       imerge_trp->read_cost= imerge_cost;
       imerge_trp->records= non_cpk_scan_records + cpk_scan_records;
       imerge_trp->records= min(imerge_trp->records,
-                               param->table->file->stats.records);
+                               param->table->stat_records());
       imerge_trp->range_scans= range_scans;
       imerge_trp->range_scans_end= range_scans + n_child_scans;
       read_time= imerge_cost;
@@ -4760,7 +4760,7 @@ skip_to_ror_scan:
         ((TRP_ROR_INTERSECT*)(*cur_roru_plan))->index_scan_costs;
     roru_total_records += (*cur_roru_plan)->records;
     roru_intersect_part *= (*cur_roru_plan)->records /
-                           param->table->file->stats.records;
+                           param->table->stat_records();
   }
 
   /*
@@ -4770,7 +4770,7 @@ skip_to_ror_scan:
     in disjunction do not share key parts.
   */
   roru_total_records -= (ha_rows)(roru_intersect_part*
-                                  param->table->file->stats.records);
+                                  param->table->stat_records());
   /* ok, got a ROR read plan for each of the disjuncts
     Calculate cost:
     cost(index_union_scan(scan_1, ... scan_n)) =
@@ -5047,12 +5047,12 @@ static inline
 ha_rows get_table_cardinality_for_index_intersect(TABLE *table)
 {
   if (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)
-    return table->file->stats.records;
+    return table->stat_records();
   else
   {
     ha_rows d;
     double q;
-    for (q= (double)table->file->stats.records, d= 1 ; q >= 10; q/= 10, d*= 10 ) ;
+    for (q= (double)table->stat_records(), d= 1 ; q >= 10; q/= 10, d*= 10 ) ;
     return (ha_rows) (floor(q+0.5) * d);
   } 
 }
@@ -5455,9 +5455,8 @@ ha_rows records_in_index_intersect_extension(PARTIAL_INDEX_INTERSECT_INFO *curr,
     ha_rows ext_records= ext_index_scan->records;
     if (i < used_key_parts)
     {
-      ulong *rec_per_key= key_info->rec_per_key+i-1;
-      ulong f1= rec_per_key[0] ? rec_per_key[0] : 1;
-      ulong f2= rec_per_key[1] ? rec_per_key[1] : 1;
+      ulong f1= key_info->real_rec_per_key(i-1);
+      ulong f2= key_info->real_rec_per_key(i);
       ext_records= (ha_rows) ((double) ext_records / f2 * f1);
     }
     if (ext_records < table_cardinality)
@@ -5949,7 +5948,7 @@ ROR_INTERSECT_INFO* ror_intersect_init(const PARAM *param)
   info->is_covering= FALSE;
   info->index_scan_costs= 0.0;
   info->index_records= 0;
-  info->out_rows= (double) param->table->file->stats.records;
+  info->out_rows= (double) param->table->stat_records();
   bitmap_clear_all(&info->covered_fields);
   return info;
 }
@@ -6075,7 +6074,7 @@ static double ror_scan_selectivity(const ROR_INTERSECT_INFO *info,
   min_range.flag= HA_READ_KEY_EXACT;
   max_range.key= key_val;
   max_range.flag= HA_READ_AFTER_KEY;
-  ha_rows prev_records= info->param->table->file->stats.records;
+  ha_rows prev_records= info->param->table->stat_records();
   DBUG_ENTER("ror_scan_selectivity");
 
   for (sel_arg= scan->sel_arg; sel_arg;
@@ -6302,7 +6301,7 @@ TRP_ROR_INTERSECT *get_best_ror_intersect(const PARAM *param, SEL_TREE *tree,
   double min_cost= DBL_MAX;
   DBUG_ENTER("get_best_ror_intersect");
 
-  if ((tree->n_ror_scans < 2) || !param->table->file->stats.records ||
+  if ((tree->n_ror_scans < 2) || !param->table->stat_records() ||
       !optimizer_flag(param->thd, OPTIMIZER_SWITCH_INDEX_MERGE_INTERSECT))
     DBUG_RETURN(NULL);
 
@@ -12570,14 +12569,14 @@ void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts,
   double cpu_cost= 0; /* TODO: CPU cost of index_read calls? */
   DBUG_ENTER("cost_group_min_max");
 
-  table_records= table->file->stats.records;
+  table_records= table->stat_records();
   keys_per_block= (table->file->stats.block_size / 2 /
                    (index_info->key_length + table->file->ref_length)
                         + 1);
   num_blocks= (uint)(table_records / keys_per_block) + 1;
 
   /* Compute the number of keys in a group. */
-  keys_per_group= index_info->rec_per_key[group_key_parts - 1];
+  keys_per_group= index_info->real_rec_per_key(group_key_parts - 1);
   if (keys_per_group == 0) /* If there is no statistics try to guess */
     /* each group contains 10% of all records */
     keys_per_group= (uint)(table_records / 10) + 1;
@@ -12597,7 +12596,7 @@ void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts,
       Compute the probability that two ends of a subgroup are inside
       different blocks.
     */
-    keys_per_subgroup= index_info->rec_per_key[used_key_parts - 1];
+    keys_per_subgroup= index_info->real_rec_per_key(used_key_parts - 1);
     if (keys_per_subgroup >= keys_per_block) /* If a subgroup is bigger than */
       p_overlap= 1.0;       /* a block, it will overlap at least two blocks. */
     else
diff --git a/sql/sql_base.h b/sql/sql_base.h
index 5b88d53d231..203f8c18e14 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -315,6 +315,7 @@ int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived);
 int read_statistics_for_table(THD *thd, TABLE *table);
 int collect_statistics_for_table(THD *thd, TABLE *table);
 int update_statistics_for_table(THD *thd, TABLE *table);
+void set_statistics_for_table(THD *thd, TABLE *table);
 
 extern "C" int simple_raw_key_cmp(void* arg, const void* key1,
                                   const void* key2);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index ff88bf7c0f8..351e38ec4f1 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -200,6 +200,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
 #endif
   /* Update the table->file->stats.records number */
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
+  set_statistics_for_table(thd, table);
 
   table->covering_keys.clear_all();
   table->quick_keys.clear_all();		// Can't use 'only index'
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index e0e1b175116..ba28cb8d0b0 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -3788,7 +3788,7 @@ uint JOIN_TAB_SCAN_MRR::aux_buffer_incr(ulong recno)
   uint incr= 0;
   TABLE_REF *ref= &join_tab->ref;
   TABLE *tab= join_tab->table;
-  uint rec_per_key= tab->key_info[ref->key].rec_per_key[ref->key_parts-1];
+  uint rec_per_key= tab->key_info[ref->key].real_rec_per_key(ref->key_parts-1);
   set_if_bigger(rec_per_key, 1);
   if (recno == 1)
     incr=  ref->key_length + tab->file->ref_length;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 35c45a9033c..9d8a4265260 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3121,6 +3121,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     table_vector[i]=s->table=table=tables->table;
     table->pos_in_table_list= tables;
     error= tables->fetch_number_of_rows();
+    set_statistics_for_table(join->thd, table);
 
     DBUG_EXECUTE_IF("bug11747970_raise_error",
                     {
@@ -3146,8 +3147,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 
     s->dependent= tables->dep_tables;
     if (tables->schema_table)
-      table->file->stats.records= 2;
-    table->quick_condition_rows= table->file->stats.records;
+      table->file->stats.records= table->used_stat_records= 2;
+    table->quick_condition_rows= table->stat_records();
 
     s->on_expr_ref= &tables->on_expr;
     if (*s->on_expr_ref)
@@ -3155,10 +3156,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
       /* s is the only inner table of an outer join */
 #ifdef WITH_PARTITION_STORAGE_ENGINE
       if (!table->is_filled_at_execution() &&
-           (!table->file->stats.records || table->no_partitions_used) && !embedding)
+	  (!table->stat_records() || table->no_partitions_used) && !embedding)
 #else
       if (!table->is_filled_at_execution() &&
-          !table->file->stats.records && !embedding)
+          !table->stat_records() && !embedding)
 #endif
       {						// Empty table
         s->dependent= 0;                        // Ignore LEFT JOIN depend.
@@ -3205,7 +3206,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     const bool no_partitions_used= FALSE;
 #endif
     if (!table->is_filled_at_execution() && 
-        (table->s->system || table->file->stats.records <= 1 ||
+        (table->s->system || table->stat_records() <= 1 ||
          no_partitions_used) &&
 	!s->dependent &&
 	(table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
@@ -3387,7 +3388,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
 	// All dep. must be constants
 	if (s->dependent & ~(found_const_table_map))
 	  continue;
-	if (table->file->stats.records <= 1L &&
+	if (table->stat_records() <= 1L &&
 	    (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
             !table->pos_in_table_list->embedding &&
 	      !((outer_join & table->map) && 
@@ -5352,7 +5353,7 @@ best_access_path(JOIN      *join,
             else
             {
               uint key_parts= table->actual_n_key_parts(keyinfo);
-              if (!(records=keyinfo->rec_per_key[key_parts-1]))
+              if (!(records= keyinfo->real_rec_per_key(key_parts-1)))
               {                                   /* Prefer longer keys */
                 records=
                   ((double) s->records / (double) rec *
@@ -5452,7 +5453,7 @@ best_access_path(JOIN      *join,
             else
             {
               /* Check if we have statistic about the distribution */
-              if ((records= keyinfo->rec_per_key[max_key_part-1]))
+              if ((records= keyinfo->real_rec_per_key(max_key_part-1)))
               {
                 /* 
                   Fix for the case where the index statistics is too
@@ -7435,6 +7436,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
   keyinfo->key_length=0;
   keyinfo->algorithm= HA_KEY_ALG_UNDEF;
   keyinfo->flags= HA_GENERATED_KEY;
+  keyinfo->is_statistics_from_stat_tables= FALSE;
   keyinfo->name= (char *) "$hj";
   keyinfo->rec_per_key= (ulong*) thd->calloc(sizeof(ulong)*key_parts);
   if (!keyinfo->rec_per_key)
@@ -10006,7 +10008,7 @@ double JOIN_TAB::scan_time()
     }
     else
     {
-      found_records= records= table->file->stats.records;
+      found_records= records= table->stat_records();
       read_time= table->file->scan_time();
       /*
         table->quick_condition_rows has already been set to
@@ -10017,7 +10019,7 @@ double JOIN_TAB::scan_time()
   }
   else
   {
-    found_records= records=table->file->stats.records;
+    found_records= records=table->stat_records();
     read_time= found_records ? (double)found_records: 10.0;// TODO:fix this stub
     res= read_time;
   }
@@ -14329,8 +14331,11 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
     keyinfo->usable_key_parts=keyinfo->key_parts= param->group_parts;
     keyinfo->ext_key_parts= keyinfo->key_parts;
     keyinfo->key_length=0;
-    keyinfo->rec_per_key=0;
+    keyinfo->rec_per_key=NULL;
+    keyinfo->read_stat.avg_frequency= NULL;
+    keyinfo->write_stat.avg_frequency= NULL;
     keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+    keyinfo->is_statistics_from_stat_tables= FALSE;
     keyinfo->name= (char*) "group_key";
     ORDER *cur_group= group;
     for (; cur_group ; cur_group= cur_group->next, key_part_info++)
@@ -14443,6 +14448,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
     keyinfo->key_length= 0;  // Will compute the sum of the parts below.
     keyinfo->name= (char*) "distinct_key";
     keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+    keyinfo->is_statistics_from_stat_tables= FALSE;
     keyinfo->rec_per_key=0;
 
     /*
@@ -18211,7 +18217,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
     uint saved_best_key_parts= 0;
     int best_key_direction= 0;
     JOIN *join= tab->join;
-    ha_rows table_records= table->file->stats.records;
+    ha_rows table_records= table->stat_records();
 
     test_if_cheaper_ordering(tab, order, table, usable_keys,
                              ref_key, select_limit,
@@ -18327,7 +18333,7 @@ check_reverse_order:
         {
           tab->ref.key= -1;
           tab->ref.key_parts= 0;
-          if (select_limit < table->file->stats.records)
+          if (select_limit < table->stat_records())
             tab->limit= select_limit;
         }
       }
@@ -18541,7 +18547,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   if (!tab->preread_init_done && tab->preread_init())
     goto err;
   if (table->s->tmp_table)
-    table->file->info(HA_STATUS_VARIABLE);	// Get record count
+    table->file->info(HA_STATUS_VARIABLE);     // Get record count
   table->sort.found_records=filesort(thd, table,join->sortorder, length,
                                      select, filesort_limit, 0,
                                      &examined_rows);
@@ -21234,7 +21240,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
                 handler->info(HA_STATUS_VARIABLE) has been called in
                 make_join_statistics()
               */
-              examined_rows= tab->table->file->stats.records;
+              examined_rows= tab->table->stat_records();
             }
           }
         }
@@ -22321,7 +22327,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
   int best_key= -1;
   bool is_best_covering= FALSE;
   double fanout= 1;
-  ha_rows table_records= table->file->stats.records;
+  ha_rows table_records= table->stat_records();
   bool group= join && join->group && order == join->group_list;
   ha_rows ref_key_quick_rows= HA_POS_ERROR;
 
@@ -22411,7 +22417,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
           if (used_key_parts > used_index_parts)
             used_pk_parts= used_key_parts-used_index_parts;
           rec_per_key= used_key_parts ?
-                       keyinfo->rec_per_key[used_key_parts-1] : 1;
+	               keyinfo->real_rec_per_key(used_key_parts-1) : 1;
           /* Take into account the selectivity of the used pk prefix */
           if (used_pk_parts)
 	  {
@@ -22426,8 +22432,8 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
               rec_per_key= 1;                 
             if (rec_per_key > 1)
 	    {
-              rec_per_key*= pkinfo->rec_per_key[used_pk_parts-1];
-              rec_per_key/= pkinfo->rec_per_key[0];
+              rec_per_key*= pkinfo->real_rec_per_key(used_pk_parts-1);
+              rec_per_key/= pkinfo->real_rec_per_key(0);
               /* 
                 The value of rec_per_key for the extended key has
                 to be adjusted accordingly if some components of
@@ -22441,9 +22447,9 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
                     We presume here that for any index rec_per_key[i] != 0
                     if rec_per_key[0] != 0.
 	          */
-                  DBUG_ASSERT(pkinfo->rec_per_key[i]);
-                  rec_per_key*= pkinfo->rec_per_key[i-1];
-                  rec_per_key/= pkinfo->rec_per_key[i];
+                  DBUG_ASSERT(pkinfo->real_rec_per_key(i));
+                  rec_per_key*= pkinfo->real_rec_per_key(i-1);
+                  rec_per_key/= pkinfo->real_rec_per_key(i);
                 }
 	      }
             }    
@@ -22488,7 +22494,7 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table,
           select_limit= (ha_rows) (select_limit *
                                    (double) table_records /
                                     table->quick_condition_rows);
-        rec_per_key= keyinfo->rec_per_key[keyinfo->key_parts-1];
+        rec_per_key= keyinfo->real_rec_per_key(keyinfo->key_parts-1);
         set_if_bigger(rec_per_key, 1);
         /*
           Here we take into account the fact that rows are
@@ -22629,7 +22635,7 @@ uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select,
       Update quick_condition_rows since single table UPDATE/DELETE procedures
       don't call make_join_statistics() and leave this variable uninitialized.
     */
-    table->quick_condition_rows= table->file->stats.records;
+    table->quick_condition_rows= table->stat_records();
     
     int key, direction;
     if (test_if_cheaper_ordering(NULL, order, table,
diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc
index 89f3c23d5af..27aa77ae1a3 100644
--- a/sql/sql_statistics.cc
+++ b/sql/sql_statistics.cc
@@ -1531,7 +1531,7 @@ int update_statistics_for_table(THD *thd, TABLE *table)
   now.        
 */
 
-int read_statistics_for_table(THD *thd, struct TABLE *table)
+int read_statistics_for_table(THD *thd, TABLE *table)
 {
   uint i;
   TABLE *stat_table;
@@ -1579,6 +1579,29 @@ int read_statistics_for_table(THD *thd, struct TABLE *table)
       index_stat.set_key_fields(key_info, i+1);
       index_stat.get_stat_values();
     }
+   
+    key_part_map ext_key_part_map= key_info->ext_key_part_map;
+    if (key_info->key_parts != key_info->ext_key_parts)
+    {
+      KEY *pk_key_info= table->key_info + table->s->primary_key;
+      uint k= key_info->key_parts;
+      double k_avg_frequency= key_info->read_stat.avg_frequency[k-1];
+      uint pk_parts= pk_key_info->key_parts;
+      ha_rows n_rows= table->read_stat.cardinality;
+      for (uint j= 0; j < pk_parts; j++)
+      {
+        double avg_frequency;
+        if (!(ext_key_part_map & 1 << j))
+          continue;
+        avg_frequency= pk_key_info->read_stat.avg_frequency[j];
+        if (avg_frequency == 0 ||
+            table->read_stat.cardinality_is_null)
+          avg_frequency= 1;
+        else if (avg_frequency > 1)
+          avg_frequency= max(k_avg_frequency * avg_frequency / n_rows, 1);
+        key_info->read_stat.avg_frequency[k++]= avg_frequency;
+      }
+    }
   }
       
   close_system_tables(thd, &open_tables_backup);
@@ -1586,3 +1609,33 @@ int read_statistics_for_table(THD *thd, struct TABLE *table)
   DBUG_RETURN(0);
 }
 
+
+/**
+  @brief
+  Set statistics for a table that will be used by the optimizer 
+
+  @param
+  thd         The thread handle
+  @param
+  table       The table to set statistics for 
+
+  @details
+  Depending on the value of thd->variables.optimizer_use_stat_tables 
+  the function performs the settings for the table that will control
+  from where the statistical data used by the optimizer will be taken.
+*/
+
+void set_statistics_for_table(THD *thd, TABLE *table)
+{
+  uint use_stat_table_mode= thd->variables.optimizer_use_stat_tables;
+  table->used_stat_records= 
+    (use_stat_table_mode <= 1 || table->read_stat.cardinality_is_null) ?
+    table->file->stats.records : table->read_stat.cardinality;
+  KEY *key_info, *key_info_end;
+  for (key_info= table->key_info, key_info_end= key_info+table->s->keys;
+       key_info < key_info_end; key_info++)
+  {
+    key_info->is_statistics_from_stat_tables=
+      (use_stat_table_mode > 1 && key_info->read_stat.avg_frequency[0] > 0.5);
+  }
+}
diff --git a/sql/structs.h b/sql/structs.h
index 51c71db7cd2..9949a72964b 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -96,6 +96,11 @@ typedef struct st_key {
   uint  block_size;
   uint  name_length;
   enum  ha_key_alg algorithm;
+  /* 
+    The flag is on if statistical data for the index prefixes
+    has to be taken from the system statistical tables.
+  */
+  bool is_statistics_from_stat_tables;
   /*
     Note that parser is used when the table is opened for use, and
     parser_name is used when the table is being created.
@@ -148,6 +153,15 @@ typedef struct st_key {
   /** reference to the list of options or NULL */
   engine_option_value *option_list;
   ha_index_option_struct *option_struct;                  /* structure with parsed options */
+
+  inline double real_rec_per_key(uint i)
+  { 
+    if (rec_per_key == 0)
+      return 0;
+    return (is_statistics_from_stat_tables ?
+            (ulong) (100 * read_stat.avg_frequency[i]) / (double) 100 :
+            (double) rec_per_key[i]);
+  }
 } KEY;
 
 
diff --git a/sql/table.cc b/sql/table.cc
index eb8d43deefa..6c20f95c28b 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -762,8 +762,8 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head,
   ulong pos, record_offset; 
   ulong *rec_per_key= NULL;
   ulong rec_buff_length;
-  double *read_avg_frequency= 0;
-  double *write_avg_frequency= 0;
+  double *read_avg_frequency= NULL;
+  double *write_avg_frequency= NULL;
   handler *handler_file= 0;
   KEY	*keyinfo;
   KEY_PART_INFO *key_part= NULL;
@@ -1019,6 +1019,8 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head,
         {
           *key_part++= first_key_part[j];
           *rec_per_key++= 0;
+          *read_avg_frequency++= 0;
+          *write_avg_frequency++= 0;
           keyinfo->ext_key_parts++;
           keyinfo->ext_key_part_map|= 1 << j;
         }
@@ -2406,6 +2408,8 @@ int open_table_from_share(THD *thd, TABLE_SHARE *share, const char *alias,
   }
 #endif
 
+  outparam->read_stat.cardinality_is_null= TRUE;
+
   if (!(field_ptr = (Field **) alloc_root(&outparam->mem_root,
                                           (uint) ((share->fields+1)*
                                                   sizeof(Field*)))))
@@ -5924,6 +5928,7 @@ bool TABLE::add_tmp_key(uint key, uint key_parts,
   keyinfo->algorithm= HA_KEY_ALG_UNDEF;
   keyinfo->flags= HA_GENERATED_KEY;
   keyinfo->ext_key_flags= keyinfo->flags;
+  keyinfo->is_statistics_from_stat_tables= FALSE;
   if (unique)
     keyinfo->flags|= HA_NOSAME;
   sprintf(buf, "key%i", key);
@@ -6619,6 +6624,7 @@ int TABLE_LIST::fetch_number_of_rows()
   {
     table->file->stats.records= ((select_union*)derived->result)->records;
     set_if_bigger(table->file->stats.records, 2);
+    table->used_stat_records= table->file->stats.records;
   }
   else
     error= table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
diff --git a/sql/table.h b/sql/table.h
index e7f41576215..0deb793aa57 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1025,6 +1025,9 @@ public:
   */
   Table_statistics write_stat;
 
+  /* The estimate of the number of records in the table used by optimizer */ 
+  ha_rows used_stat_records;
+
   /* 
     For each key that has quick_keys.is_set(key) == TRUE: estimate of #records
     and max #key parts that range access would use.
@@ -1271,6 +1274,7 @@ public:
   bool update_const_key_parts(COND *conds);
   uint actual_n_key_parts(KEY *keyinfo);
   ulong actual_key_flags(KEY *keyinfo);
+  inline ha_rows stat_records() { return used_stat_records; }
 };