diff options
Diffstat (limited to 'mysql-test/r/statistics.result')
| -rw-r--r-- | mysql-test/r/statistics.result | 1720 |
1 files changed, 0 insertions, 1720 deletions
diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result deleted file mode 100644 index ffaaf09acc8..00000000000 --- a/mysql-test/r/statistics.result +++ /dev/null @@ -1,1720 +0,0 @@ -drop table if exists t1,t2; -set @save_use_stat_tables=@@use_stat_tables; -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -set 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) -) ENGINE= MYISAM; -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 Engine-independent statistics collected -test.t1 analyze status OK -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 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 -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 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 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)", -(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 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 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)", -(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 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 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)", -(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 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 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)", -(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 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 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)", -(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 mysql.index_stats -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 mysql.index_stats -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 mysql.index_stats -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 mysql.index_stats -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 -DELETE FROM mysql.column_stats; -set histogram_size=4; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -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 001919FF -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 Engine-independent statistics collected -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 0000031A031AFFFF -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 Engine-independent statistics collected -test.t1 analyze status Table is already up to date -CREATE TABLE t3 ( -a int NOT NULL PRIMARY KEY, -b varchar(32), -c char(16), -INDEX idx (c) -) ENGINE=MYISAM; -INSERT INTO t3 VALUES -(0, NULL, NULL), -(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd'), -(17, 'vvvvvvvvvvvvv', 'aaaa'), -(1, 'vvvvvvvvvvvvv', NULL), -(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'), -(23, 'vvvvvvvvvvvvv', 'dddddddd'), -(8, 'vvvvvvvvvvvvv', 'aaaa'), -(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa'), -(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa'), -(10, NULL, 'aaaa'), -(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'), -(15, 'vvvvvvvvvvvvv', 'ccccccccc'), -(30, NULL, 'bbbbbb'), -(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb'), -(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc'), -(9, 'yyy', 'bbbbbb'), -(29, 'vvvvvvvvvvvvv', 'dddddddd'); -ANALYZE TABLE t3; -Table Op Msg_type Msg_text -test.t3 analyze status Engine-independent statistics collected -test.t3 analyze status OK -SELECT * FROM mysql.table_stats; -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 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 -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 -test t3 PRIMARY 1 1.0000 -test t3 idx 1 3.7500 -ALTER TABLE t1 RENAME TO s1; -SELECT * FROM mysql.table_stats; -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 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 -test s1 idx1 1 6.4000 -test s1 idx1 2 1.6875 -test s1 idx2 1 7.0000 -test s1 idx2 2 2.3846 -test s1 idx3 1 8.5000 -test s1 idx4 1 6.2000 -test s1 idx4 2 1.6875 -test s1 idx4 3 1.1304 -test t3 PRIMARY 1 1.0000 -test t3 idx 1 3.7500 -RENAME TABLE s1 TO t1; -SELECT * FROM mysql.table_stats; -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 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 -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 -test t3 PRIMARY 1 1.0000 -test t3 idx 1 3.7500 -DROP TABLE t3; -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 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 -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 -CREATE TEMPORARY TABLE t0 ( -a int NOT NULL PRIMARY KEY, -b varchar(32) -); -INSERT INTO t0 SELECT a,b FROM t1; -ALTER TABLE t1 CHANGE COLUMN b x varchar(32), -CHANGE COLUMN e y double; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `x` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `y` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`x`,`y`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL -test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL -test t1 y 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL -ALTER TABLE t1 CHANGE COLUMN x b varchar(32), -CHANGE COLUMN y e double; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -s1 CREATE TABLE `s1` ( - `a` int(11) NOT NULL, - `x` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`x`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - KEY `idx4` (`e`,`x`,`d`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -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 hist_size hist_type histogram -test s1 a 0 49 0.0000 4.0000 1.0000 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 s1 x 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 s1 PRIMARY 1 1.0000 -test s1 idx1 1 6.4000 -test s1 idx1 2 1.6875 -test s1 idx2 1 7.0000 -test s1 idx2 2 2.3846 -test s1 idx3 1 8.5000 -test s1 idx4 1 6.2000 -test s1 idx4 2 1.6875 -test s1 idx4 3 1.1304 -ALTER TABLE s1 RENAME TO t1, CHANGE COLUMN x b varchar(32); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - KEY `idx4` (`e`,`b`,`d`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -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 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 -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 -ALTER TABLE t1 CHANGE COLUMN b x varchar(30); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `x` varchar(30) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`x`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx3 1 8.5000 -ALTER TABLE t1 CHANGE COLUMN x b varchar(32); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx3 1 8.5000 -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx4); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -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 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 -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 * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/save_column_stats' - FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' - FROM mysql.column_stats WHERE column_name='b'; -SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/save_index_stats' - FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' - FROM mysql.index_stats WHERE index_name IN ('idx1', 'idx4'); -ALTER TABLE t1 CHANGE COLUMN b x varchar(30); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `x` varchar(30) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`x`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx3 1 8.5000 -ALTER TABLE t1 CHANGE COLUMN x b varchar(32); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` varchar(32) DEFAULT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx3 1 8.5000 -LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/save_column_stats' - INTO TABLE mysql.column_stats -FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; -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 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 -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 -ALTER TABLE t1 DROP COLUMN b; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`e`), - KEY `idx2` (`c`,`d`), - KEY `idx3` (`d`), - 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 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 -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx3 1 8.5000 -DROP INDEX idx2 ON t1; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx1` (`e`), - KEY `idx3` (`d`), - KEY `idx4` (`e`,`d`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -SELECT * FROM mysql.index_stats; -db_name table_name index_name prefix_arity avg_frequency -test t1 PRIMARY 1 1.0000 -test t1 idx3 1 8.5000 -DROP INDEX idx1 ON t1; -DROP INDEX idx4 ON t1; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx3` (`d`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -ALTER TABLE t1 ADD COLUMN b varchar(32); -CREATE INDEX idx1 ON t1(b, e); -CREATE INDEX idx2 ON t1(c, d); -CREATE INDEX idx4 ON t1(e, b, d); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - `b` varchar(32) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx3` (`d`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - 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 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 -test t1 idx3 1 8.5000 -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -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 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 -test t1 idx1 1 NULL -test t1 idx1 2 NULL -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 NULL -test t1 idx4 3 NULL -UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a); -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -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 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 -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 -ALTER TABLE t1 DROP COLUMN b, -DROP INDEX idx1, DROP INDEX idx2, DROP INDEX idx4; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - PRIMARY KEY (`a`), - 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 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 -test t1 idx3 1 8.5000 -ALTER TABLE t1 ADD COLUMN b varchar(32); -ALTER TABLE t1 -ADD INDEX idx1 (b, e), ADD INDEX idx2 (c, d), ADD INDEX idx4 (e, b, d); -UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `c` char(16) DEFAULT NULL, - `d` date DEFAULT NULL, - `e` double DEFAULT NULL, - `f` bit(3) DEFAULT NULL, - `b` varchar(32) DEFAULT NULL, - PRIMARY KEY (`a`), - KEY `idx3` (`d`), - KEY `idx1` (`b`,`e`), - KEY `idx2` (`c`,`d`), - 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 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 -test t1 idx3 1 8.5000 -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -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 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 -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 -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES(); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status Table is already up to date -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 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); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status Table is already up to date -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 hist_size hist_type histogram -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 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL -SELECT * FROM mysql.index_stats; -db_name table_name index_name prefix_arity avg_frequency -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx4 1 6.2000 -test t1 idx4 2 1.6875 -test t1 idx4 3 1.1304 -DELETE FROM mysql.index_stats WHERE table_name='t1' AND index_name='primary'; -SELECT * FROM mysql.index_stats; -db_name table_name index_name prefix_arity avg_frequency -test t1 idx2 1 7.0000 -test t1 idx2 2 2.3846 -test t1 idx4 1 6.2000 -test t1 idx4 2 1.6875 -test t1 idx4 3 1.1304 -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES(primary); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status Table is already up to date -SELECT * FROM mysql.index_stats; -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 idx4 1 6.2000 -test t1 idx4 2 1.6875 -test t1 idx4 3 1.1304 -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS ALL INDEXES ALL; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status Table is already up to date -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 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 -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 -CREATE TABLE t2 LIKE t1; -ALTER TABLE t2 ENGINE=InnoDB; -INSERT INTO t2 SELECT * FROM t1; -set optimizer_switch='extended_keys=off'; -ANALYZE TABLE t2; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -SELECT * FROM mysql.table_stats; -db_name table_name cardinality -test t1 40 -test t2 40 -SELECT * FROM mysql.column_stats ORDER BY column_name, table_name; -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 -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_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -set optimizer_switch='extended_keys=on'; -ANALYZE TABLE t2; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -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 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 -test t2 idx1 1 6.4000 -test t2 idx1 2 1.6875 -test t2 idx1 3 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.6875 -test t2 idx4 3 1.1304 -test t2 idx4 4 1.0000 -ALTER TABLE t2 DROP PRIMARY KEY, DROP INDEX idx1; -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 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx3 1 8.5000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.6875 -test t2 idx4 3 1.1304 -UPDATE t2 SET b=0 WHERE b IS NULL; -ALTER TABLE t2 ADD PRIMARY KEY (a,b); -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 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx3 1 8.5000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.6875 -test t2 idx4 3 1.1304 -ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -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 -test t2 PRIMARY 2 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx2 4 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx3 3 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.7222 -test t2 idx4 3 1.1154 -test t2 idx4 4 1.0000 -ALTER TABLE t2 CHANGE COLUMN b b varchar(30); -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 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx3 1 8.5000 -ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -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 -test t2 PRIMARY 2 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx2 4 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx3 3 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.7222 -test t2 idx4 3 1.1154 -test t2 idx4 4 1.0000 -ALTER TABLE t2 CHANGE COLUMN b b varchar(32); -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 -test t2 PRIMARY 2 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx2 4 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx3 3 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.7222 -test t2 idx4 3 1.1154 -test t2 idx4 4 1.0000 -ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -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 -test t2 PRIMARY 2 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx2 4 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx3 3 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 1.7222 -test t2 idx4 3 1.1154 -test t2 idx4 4 1.0000 -ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a); -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 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx3 1 8.5000 -ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; -Table Op Msg_type Msg_text -test.t2 analyze status Engine-independent statistics collected -test.t2 analyze status OK -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 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 2.2308 -test t2 idx4 3 1.0000 -set optimizer_switch='extended_keys=off'; -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); -SELECT * FROM mysql.column_stats; -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 PRIMARY 1 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 2.2308 -test t2 idx4 3 1.0000 -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' -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 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 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 t1 PRIMARY 1 1.0000 -test t1 idx1 1 NULL -test t1 idx1 2 NULL -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 NULL -test t1 idx4 3 NULL -test t2 PRIMARY 1 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 -test t2 idx2 3 1.0000 -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx4 1 6.2000 -test t2 idx4 2 2.2308 -test t2 idx4 3 1.0000 -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -ANALYZE TABLE mysql.column_stats PERSISTENT FOR ALL; -Table Op Msg_type Msg_text -mysql.column_stats analyze error Invalid argument -ANALYZE TABLE mysql.column_stats; -Table Op Msg_type Msg_text -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 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'; -ANALYZE TABLE t1 PERSISTENT FOR ALL; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' -test.t1 analyze status Table is already up to date -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 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 -test t1 idx1 1 NULL -test t1 idx1 2 NULL -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 NULL -test t1 idx4 3 NULL -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' -test.t1 analyze status Table is already up to date -ANALYZE TABLE t1 PERSISTENT FOR columns(a,b) INDEXES(); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' -test.t1 analyze status Table is already up to date -ANALYZE TABLE t1 PERSISTENT FOR columns(b) indexes(idx2); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' -test.t1 analyze status Table is already up to date -ANALYZE TABLE t1 PERSISTENT FOR columns() indexes(idx2); -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status Table is already up to date -DELETE FROM mysql.table_stats; -DELETE FROM mysql.column_stats; -DELETE FROM mysql.index_stats; -DROP TABLE t1,t2; -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 use_stat_tables='preferably'; -ANALYZE TABLE Country, City, CountryLanguage; -SELECT UPPER(db_name), UPPER(table_name), cardinality -FROM mysql.table_stats; -UPPER(db_name) UPPER(table_name) cardinality -WORLD CITY 4079 -WORLD COUNTRY 239 -WORLD COUNTRYLANGUAGE 984 -SELECT UPPER(db_name), UPPER(table_name), -column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency -FROM mysql.column_stats; -UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency -WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 -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 Population 42 10500000 0.0000 4.0000 1.0467 -WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 -WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 -WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 -WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 -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 UPPER(db_name), UPPER(table_name), -index_name, prefix_arity, avg_frequency -FROM mysql.index_stats; -UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency -WORLD CITY Country 1 17.5819 -WORLD CITY PRIMARY 1 1.0000 -WORLD CITY Population 1 1.0467 -WORLD COUNTRY Name 1 1.0000 -WORLD COUNTRY PRIMARY 1 1.0000 -WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 -WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 -WORLD COUNTRYLANGUAGE Percentage 1 2.7640 -use test; -set 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 use_stat_tables='preferably'; -ANALYZE TABLE Country, City, CountryLanguage; -SELECT UPPER(db_name), UPPER(table_name), cardinality -FROM mysql.table_stats; -UPPER(db_name) UPPER(table_name) cardinality -WORLD CITY 4079 -WORLD COUNTRY 239 -WORLD COUNTRYLANGUAGE 984 -WORLD_INNODB CITY 4079 -WORLD_INNODB COUNTRY 239 -WORLD_INNODB COUNTRYLANGUAGE 984 -SELECT UPPER(db_name), UPPER(table_name), -column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency -FROM mysql.column_stats; -UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency -WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 -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 Population 42 10500000 0.0000 4.0000 1.0467 -WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 -WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 -WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 -WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 -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 CITY Country ABW ZWE 0.0000 3.0000 17.5819 -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 Population 42 10500000 0.0000 4.0000 1.0467 -WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 -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 Population 0 1277558000 0.0000 4.0000 1.0575 -WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 -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 UPPER(db_name), UPPER(table_name), -index_name, prefix_arity, avg_frequency -FROM mysql.index_stats; -UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency -WORLD CITY Country 1 17.5819 -WORLD CITY PRIMARY 1 1.0000 -WORLD CITY Population 1 1.0467 -WORLD COUNTRY Name 1 1.0000 -WORLD COUNTRY PRIMARY 1 1.0000 -WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 -WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 -WORLD COUNTRYLANGUAGE Percentage 1 2.7640 -WORLD_INNODB CITY Country 1 17.5819 -WORLD_INNODB CITY PRIMARY 1 1.0000 -WORLD_INNODB CITY Population 1 1.0467 -WORLD_INNODB COUNTRY Name 1 1.0000 -WORLD_INNODB COUNTRY PRIMARY 1 1.0000 -WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 -WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 -WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 -use world; -set use_stat_tables='preferably'; -set histogram_size=100; -set histogram_type='SINGLE_PREC_HB'; -ANALYZE TABLE CountryLanguage; -set histogram_size=254; -set histogram_type='DOUBLE_PREC_HB'; -ANALYZE TABLE City; -FLUSH TABLES; -select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='COUNTRYLANGUAGE' and UPPER(column_name) = 'PERCENTAGE';; -UPPER(db_name) WORLD -UPPER(table_name) COUNTRYLANGUAGE -UPPER(column_name) PERCENTAGE -min_value 0.0 -max_value 99.9 -nulls_ratio 0.0000 -avg_length 4.0000 -avg_frequency 2.7640 -hist_size 100 -hist_type SINGLE_PREC_HB -hex(histogram) 0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF -decode_histogram(hist_type,histogram) 0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 -select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='CITY' and UPPER(column_name) = 'POPULATION';; -UPPER(db_name) WORLD -UPPER(table_name) CITY -UPPER(column_name) POPULATION -min_value 42 -max_value 10500000 -nulls_ratio 0.0000 -avg_length 4.0000 -avg_frequency 1.0467 -hist_size 254 -hist_type DOUBLE_PREC_HB -hex(histogram) 1F00A1002B023002350238023F02430249024E02520258025D02630268026E02720276027B02800285028C02920297029D02A102A802AC02B402BC02C402CC02D302DA02E302EA02F102F802010305030C03120319031F03290333033D0343034F03590363036D037803840390039A03A603B303C303D103E003F203020412042404330440045304600472047F049104A204B804C804DE04F2040A0526053F0558056F058E05B305D905F4051306380667068406AB06DA06020731075C079407C507F8072E085E08A508DF0824096909CC092E0A760AD50A400BA90B150CAD0C310D240E130F0E103B11B9126B14F0166B192F1CB71FFF240630483FC567 -decode_histogram(hist_type,histogram) 0.00047,0.00198,0.00601,0.00008,0.00008,0.00005,0.00011,0.00006,0.00009,0.00008,0.00006,0.00009,0.00008,0.00009,0.00008,0.00009,0.00006,0.00006,0.00008,0.00008,0.00008,0.00011,0.00009,0.00008,0.00009,0.00006,0.00011,0.00006,0.00012,0.00012,0.00012,0.00012,0.00011,0.00011,0.00014,0.00011,0.00011,0.00011,0.00014,0.00006,0.00011,0.00009,0.00011,0.00009,0.00015,0.00015,0.00015,0.00009,0.00018,0.00015,0.00015,0.00015,0.00017,0.00018,0.00018,0.00015,0.00018,0.00020,0.00024,0.00021,0.00023,0.00027,0.00024,0.00024,0.00027,0.00023,0.00020,0.00029,0.00020,0.00027,0.00020,0.00027,0.00026,0.00034,0.00024,0.00034,0.00031,0.00037,0.00043,0.00038,0.00038,0.00035,0.00047,0.00056,0.00058,0.00041,0.00047,0.00056,0.00072,0.00044,0.00060,0.00072,0.00061,0.00072,0.00066,0.00085,0.00075,0.00078,0.00082,0.00073,0.00108,0.00089,0.00105,0.00105,0.00151,0.00150,0.00110,0.00145,0.00163,0.00160,0.00165,0.00232,0.00201,0.00371,0.00365,0.00383,0.00459,0.00583,0.00662,0.00984,0.00969,0.01080,0.01379,0.02063,0.04308,0.05960,0.15816,0.59464 -set histogram_type=default; -set histogram_size=default; -use test; -DROP DATABASE world; -SELECT UPPER(db_name), UPPER(table_name), cardinality -FROM mysql.table_stats; -UPPER(db_name) UPPER(table_name) cardinality -WORLD_INNODB CITY 4079 -WORLD_INNODB COUNTRY 239 -WORLD_INNODB COUNTRYLANGUAGE 984 -SELECT UPPER(db_name), UPPER(table_name), -column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency -FROM mysql.column_stats; -UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency -WORLD_INNODB CITY Country ABW ZWE 0.0000 3.0000 17.5819 -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 Population 42 10500000 0.0000 4.0000 1.0467 -WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 -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 Population 0 1277558000 0.0000 4.0000 1.0575 -WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 -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 UPPER(db_name), UPPER(table_name), -index_name, prefix_arity, avg_frequency -FROM mysql.index_stats; -UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency -WORLD_INNODB CITY Country 1 17.5819 -WORLD_INNODB CITY PRIMARY 1 1.0000 -WORLD_INNODB CITY Population 1 1.0467 -WORLD_INNODB COUNTRY Name 1 1.0000 -WORLD_INNODB COUNTRY PRIMARY 1 1.0000 -WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 -WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 -WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 -DROP DATABASE world_innodb; -SELECT UPPER(db_name), UPPER(table_name), cardinality -FROM mysql.table_stats; -UPPER(db_name) UPPER(table_name) cardinality -SELECT UPPER(db_name), UPPER(table_name), -column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency -FROM mysql.column_stats; -UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency -SELECT UPPER(db_name), UPPER(table_name), -index_name, prefix_arity, avg_frequency -FROM mysql.index_stats; -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 Engine-independent statistics collected -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 Engine-independent statistics collected -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 Engine-independent statistics collected -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; -# -# Bug MDEV-7383: min/max value for a column not utf8 compatible -# -create table t1 (a varchar(100)) engine=MyISAM; -insert into t1 values(unhex('D879626AF872675F73E662F8')); -analyze table t1 persistent for all; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -show warnings; -Level Code Message -select db_name, table_name, column_name, -HEX(min_value), HEX(max_value), -nulls_ratio, avg_frequency, -hist_size, hist_type, HEX(histogram) -FROM mysql.column_stats; -db_name table_name column_name HEX(min_value) HEX(max_value) nulls_ratio avg_frequency hist_size hist_type HEX(histogram) -test t1 a D879626AF872675F73E662F8 D879626AF872675F73E662F8 0.0000 1.0000 0 NULL NULL -drop table t1; -# -# MDEV-9590: Always print "Engine-independent statistic" warnings and -# might be filtering columns unintentionally from engines -# -set use_stat_tables='NEVER'; -create table t1 (test blob); -show variables like 'use_stat_tables'; -Variable_name Value -use_stat_tables NEVER -analyze table t1; -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -drop table t1; -# -# MDEV-10435 crash with bad stat tables -# -set use_stat_tables='preferably'; -call mtr.add_suppression("Column count of mysql.table_stats is wrong. Expected 3, found 1. The table is probably corrupted"); -rename table mysql.table_stats to test.table_stats; -flush tables; -create table t1 (a int); -rename table t1 to t2, t3 to t4; -ERROR 42S02: Table 'test.t3' doesn't exist -drop table t1; -rename table test.table_stats to mysql.table_stats; -rename table mysql.table_stats to test.table_stats; -create table mysql.table_stats (a int); -flush tables; -create table t1 (a int); -rename table t1 to t2, t3 to t4; -ERROR 42S02: Table 'test.t3' doesn't exist -drop table t1, mysql.table_stats; -rename table test.table_stats to mysql.table_stats; -set use_stat_tables=@save_use_stat_tables; -# -# Start of 10.2 tests -# -# -# MDEV-10134 Add full support for DEFAULT -# -CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a)); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` blob DEFAULT NULL, - `b` text DEFAULT decode_histogram('SINGLE_PREC_HB',`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -INSERT INTO t1 (a) VALUES (0x0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF); -SELECT b FROM t1; -b -0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 -DROP TABLE t1; -# -# End of 10.2 tests -# |