The main patch for the MWL#248 back-ported from

lp:~igorb-seattle/mysql-server/mysql-azalea-wl4777.

16 files changed, 866 insertions, 5 deletions

diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc
index 123c82484b9..3d97ff67043 100644
--- a/mysql-test/include/system_db_struct.inc
+++ b/mysql-test/include/system_db_struct.inc
@@ -16,3 +16,6 @@ show create table proc;
 show create table event;
 show create table general_log;
 show create table slow_log;
+show create table table_stat;
+show create table column_stat;
+show create table index_stat;
diff --git a/mysql-test/include/world_schema_utf8.inc b/mysql-test/include/world_schema_utf8.inc
new file mode 100644
index 00000000000..2a09c5d32d5
--- /dev/null
+++ b/mysql-test/include/world_schema_utf8.inc
@@ -0,0 +1,25 @@
+CREATE TABLE Country (
+  Code char(3) NOT NULL default '',
+  Name char(52) NOT NULL default '',
+  SurfaceArea float(10,2) NOT NULL default '0.00',
+  Population int(11) NOT NULL default '0',
+  Capital int(11) default NULL,
+  PRIMARY KEY  (Code),
+  UNIQUE INDEX (Name)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE City (
+  ID int(11) NOT NULL auto_increment,
+  Name char(35) NOT NULL default '',
+  Country char(3) NOT NULL default '',
+  Population int(11) NOT NULL default '0',
+  PRIMARY KEY  (ID),
+  INDEX (Population),
+  INDEX (Country) 
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE CountryLanguage (
+  Country char(3) NOT NULL default '',
+  Language char(30) NOT NULL default '',
+  Percentage float(3,1) NOT NULL default '0.0',
+  PRIMARY KEY  (Country, Language),
+  INDEX (Percentage)
+) CHARACTER SET utf8 COLLATE utf8_bin;
diff --git a/mysql-test/r/1st.result b/mysql-test/r/1st.result
index 4a82f8c66e9..7675c9ec35c 100644
--- a/mysql-test/r/1st.result
+++ b/mysql-test/r/1st.result
@@ -6,6 +6,7 @@ mysql
 test
 show tables in mysql;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -16,12 +17,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result
index 7a972f4814a..544e2d29846 100644
--- a/mysql-test/r/connect.result
+++ b/mysql-test/r/connect.result
@@ -1,6 +1,7 @@
 drop table if exists t1,t2;
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -11,12 +12,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -34,6 +37,7 @@ grant ALL on *.* to test@localhost identified by "gambling";
 grant ALL on *.* to test@127.0.0.1 identified by "gambling";
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -44,12 +48,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -75,6 +81,7 @@ ERROR HY000: Password hash should be a 41-digit hexadecimal number
 set password=old_password('gambling3');
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -85,12 +92,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 6694cf48109..1370fb86875 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -95,6 +95,7 @@ USER_STATISTICS
 VIEWS
 XTRADB_ADMIN_COMMAND
 XTRADB_ENHANCEMENTS
+column_stat
 columns_priv
 db
 event
@@ -105,6 +106,7 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 plugin
 proc
 procs_priv
@@ -115,6 +117,7 @@ t2
 t3
 t4
 t5
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -132,6 +135,7 @@ TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
 TABLE_PRIVILEGES	TABLE_PRIVILEGES
 TABLE_STATISTICS	TABLE_STATISTICS
 TRIGGERS	TRIGGERS
+table_stat	table_stat
 tables_priv	tables_priv
 time_zone	time_zone
 time_zone_leap_second	time_zone_leap_second
@@ -152,6 +156,7 @@ TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
 TABLE_PRIVILEGES	TABLE_PRIVILEGES
 TABLE_STATISTICS	TABLE_STATISTICS
 TRIGGERS	TRIGGERS
+table_stat	table_stat
 tables_priv	tables_priv
 time_zone	time_zone
 time_zone_leap_second	time_zone_leap_second
@@ -172,6 +177,7 @@ TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
 TABLE_PRIVILEGES	TABLE_PRIVILEGES
 TABLE_STATISTICS	TABLE_STATISTICS
 TRIGGERS	TRIGGERS
+table_stat	table_stat
 tables_priv	tables_priv
 time_zone	time_zone
 time_zone_leap_second	time_zone_leap_second
diff --git a/mysql-test/r/information_schema_all_engines.result b/mysql-test/r/information_schema_all_engines.result
index 7a2c548a329..ad172710052 100644
--- a/mysql-test/r/information_schema_all_engines.result
+++ b/mysql-test/r/information_schema_all_engines.result
@@ -382,4 +382,4 @@ Wildcard: inf_rmation_schema
 SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
 table_schema	count(*)
 information_schema	52
-mysql	22
+mysql	25
diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result
index a9d1b41cf2c..53d6b6ce4eb 100644
--- a/mysql-test/r/log_tables_upgrade.result
+++ b/mysql-test/r/log_tables_upgrade.result
@@ -19,6 +19,7 @@ mtr
 mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
 mysql
+mysql.column_stat                                  OK
 mysql.columns_priv                                 OK
 mysql.db                                           OK
 mysql.event                                        OK
@@ -28,12 +29,14 @@ mysql.help_keyword                                 OK
 mysql.help_relation                                OK
 mysql.help_topic                                   OK
 mysql.host                                         OK
+mysql.index_stat                                   OK
 mysql.ndb_binlog_index                             OK
 mysql.plugin                                       OK
 mysql.proc                                         OK
 mysql.procs_priv                                   OK
 mysql.renamed_general_log                          OK
 mysql.servers                                      OK
+mysql.table_stat                                   OK
 mysql.tables_priv                                  OK
 mysql.time_zone                                    OK
 mysql.time_zone_leap_second                        OK
diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result
index 8aa121f9854..45904b0f088 100644
--- a/mysql-test/r/mysqlcheck.result
+++ b/mysql-test/r/mysqlcheck.result
@@ -3,6 +3,7 @@ drop view if exists v1;
 drop database if exists client_test_db;
 mtr.global_suppressions                            OK
 mtr.test_suppressions                              OK
+mysql.column_stat                                  OK
 mysql.columns_priv                                 OK
 mysql.db                                           OK
 mysql.event                                        OK
@@ -12,11 +13,13 @@ mysql.help_keyword                                 OK
 mysql.help_relation                                OK
 mysql.help_topic                                   OK
 mysql.host                                         OK
+mysql.index_stat                                   OK
 mysql.ndb_binlog_index                             OK
 mysql.plugin                                       OK
 mysql.proc                                         OK
 mysql.procs_priv                                   OK
 mysql.servers                                      OK
+mysql.table_stat                                   OK
 mysql.tables_priv                                  OK
 mysql.time_zone                                    OK
 mysql.time_zone_leap_second                        OK
@@ -24,6 +27,7 @@ mysql.time_zone_name                               OK
 mysql.time_zone_transition                         OK
 mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
+mysql.column_stat                                  OK
 mysql.columns_priv                                 OK
 mysql.db                                           OK
 mysql.event                                        OK
@@ -33,11 +37,13 @@ mysql.help_keyword                                 OK
 mysql.help_relation                                OK
 mysql.help_topic                                   OK
 mysql.host                                         OK
+mysql.index_stat                                   OK
 mysql.ndb_binlog_index                             OK
 mysql.plugin                                       OK
 mysql.proc                                         OK
 mysql.procs_priv                                   OK
 mysql.servers                                      OK
+mysql.table_stat                                   OK
 mysql.tables_priv                                  OK
 mysql.time_zone                                    OK
 mysql.time_zone_leap_second                        OK
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result
new file mode 100644
index 00000000000..424dec3bb9d
--- /dev/null
+++ b/mysql-test/r/statistics.result
@@ -0,0 +1,455 @@
+drop table if exists t1,t2;
+CREATE VIEW table_stat AS
+SELECT * FROM mysql.table_stat;
+CREATE VIEW column_stat AS
+SELECT db_name, table_name, column_name,
+min_value, max_value,
+CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio',
+CAST(avg_length AS decimal(12,4)) AS 'avg_length',
+CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
+    FROM mysql.column_stat;
+CREATE VIEW index_stat AS
+SELECT db_name, table_name, index_name, prefix_arity,
+CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
+    FROM mysql.index_stat;
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+set optimizer_use_stat_tables='preferably';
+CREATE TABLE t1 (
+a int NOT NULL PRIMARY KEY,
+b varchar(32),
+c char(16),
+d date,
+e double,
+f bit(3),
+INDEX idx1 (b, e), 
+INDEX idx2(c, d),
+INDEX idx3 (d),
+INDEX idx4 (e, b, d)
+);
+INSERT INTO t1 VALUES
+(0, NULL, NULL, NULL, NULL, NULL),
+(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'),
+(17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'),
+(1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'),
+(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'),
+(23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'),
+(8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'),
+(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'),
+(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'),
+(10, NULL, 'aaaa', NULL, 0.01, b'010'),
+(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'),
+(15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'),
+(30, NULL, 'bbbbbb', NULL, NULL, b'100'),
+(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL),
+(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'),
+(9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL),
+(29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'),
+(3, 'yyy', 'dddddddd',  '1990-05-15', 0.112, b'010'),
+(39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'),
+(14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'),
+(40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL),
+(44, NULL, 'aaaa', '1989-03-12', NULL, b'010'),
+(19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'),
+(21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'),
+(45, NULL, NULL, '1989-03-12', NULL, b'011'),
+(2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'),
+(35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'),
+(4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'),
+(47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'),
+(42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'),
+(32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'),
+(49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL),
+(43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'),
+(37, 'yyy', NULL, '1989-03-12', 0.05, b'011'),
+(41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL),
+(34, 'yyy', NULL, NULL, NULL, NULL),
+(33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'),
+(24,  'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01,  b'101'),
+(11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL),
+(25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01,  b'101');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM table_stat;
+db_name	table_name	cardinality
+test	t1	40
+SELECT * FROM column_stat;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	a	0	49	0.0000	4.0000	1.0000
+test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
+test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
+test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
+test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
+test	t1	f	1	5	0.2000	1.0000	6.4000
+SELECT * FROM index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	idx1	1	6.4000
+test	t1	idx1	2	1.6875
+test	t1	idx2	1	7.0000
+test	t1	idx2	2	2.3846
+test	t1	idx3	1	8.5000
+test	t1	idx4	1	6.2000
+test	t1	idx4	2	1.6875
+test	t1	idx4	3	1.1304
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40
+SELECT * FROM column_stat
+WHERE db_name='test' AND table_name='t1' AND column_name='a';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	a	0	49	0.0000	4.0000	1.0000
+SELECT MIN(t1.a), MAX(t1.a), 
+(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)",
+(SELECT COUNT(t1.a) FROM t1) /
+(SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)"
+FROM t1;
+MIN(t1.a)	MAX(t1.a)	NULLS_RATIO(t1.a)	AVG_FREQUENCY(t1.a)
+0	49	0.2000	1.0000
+SELECT * FROM column_stat
+WHERE db_name='test' AND table_name='t1' AND column_name='b';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
+SELECT MIN(t1.b), MAX(t1.b), 
+(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)",
+(SELECT COUNT(t1.b) FROM t1) /
+(SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)"
+FROM t1;
+MIN(t1.b)	MAX(t1.b)	NULLS_RATIO(t1.b)	AVG_FREQUENCY(t1.b)
+vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	6.4000
+SELECT * FROM column_stat 
+WHERE db_name='test' AND table_name='t1' AND column_name='c';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
+SELECT MIN(t1.c), MAX(t1.c), 
+(SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) /
+(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)",
+(SELECT COUNT(t1.c) FROM t1) /
+(SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)"
+FROM t1;
+MIN(t1.c)	MAX(t1.c)	NULLS_RATIO(t1.c)	AVG_FREQUENCY(t1.c)
+aaaa	dddddddd	0.1250	7.0000
+SELECT * FROM column_stat
+WHERE db_name='test' AND table_name='t1' AND column_name='d';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
+SELECT MIN(t1.d), MAX(t1.d), 
+(SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) /
+(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)",
+(SELECT COUNT(t1.d) FROM t1) /
+(SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)"
+FROM t1;
+MIN(t1.d)	MAX(t1.d)	NULLS_RATIO(t1.d)	AVG_FREQUENCY(t1.d)
+1989-03-12	1999-07-23	0.1500	8.5000
+SELECT * FROM column_stat
+WHERE db_name='test' AND table_name='t1' AND column_name='e';
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
+SELECT MIN(t1.e), MAX(t1.e), 
+(SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) /
+(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)",
+(SELECT COUNT(t1.e) FROM t1) /
+(SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)"
+FROM t1;
+MIN(t1.e)	MAX(t1.e)	NULLS_RATIO(t1.e)	AVG_FREQUENCY(t1.e)
+0.01	0.112	0.2250	6.2000
+SELECT * FROM index_stat
+WHERE db_name='test' AND table_name='t1' AND index_name='idx1';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	idx1	1	6.4000
+test	t1	idx1	2	1.6875
+SELECT 
+(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL)
+AS 'ARITY 1',
+(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1
+WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) 
+AS 'ARITY 2';
+ARITY 1	ARITY 2
+6.4000	1.6875
+SELECT * FROM index_stat
+WHERE db_name='test' AND table_name='t1' AND index_name='idx2';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	idx2	1	7.0000
+test	t1	idx2	2	2.3846
+SELECT 
+(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) 
+AS 'ARITY 1',
+(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1
+WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL)
+AS 'ARITY 2';
+ARITY 1	ARITY 2
+7.0000	2.3846
+SELECT * FROM index_stat
+WHERE db_name='test' AND table_name='t1' AND index_name='idx3';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	idx3	1	8.5000
+SELECT 
+(SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL)
+AS 'ARITY 1';
+ARITY 1
+8.5000
+SELECT * FROM index_stat
+WHERE db_name='test' AND table_name='t1' AND index_name='idx4';
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	idx4	1	6.2000
+test	t1	idx4	2	1.6875
+test	t1	idx4	3	1.1304
+SELECT 
+(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL)
+AS 'ARITY 1',
+(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) /
+(SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1
+WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL)
+AS 'ARITY 2',
+(SELECT COUNT(*) FROM t1
+WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) /  
+(SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1
+WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL)
+AS 'ARITY 3';
+ARITY 1	ARITY 2	ARITY 3
+6.2000	1.6875	1.1304
+CREATE TABLE t2 LIKE t1;
+ALTER TABLE t2 ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+SELECT * FROM table_stat;
+db_name	table_name	cardinality
+test	t1	40
+test	t2	40
+SELECT * FROM column_stat ORDER BY column_name;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	a	0	49	0.0000	4.0000	1.0000
+test	t2	a	0	49	0.0000	4.0000	1.0000
+test	t1	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
+test	t2	b	vvvvvvvvvvvvv	zzzzzzzzzzzzzzzzzz	0.2000	17.1250	6.4000
+test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
+test	t2	c	aaaa	dddddddd	0.1250	6.6571	7.0000
+test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
+test	t2	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
+test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
+test	t2	e	0.01	0.112	0.2250	8.0000	6.2000
+test	t1	f	1	5	0.2000	1.0000	6.4000
+test	t2	f	1	5	0.2000	1.0000	6.4000
+SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t2	PRIMARY	1	1.0000
+test	t1	idx1	1	6.4000
+test	t2	idx1	1	6.4000
+test	t1	idx1	2	1.6875
+test	t2	idx1	2	1.6875
+test	t1	idx2	1	7.0000
+test	t2	idx2	1	7.0000
+test	t1	idx2	2	2.3846
+test	t2	idx2	2	2.3846
+test	t1	idx3	1	8.5000
+test	t2	idx3	1	8.5000
+test	t1	idx4	1	6.2000
+test	t2	idx4	1	6.2000
+test	t1	idx4	2	1.6875
+test	t2	idx4	2	1.6875
+test	t1	idx4	3	1.1304
+test	t2	idx4	3	1.1304
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+ALTER TABLE t1
+DROP INDEX idx1,
+DROP INDEX idx4;
+ALTER TABLE t1
+MODIFY COLUMN b text,
+ADD INDEX idx1 (b(4), e), 
+ADD INDEX idx4 (e, b(4), d);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+SELECT * FROM column_stat;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+test	t1	a	0	49	0.0000	4.0000	1.0000
+test	t1	b	NULL	NULL	0.2000	17.1250	NULL
+test	t1	c	aaaa	dddddddd	0.1250	6.6571	7.0000
+test	t1	d	1989-03-12	1999-07-23	0.1500	3.0000	8.5000
+test	t1	e	0.01	0.112	0.2250	8.0000	6.2000
+test	t1	f	1	5	0.2000	1.0000	6.4000
+SELECT * FROM index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+test	t1	PRIMARY	1	1.0000
+test	t1	idx2	1	7.0000
+test	t1	idx2	2	2.3846
+test	t1	idx3	1	8.5000
+test	t1	idx1	1	NULL
+test	t1	idx1	2	NULL
+test	t1	idx4	1	6.2000
+test	t1	idx4	2	NULL
+test	t1	idx4	3	NULL
+DROP TABLE t1,t2;
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+set optimizer_use_stat_tables='never';
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+set optimizer_use_stat_tables='preferably';
+ANALYZE TABLE Country, City, CountryLanguage;
+SELECT * FROM test.table_stat;
+db_name	table_name	cardinality
+world	Country	239
+world	City	4079
+world	CountryLanguage	984
+SELECT * FROM test.column_stat;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+world	Country	Code	ABW	ZWE	0.0000	3.0000	1.0000
+world	Country	Name	Afghanistan	Zimbabwe	0.0000	10.1088	1.0000
+world	Country	SurfaceArea	0.40	17075400.00	0.0000	4.0000	1.0042
+world	Country	Population	0	1277558000	0.0000	4.0000	1.0575
+world	Country	Capital	1	4074	0.0293	4.0000	1.0000
+world	City	ID	1	4079	0.0000	4.0000	1.0000
+world	City	Name	A Coruña (La Coruña)	Ürgenc	0.0000	8.6416	1.0195
+world	City	Country	ABW	ZWE	0.0000	3.0000	17.5819
+world	City	Population	42	10500000	0.0000	4.0000	1.0467
+world	CountryLanguage	Country	ABW	ZWE	0.0000	3.0000	4.2232
+world	CountryLanguage	Language	Abhyasi	[South]Mande	0.0000	7.1778	2.1532
+world	CountryLanguage	Percentage	0.0	99.9	0.0000	4.0000	2.7640
+SELECT * FROM test.index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+world	Country	PRIMARY	1	1.0000
+world	Country	Name	1	1.0000
+world	City	PRIMARY	1	1.0000
+world	City	Population	1	1.0467
+world	City	Country	1	17.5819
+world	CountryLanguage	PRIMARY	1	4.2232
+world	CountryLanguage	PRIMARY	2	1.0000
+world	CountryLanguage	Percentage	1	2.7640
+use test;
+set optimizer_use_stat_tables='never';
+CREATE DATABASE world_innodb;
+use world_innodb;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+) CHARACTER SET utf8 COLLATE utf8_bin;
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+) CHARACTER SET utf8 COLLATE utf8_bin;
+ALTER TABLE Country ENGINE=InnoDB;
+ALTER TABLE City ENGINE=InnoDB;
+ALTER TABLE CountryLanguage ENGINE=InnoDB;
+set optimizer_use_stat_tables='preferably';
+ANALYZE TABLE Country, City, CountryLanguage;
+SELECT * FROM test.table_stat;
+db_name	table_name	cardinality
+world	Country	239
+world	City	4079
+world	CountryLanguage	984
+world_innodb	Country	239
+world_innodb	City	4079
+world_innodb	CountryLanguage	984
+SELECT * FROM test.column_stat;
+db_name	table_name	column_name	min_value	max_value	nulls_ratio	avg_length	avg_frequency
+world	Country	Code	ABW	ZWE	0.0000	3.0000	1.0000
+world	Country	Name	Afghanistan	Zimbabwe	0.0000	10.1088	1.0000
+world	Country	SurfaceArea	0.40	17075400.00	0.0000	4.0000	1.0042
+world	Country	Population	0	1277558000	0.0000	4.0000	1.0575
+world	Country	Capital	1	4074	0.0293	4.0000	1.0000
+world	City	ID	1	4079	0.0000	4.0000	1.0000
+world	City	Name	A Coruña (La Coruña)	Ürgenc	0.0000	8.6416	1.0195
+world	City	Country	ABW	ZWE	0.0000	3.0000	17.5819
+world	City	Population	42	10500000	0.0000	4.0000	1.0467
+world	CountryLanguage	Country	ABW	ZWE	0.0000	3.0000	4.2232
+world	CountryLanguage	Language	Abhyasi	[South]Mande	0.0000	7.1778	2.1532
+world	CountryLanguage	Percentage	0.0	99.9	0.0000	4.0000	2.7640
+world_innodb	Country	Code	ABW	ZWE	0.0000	3.0000	1.0000
+world_innodb	Country	Name	Afghanistan	Zimbabwe	0.0000	10.1088	1.0000
+world_innodb	Country	SurfaceArea	0.40	17075400.00	0.0000	4.0000	1.0042
+world_innodb	Country	Population	0	1277558000	0.0000	4.0000	1.0575
+world_innodb	Country	Capital	1	4074	0.0293	4.0000	1.0000
+world_innodb	City	ID	1	4079	0.0000	4.0000	1.0000
+world_innodb	City	Name	A Coruña (La Coruña)	Ürgenc	0.0000	8.6416	1.0195
+world_innodb	City	Country	ABW	ZWE	0.0000	3.0000	17.5819
+world_innodb	City	Population	42	10500000	0.0000	4.0000	1.0467
+world_innodb	CountryLanguage	Country	ABW	ZWE	0.0000	3.0000	4.2232
+world_innodb	CountryLanguage	Language	Abhyasi	[South]Mande	0.0000	7.1778	2.1532
+world_innodb	CountryLanguage	Percentage	0.0	99.9	0.0000	4.0000	2.7640
+SELECT * FROM test.index_stat;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+world	Country	PRIMARY	1	1.0000
+world	Country	Name	1	1.0000
+world	City	PRIMARY	1	1.0000
+world	City	Population	1	1.0467
+world	City	Country	1	17.5819
+world	CountryLanguage	PRIMARY	1	4.2232
+world	CountryLanguage	PRIMARY	2	1.0000
+world	CountryLanguage	Percentage	1	2.7640
+world_innodb	Country	PRIMARY	1	1.0000
+world_innodb	Country	Name	1	1.0000
+world_innodb	City	PRIMARY	1	1.0000
+world_innodb	City	Population	1	1.0467
+world_innodb	City	Country	1	17.5819
+world_innodb	CountryLanguage	PRIMARY	1	4.2232
+world_innodb	CountryLanguage	PRIMARY	2	1.0000
+world_innodb	CountryLanguage	Percentage	1	2.7640
+use test;
+DROP DATABASE world;
+DROP DATABASE world_innodb;
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+DROP VIEW test.table_stat;
+DROP VIEW test.column_stat;
+DROP VIEW test.index_stat;
diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result
index 5079d72aaea..312383c8ee6 100644
--- a/mysql-test/r/system_mysql_db.result
+++ b/mysql-test/r/system_mysql_db.result
@@ -1,5 +1,6 @@
 show tables;
 Tables_in_db
+column_stat
 columns_priv
 db
 event
@@ -10,12 +11,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -262,5 +265,36 @@ slow_log	CREATE TABLE `slow_log` (
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL
 ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
+show create table table_stat;
+Table	Create Table
+table_stat	CREATE TABLE `table_stat` (
+  `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `cardinality` bigint(21) unsigned DEFAULT NULL,
+  PRIMARY KEY (`db_name`,`table_name`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables'
+show create table column_stat;
+Table	Create Table
+column_stat	CREATE TABLE `column_stat` (
+  `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `column_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
+  `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
+  `nulls_ratio` double DEFAULT NULL,
+  `avg_length` double DEFAULT NULL,
+  `avg_frequency` double DEFAULT NULL,
+  PRIMARY KEY (`db_name`,`table_name`,`column_name`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns'
+show create table index_stat;
+Table	Create Table
+index_stat	CREATE TABLE `index_stat` (
+  `db_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
+  `prefix_arity` int(11) unsigned NOT NULL,
+  `avg_frequency` double DEFAULT NULL,
+  PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes'
 show tables;
 Tables_in_test
diff --git a/mysql-test/suite/pbxt/r/connect.result b/mysql-test/suite/pbxt/r/connect.result
index c8d048f4d38..f4b042a4e84 100644
--- a/mysql-test/suite/pbxt/r/connect.result
+++ b/mysql-test/suite/pbxt/r/connect.result
@@ -1,6 +1,7 @@
 drop table if exists t1,t2;
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -11,12 +12,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -34,6 +37,7 @@ grant ALL on *.* to test@localhost identified by "gambling";
 grant ALL on *.* to test@127.0.0.1 identified by "gambling";
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -44,12 +48,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
@@ -75,6 +81,7 @@ ERROR HY000: Password hash should be a 41-digit hexadecimal number
 set password=old_password('gambling3');
 show tables;
 Tables_in_mysql
+column_stat
 columns_priv
 db
 event
@@ -85,12 +92,14 @@ help_keyword
 help_relation
 help_topic
 host
+index_stat
 ndb_binlog_index
 plugin
 proc
 procs_priv
 servers
 slow_log
+table_stat
 tables_priv
 time_zone
 time_zone_leap_second
diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test
new file mode 100644
index 00000000000..9ea57aca14a
--- /dev/null
+++ b/mysql-test/t/statistics.test
@@ -0,0 +1,290 @@
+--source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+CREATE VIEW table_stat AS
+SELECT * FROM mysql.table_stat;
+ 
+CREATE VIEW column_stat AS
+  SELECT db_name, table_name, column_name,
+         min_value, max_value,
+         CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio',
+         CAST(avg_length AS decimal(12,4)) AS 'avg_length',
+         CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
+    FROM mysql.column_stat;
+
+CREATE VIEW index_stat AS
+  SELECT db_name, table_name, index_name, prefix_arity,
+         CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
+    FROM mysql.index_stat;
+
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;  
+   
+set optimizer_use_stat_tables='preferably';
+
+CREATE TABLE t1 (
+  a int NOT NULL PRIMARY KEY,
+  b varchar(32),
+  c char(16),
+  d date,
+  e double,
+  f bit(3),
+  INDEX idx1 (b, e), 
+  INDEX idx2(c, d),
+  INDEX idx3 (d),
+  INDEX idx4 (e, b, d)
+);
+
+INSERT INTO t1 VALUES
+  (0, NULL, NULL, NULL, NULL, NULL),
+  (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'),
+  (17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'),
+  (1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'),
+  (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'),
+  (23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'),
+  (8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'),
+  (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'),
+  (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'),
+  (10, NULL, 'aaaa', NULL, 0.01, b'010'),
+  (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'),
+  (15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'),
+  (30, NULL, 'bbbbbb', NULL, NULL, b'100'),
+  (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL),
+  (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'),
+  (9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL),
+  (29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'),
+  (3, 'yyy', 'dddddddd',  '1990-05-15', 0.112, b'010'),
+  (39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'),
+  (14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'),
+  (40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL),
+  (44, NULL, 'aaaa', '1989-03-12', NULL, b'010'),
+  (19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'),
+  (21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'),
+  (45, NULL, NULL, '1989-03-12', NULL, b'011'),
+  (2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'),
+  (35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'),
+  (4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'),
+  (47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'),
+  (42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'),
+  (32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'),
+  (49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL),
+  (43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'),
+  (37, 'yyy', NULL, '1989-03-12', 0.05, b'011'),
+  (41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL),
+  (34, 'yyy', NULL, NULL, NULL, NULL),
+  (33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'),
+  (24,  'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01,  b'101'),
+  (11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL),
+  (25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01,  b'101');
+
+ANALYZE TABLE t1;
+
+SELECT * FROM table_stat;
+SELECT * FROM column_stat;
+SELECT * FROM index_stat;
+
+SELECT COUNT(*) FROM t1;
+
+SELECT * FROM column_stat
+  WHERE db_name='test' AND table_name='t1' AND column_name='a';
+SELECT MIN(t1.a), MAX(t1.a), 
+       (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)",
+       (SELECT COUNT(t1.a) FROM t1) /
+       (SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)"
+FROM t1;
+
+SELECT * FROM column_stat
+  WHERE db_name='test' AND table_name='t1' AND column_name='b';
+SELECT MIN(t1.b), MAX(t1.b), 
+       (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
+       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)",
+       (SELECT COUNT(t1.b) FROM t1) /
+       (SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)"
+FROM t1;
+
+SELECT * FROM column_stat 
+  WHERE db_name='test' AND table_name='t1' AND column_name='c';
+SELECT MIN(t1.c), MAX(t1.c), 
+       (SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) /
+       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)",
+       (SELECT COUNT(t1.c) FROM t1) /
+       (SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)"
+FROM t1;
+
+SELECT * FROM column_stat
+  WHERE db_name='test' AND table_name='t1' AND column_name='d';
+SELECT MIN(t1.d), MAX(t1.d), 
+       (SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) /
+       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)",
+       (SELECT COUNT(t1.d) FROM t1) /
+       (SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)"
+FROM t1;
+
+SELECT * FROM column_stat
+  WHERE db_name='test' AND table_name='t1' AND column_name='e';
+SELECT MIN(t1.e), MAX(t1.e), 
+       (SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) /
+       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)",
+       (SELECT COUNT(t1.e) FROM t1) /
+       (SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)"
+FROM t1;
+
+SELECT * FROM index_stat
+  WHERE db_name='test' AND table_name='t1' AND index_name='idx1';
+SELECT 
+  (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL)
+  AS 'ARITY 1',
+  (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1
+     WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) 
+  AS 'ARITY 2';
+
+SELECT * FROM index_stat
+  WHERE db_name='test' AND table_name='t1' AND index_name='idx2';
+SELECT 
+  (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) 
+  AS 'ARITY 1',
+  (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1
+     WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL)
+  AS 'ARITY 2';
+
+SELECT * FROM index_stat
+  WHERE db_name='test' AND table_name='t1' AND index_name='idx3';
+SELECT 
+  (SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL)
+  AS 'ARITY 1';
+
+SELECT * FROM index_stat
+  WHERE db_name='test' AND table_name='t1' AND index_name='idx4';
+SELECT 
+  (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL)
+  AS 'ARITY 1',
+  (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) /
+  (SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1
+         WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL)
+  AS 'ARITY 2',
+  (SELECT COUNT(*) FROM t1
+     WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) /  
+  (SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1
+     WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL)
+  AS 'ARITY 3';
+
+
+CREATE TABLE t2 LIKE t1;
+ALTER TABLE t2 ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+
+ANALYZE TABLE t2;
+
+SELECT * FROM table_stat;
+SELECT * FROM column_stat ORDER BY column_name;
+SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name;
+
+
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+
+ALTER TABLE t1
+  DROP INDEX idx1,
+  DROP INDEX idx4;
+ALTER TABLE t1
+  MODIFY COLUMN b text,
+  ADD INDEX idx1 (b(4), e), 
+  ADD INDEX idx4 (e, b(4), d);
+  
+ANALYZE TABLE t1;
+
+SELECT * FROM column_stat;
+SELECT * FROM index_stat;
+
+DROP TABLE t1,t2;
+
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+
+set optimizer_use_stat_tables='never';  
+
+set names utf8;
+
+CREATE DATABASE world;
+
+use world;
+
+--source include/world_schema_utf8.inc
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set optimizer_use_stat_tables='preferably';
+
+--disable_result_log
+ANALYZE TABLE Country, City, CountryLanguage;
+--enable_result_log
+
+SELECT * FROM test.table_stat;
+SELECT * FROM test.column_stat;
+SELECT * FROM test.index_stat;
+
+use test;
+
+set optimizer_use_stat_tables='never'; 
+
+CREATE DATABASE world_innodb;
+
+use world_innodb;
+
+--source include/world_schema_utf8.inc
+
+ALTER TABLE Country ENGINE=InnoDB;
+ALTER TABLE City ENGINE=InnoDB;
+ALTER TABLE CountryLanguage ENGINE=InnoDB;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/world.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+set optimizer_use_stat_tables='preferably';
+
+--disable_result_log
+ANALYZE TABLE Country, City, CountryLanguage;
+--enable_result_log
+
+SELECT * FROM test.table_stat;
+SELECT * FROM test.column_stat;
+SELECT * FROM test.index_stat;
+
+use test;
+
+DROP DATABASE world;
+DROP DATABASE world_innodb;
+
+DELETE FROM mysql.table_stat;
+DELETE FROM mysql.column_stat;
+DELETE FROM mysql.index_stat;
+  
+DROP VIEW test.table_stat;
+DROP VIEW test.column_stat;
+DROP VIEW test.index_stat;
+
+   
+    
\ No newline at end of file
diff --git a/mysql-test/t/system_mysql_db_fix30020.test b/mysql-test/t/system_mysql_db_fix30020.test
index 0a7d9dad7c9..e5f743f25fe 100644
--- a/mysql-test/t/system_mysql_db_fix30020.test
+++ b/mysql-test/t/system_mysql_db_fix30020.test
@@ -97,7 +97,8 @@ INSERT INTO user VALUES ('localhost','',    '','N','N','N','N','N','N','N','N','
 DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv,
 procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc,
 time_zone, time_zone_leap_second, time_zone_name, time_zone_transition,
-time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index;
+time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index,
+table_stat, column_stat, index_stat;
 
 -- enable_query_log
 
diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test
index 08d40bdae0f..4a91872a27a 100644
--- a/mysql-test/t/system_mysql_db_fix40123.test
+++ b/mysql-test/t/system_mysql_db_fix40123.test
@@ -59,6 +59,11 @@ CREATE TABLE time_zone_transition_type (   Time_zone_id int unsigned NOT NULL,
 
 CREATE TABLE time_zone_leap_second (   Transition_time bigint signed NOT NULL,   Correction int signed NOT NULL,   PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8   comment='Leap seconds information for time zones';
 
+CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables';
+
+CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns';
+
+CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes';
 
 # Run the mysql_fix_privilege_tables.sql using "mysql --force"
 --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix40123.log 2>&1
@@ -72,7 +77,7 @@ CREATE TABLE time_zone_leap_second (   Transition_time bigint signed NOT NULL,
 -- disable_query_log
 
 # Drop all tables created by this test
-DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index;
+DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, table_stat, column_stat, index_stat;
 
 -- enable_query_log
 
diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test
index 41e85661fe8..cabef0f6b8a 100644
--- a/mysql-test/t/system_mysql_db_fix50030.test
+++ b/mysql-test/t/system_mysql_db_fix50030.test
@@ -66,6 +66,12 @@ CREATE TABLE servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) N
 
 INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','root');
 
+CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables';
+
+CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns';
+
+CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes';
+
 # Run the mysql_fix_privilege_tables.sql using "mysql --force"
 --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix50030.log 2>&1
 
@@ -78,7 +84,7 @@ INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','r
 -- disable_query_log
 
 # Drop all tables created by this test
-DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index;
+DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, table_stat, column_stat, index_stat;
 
 -- enable_query_log
 
diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test
index bed00239081..c2db3f191b0 100644
--- a/mysql-test/t/system_mysql_db_fix50117.test
+++ b/mysql-test/t/system_mysql_db_fix50117.test
@@ -85,6 +85,12 @@ CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_b
 
 CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM;
 
+CREATE TABLE table_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables';
+
+CREATE TABLE column_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns';
+
+CREATE TABLE index_stat (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes';
+
 # Run the mysql_fix_privilege_tables.sql using "mysql --force"
 --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/log/system_mysql_db_fix50117.log 2>&1
 
@@ -97,7 +103,7 @@ CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL,
 -- disable_query_log
 
 # Drop all tables created by this test
-DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index;
+DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, table_stat, column_stat, index_stat;
 
 -- enable_query_log