diff options
Diffstat (limited to 'mysql-test/r/statistics.result')
| -rw-r--r-- | mysql-test/r/statistics.result | 570 |
1 files changed, 347 insertions, 223 deletions
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index ba0390f98db..20469c01a2e 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -64,13 +64,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -87,8 +87,8 @@ COUNT(*) 40 SELECT * FROM mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 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)", @@ -99,8 +99,8 @@ MIN(t1.a) MAX(t1.a) NULLS_RATIO(t1.a) AVG_FREQUENCY(t1.a) 0 49 0.2000 1.0000 SELECT * FROM mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 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)", @@ -111,8 +111,8 @@ MIN(t1.b) MAX(t1.b) NULLS_RATIO(t1.b) AVG_FREQUENCY(t1.b) vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 SELECT * FROM mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 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)", @@ -123,8 +123,8 @@ MIN(t1.c) MAX(t1.c) NULLS_RATIO(t1.c) AVG_FREQUENCY(t1.c) aaaa dddddddd 0.1250 7.0000 SELECT * FROM mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 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)", @@ -135,8 +135,8 @@ 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 mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 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)", @@ -206,6 +206,47 @@ 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 +DELETE FROM mysql.column_stats; +set histogram_size=4; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +SELECT db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a 0 49 0.0000 1.0000 4 SINGLE_PREC_HB 2E62A1D0 +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 SINGLE_PREC_HB 003FBFFF +test t1 c aaaa dddddddd 0.1250 7.0000 4 SINGLE_PREC_HB 0055AAFF +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 SINGLE_PREC_HB 009393FF +test t1 e 0.01 0.112 0.2250 6.2000 4 SINGLE_PREC_HB 000564E1 +test t1 f 1 5 0.2000 6.4000 4 SINGLE_PREC_HB 3F7FBFBF +DELETE FROM mysql.column_stats; +set histogram_size=8; +set histogram_type='DOUBLE_PREC_HB'; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +SELECT db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a 0 49 0.0000 1.0000 8 DOUBLE_PREC_HB 052F4363F4A1F9D0 +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 DOUBLE_PREC_HB 0000FF3FFFBFFFFF +test t1 c aaaa dddddddd 0.1250 7.0000 8 DOUBLE_PREC_HB 00005555AAAAFFFF +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 DOUBLE_PREC_HB 000026942694FFFF +test t1 e 0.01 0.112 0.2250 6.2000 8 DOUBLE_PREC_HB 000005056464E1E1 +test t1 f 1 5 0.2000 6.4000 8 DOUBLE_PREC_HB FF3FFF7FFFBFFFBF +DELETE FROM mysql.column_stats; +set histogram_size= 0; +set histogram_type=default; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date CREATE TABLE t3 ( a int NOT NULL PRIMARY KEY, b varchar(32), @@ -238,16 +279,16 @@ db_name table_name cardinality test t1 40 test t3 17 SELECT * FROM mysql.column_stats; -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 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -267,16 +308,16 @@ db_name table_name cardinality test s1 40 test t3 17 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test s1 a 0 49 0.0000 4.0000 1.0000 -test s1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test s1 e 0.01 0.112 0.2250 8.0000 6.2000 -test s1 f 1 5 0.2000 1.0000 6.4000 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test s1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test s1 PRIMARY 1 1.0000 @@ -296,16 +337,16 @@ db_name table_name cardinality test t1 40 test t3 17 SELECT * FROM mysql.column_stats; -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 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -324,13 +365,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -365,13 +406,13 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`y`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 x 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 y 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 y 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL ALTER TABLE t1 CHANGE COLUMN x b varchar(32), CHANGE COLUMN y e double; SHOW CREATE TABLE t1; @@ -390,13 +431,13 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL ALTER TABLE t1 RENAME TO s1, CHANGE COLUMN b x varchar(32); SHOW CREATE TABLE s1; Table Create Table @@ -417,13 +458,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test s1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test s1 a 0 49 0.0000 4.0000 1.0000 -test s1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test s1 e 0.01 0.112 0.2250 8.0000 6.2000 -test s1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test s1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test s1 PRIMARY 1 1.0000 @@ -455,13 +496,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -490,12 +531,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -519,12 +560,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -535,13 +576,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx4); Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -576,12 +617,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -605,12 +646,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -624,13 +665,13 @@ LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/save_index_stats' INTO TABLE mysql.index_stats FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -658,12 +699,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -721,12 +762,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -735,13 +776,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM mysql.column_stats; -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 1.0000 NULL 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b NULL NULL 1.0000 NULL NULL 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -758,13 +799,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -790,12 +831,12 @@ t1 CREATE TABLE `t1` ( KEY `idx3` (`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -820,12 +861,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -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 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -834,13 +875,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM mysql.column_stats; -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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -862,7 +903,7 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(c,e,b) INDEXES(idx2,idx4); @@ -872,10 +913,10 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 idx2 1 7.0000 @@ -912,13 +953,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 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 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -942,19 +983,19 @@ db_name table_name cardinality test t1 40 test t2 40 SELECT * FROM mysql.column_stats 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 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -986,13 +1027,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t2 40 SELECT * FROM mysql.column_stats ORDER BY column_name; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency test t2 PRIMARY 1 1.0000 @@ -1122,12 +1163,12 @@ MODIFY COLUMN b text, ADD INDEX idx1 (b(4), e), ADD INDEX idx4 (e, b(4), d); SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t2 idx3 1 8.5000 @@ -1143,18 +1184,18 @@ ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 -test t1 a 0 49 0.0000 4.0000 1.0000 -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 -test t1 b NULL NULL 0.2000 17.1250 NULL +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t1 b NULL NULL 0.2000 17.1250 NULL NULL NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t2 idx3 1 8.5000 @@ -1187,7 +1228,7 @@ mysql.column_stats analyze status OK SELECT * FROM mysql.table_stats; db_name table_name cardinality SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency set use_stat_tables='never'; @@ -1198,13 +1239,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -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 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 -test t1 b NULL NULL 0.2000 17.1250 NULL +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL +test t1 b NULL NULL 0.2000 17.1250 NULL NULL NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -1426,4 +1467,87 @@ UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency DELETE FROM mysql.table_stats; DELETE FROM mysql.column_stats; DELETE FROM mysql.index_stats; +# +# Bug mdev-4357: empty string as a value of the HIST_SIZE column +# from mysql.column_stats +# +create table t1 (a int); +insert into t1 values (1),(2),(3); +set histogram_size=10; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a 1 3 0.0000 1.0000 10 SINGLE_PREC_HB 0000007F7F7F7FFFFFFF +set histogram_size=default; +drop table t1; +# +# Bug mdev-4359: wrong setting of the HIST_SIZE column +# (see also mdev-4357) from mysql.column_stats +# +create table t1 ( a int); +insert into t1 values (1),(2),(3),(4),(5); +set histogram_size=10; +set histogram_type='double_prec_hb'; +show variables like 'histogram%'; +Variable_name Value +histogram_size 10 +histogram_type DOUBLE_PREC_HB +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t1 a 1 5 0.0000 1.0000 10 DOUBLE_PREC_HB 0000FF3FFF7FFFBFFFFF +set histogram_size=default; +set histogram_type=default; +drop table t1; +# +# Bug mdev-4369: histogram for a column with many distinct values +# +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int); +INSERT INTO t1 (id) VALUES (1), (1), (1),(1); +INSERT INTO t1 (id) SELECT id FROM t1; +INSERT INTO t1 SELECT id+1 FROM t1; +INSERT INTO t1 SELECT id+2 FROM t1; +INSERT INTO t1 SELECT id+4 FROM t1; +INSERT INTO t1 SELECT id+8 FROM t1; +INSERT INTO t1 SELECT id+16 FROM t1; +INSERT INTO t1 SELECT id+32 FROM t1; +INSERT INTO t1 SELECT id+64 FROM t1; +INSERT INTO t1 SELECT id+128 FROM t1; +INSERT INTO t1 SELECT id+256 FROM t1; +INSERT INTO t1 SELECT id+512 FROM t1; +INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); +SELECT COUNT(*) FROM t2; +COUNT(*) +8192 +SELECT COUNT(DISTINCT id) FROM t2; +COUNT(DISTINCT id) +1024 +set @@tmp_table_size=1024*16; +set @@max_heap_table_size=1024*16; +set histogram_size=63; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status OK +select db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, hist_type, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) +test t2 id 1 1024 0.0000 8.0000 63 SINGLE_PREC_HB 03070B0F13171B1F23272B2F33373B3F43474B4F53575B5F63676B6F73777B7F83878B8F93979B9FA3A7ABAFB3B7BBBFC3C7CBCFD3D7DBDFE3E7EBEFF3F7FB +set histogram_size=default; +drop table t1, t2; set use_stat_tables=@save_use_stat_tables; |