diff options
Diffstat (limited to 'mysql-test/main/statistics_json.result')
| -rw-r--r-- | mysql-test/main/statistics_json.result | 7798 |
1 files changed, 7798 insertions, 0 deletions
diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result new file mode 100644 index 00000000..7a6ed6d2 --- /dev/null +++ b/mysql-test/main/statistics_json.result @@ -0,0 +1,7798 @@ +# +# Test that we can store JSON arrays in histogram field mysql.column_stats when histogram_type=JSON +# +set @SINGLE_PREC_TYPE='single_prec_hb'; +set @DOUBLE_PREC_TYPE='double_prec_hb'; +set @DEFAULT_HIST_TYPE='double_prec_hb'; +set @SINGLE_PREC_TYPE='JSON_HB'; +set @DOUBLE_PREC_TYPE='JSON_HB'; +set @DEFAULT_HIST_TYPE='JSON_HB'; +set @save_use_stat_tables=@@use_stat_tables; +set @save_histogram_size=@@global.histogram_size; +set @@global.histogram_size=0,@@local.histogram_size=0; +set @save_hist_type=@DEFAULT_HIST_TYPE; +set histogram_type=@SINGLE_PREC_TYPE; +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 NULL 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 NULL 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, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type decode_histogram(hist_type,histogram) +test t1 a 0 49 0.0000 1.0000 NULL NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 JSON_HB { + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "vvvvvvvvvvvvv", + "size": 0.28125, + "ndv": 2 + }, + { + "start": "wwwwwwwwwwwwwwwwwwwwwwwwwwww", + "size": 0.28125, + "ndv": 2 + }, + { + "start": "xxxxxxxxxxxxxxxxxxxxxxxxxx", + "size": 0.28125, + "ndv": 3 + }, + { + "start": "zzzzzzzzzzzzzzzzzz", + "end": "zzzzzzzzzzzzzzzzzz", + "size": 0.15625, + "ndv": 1 + } + ] +} +test t1 c aaaa dddddddd 0.1250 7.0000 4 JSON_HB { + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "aaaa", + "size": 0.257142857, + "ndv": 1 + }, + { + "start": "bbb", + "size": 0.257142857, + "ndv": 3 + }, + { + "start": "ccccccccc", + "size": 0.257142857, + "ndv": 2 + }, + { + "start": "dddddddd", + "end": "dddddddd", + "size": 0.228571429, + "ndv": 1 + } + ] +} +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 3 JSON_HB { + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1989-03-12", + "size": 0.323529412, + "ndv": 1 + }, + { + "start": "1990-05-15", + "size": 0.411764706, + "ndv": 1 + }, + { + "start": "1998-08-28", + "end": "1999-07-23", + "size": 0.264705882, + "ndv": 2 + } + ] +} +test t1 e 0.01 0.112 0.2250 6.2000 4 JSON_HB { + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0.01", + "size": 0.387096774, + "ndv": 1 + }, + { + "start": "0.012", + "size": 0.258064516, + "ndv": 3 + }, + { + "start": "0.1", + "size": 0.258064516, + "ndv": 2 + }, + { + "start": "0.112", + "end": "0.112", + "size": 0.096774194, + "ndv": 1 + } + ] +} +test t1 f 1 5 0.2000 6.4000 4 JSON_HB { + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start_hex": "01", + "size": 0.28125, + "ndv": 2 + }, + { + "start_hex": "02", + "size": 0.28125, + "ndv": 2 + }, + { + "start_hex": "04", + "size": 0.3125, + "ndv": 1 + }, + { + "start_hex": "05", + "end_hex": "05", + "size": 0.125, + "ndv": 1 + } + ] +} +DELETE FROM mysql.column_stats; +set histogram_size=8; +set histogram_type=@DOUBLE_PREC_TYPE; +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, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type decode_histogram(hist_type,histogram) +test t1 a 0 49 0.0000 1.0000 NULL NULL NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 5 JSON_HB { + "target_histogram_size": 8, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "vvvvvvvvvvvvv", + "size": 0.25, + "ndv": 1 + }, + { + "start": "wwwwwwwwwwwwwwwwwwwwwwwwwwww", + "size": 0.21875, + "ndv": 1 + }, + { + "start": "xxxxxxxxxxxxxxxxxxxxxxxxxx", + "size": 0.125, + "ndv": 1 + }, + { + "start": "yyy", + "size": 0.1875, + "ndv": 1 + }, + { + "start": "zzzzzzzzzzzzzzzzzz", + "end": "zzzzzzzzzzzzzzzzzz", + "size": 0.21875, + "ndv": 1 + } + ] +} +test t1 c aaaa dddddddd 0.1250 7.0000 5 JSON_HB { + "target_histogram_size": 8, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "aaaa", + "size": 0.257142857, + "ndv": 1 + }, + { + "start": "bbb", + "size": 0.142857143, + "ndv": 2 + }, + { + "start": "bbbbbb", + "size": 0.085714286, + "ndv": 1 + }, + { + "start": "ccccccccc", + "size": 0.228571429, + "ndv": 1 + }, + { + "start": "dddddddd", + "end": "dddddddd", + "size": 0.285714286, + "ndv": 1 + } + ] +} +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 JSON_HB { + "target_histogram_size": 8, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1989-03-12", + "size": 0.323529412, + "ndv": 1 + }, + { + "start": "1990-05-15", + "size": 0.411764706, + "ndv": 1 + }, + { + "start": "1998-08-28", + "size": 0.147058824, + "ndv": 2 + }, + { + "start": "1999-07-23", + "end": "1999-07-23", + "size": 0.117647059, + "ndv": 1 + } + ] +} +test t1 e 0.01 0.112 0.2250 6.2000 5 JSON_HB { + "target_histogram_size": 8, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0.01", + "size": 0.387096774, + "ndv": 1 + }, + { + "start": "0.012", + "size": 0.129032258, + "ndv": 2 + }, + { + "start": "0.05", + "size": 0.096774194, + "ndv": 1 + }, + { + "start": "0.1", + "size": 0.258064516, + "ndv": 1 + }, + { + "start": "0.112", + "end": "0.112", + "size": 0.129032258, + "ndv": 1 + } + ] +} +test t1 f 1 5 0.2000 6.4000 5 JSON_HB { + "target_histogram_size": 8, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start_hex": "01", + "size": 0.125, + "ndv": 1 + }, + { + "start_hex": "02", + "size": 0.25, + "ndv": 1 + }, + { + "start_hex": "03", + "size": 0.1875, + "ndv": 1 + }, + { + "start_hex": "04", + "size": 0.3125, + "ndv": 1 + }, + { + "start_hex": "05", + "end_hex": "05", + "size": 0.125, + "ndv": 1 + } + ] +} +DELETE FROM mysql.column_stats; +set histogram_size= 0; +set histogram_type=@SINGLE_PREC_TYPE; +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 NULL 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 NULL 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 NULL 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 NULL 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 NULL 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 NULL 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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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(x) INDEXES(); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM mysql.column_stats where column_name="x"; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +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 COLLATE=latin1_swedish_ci +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 NULL 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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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' IGNORE +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 NULL 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 NULL 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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 COLLATE=latin1_swedish_ci +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 NULL 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 NULL 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 NULL 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 NULL NULL NULL +test t2 a 0 49 0.0000 4.0000 1.0000 NULL 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 NULL 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 +SELECT * FROM mysql.column_stats where 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 t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL +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 +SELECT * FROM mysql.column_stats where column_name="b"; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +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 +SELECT * FROM mysql.column_stats where 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 t2 b 0 zzzzzzzzzzzzzzzzzz 0.0000 13.9000 6.6667 0 NULL NULL +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 NULL 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 NULL 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.1172 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.1172 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.1172 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_TYPE; +ANALYZE TABLE CountryLanguage; +set histogram_size=254; +set histogram_type=@DOUBLE_PREC_TYPE; +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,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 85 +hist_type JSON_HB +decode_histogram(hist_type,histogram) { + "target_histogram_size": 100, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0.0", + "size": 0.066056911, + "ndv": 1 + }, + { + "start": "0.1", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.2", + "size": 0.022357724, + "ndv": 1 + }, + { + "start": "0.3", + "size": 0.017276423, + "ndv": 1 + }, + { + "start": "0.4", + "size": 0.025406504, + "ndv": 1 + }, + { + "start": "0.5", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.6", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.7", + "size": 0.017276423, + "ndv": 1 + }, + { + "start": "0.8", + "size": 0.010162602, + "ndv": 1 + }, + { + "start": "0.9", + "size": 0.010162602, + "ndv": 1 + }, + { + "start": "1.0", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "1.1", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "1.3", + "size": 0.012195122, + "ndv": 1 + }, + { + "start": "1.4", + "size": 0.015243902, + "ndv": 1 + }, + { + "start": "1.5", + "size": 0.005081301, + "ndv": 1 + }, + { + "start": "1.6", + "size": 0.015243902, + "ndv": 1 + }, + { + "start": "1.7", + "size": 0.010162602, + "ndv": 1 + }, + { + "start": "1.8", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "1.9", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.0", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "2.2", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.3", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.4", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.5", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.7", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "2.8", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "3.0", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "3.2", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "3.3", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "3.5", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "3.7", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "3.8", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "4.1", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "4.4", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "4.8", + "size": 0.010162602, + "ndv": 2 + }, + { + "start": "4.9", + "size": 0.010162602, + "ndv": 5 + }, + { + "start": "5.3", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "5.5", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "5.7", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "6.0", + "size": 0.010162602, + "ndv": 5 + }, + { + "start": "6.4", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "6.7", + "size": 0.010162602, + "ndv": 5 + }, + { + "start": "7.2", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "7.4", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "7.7", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "8.0", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "8.5", + "size": 0.010162602, + "ndv": 3 + }, + { + "start": "8.7", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "9.1", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "9.5", + "size": 0.010162602, + "ndv": 4 + }, + { + "start": "10.1", + "size": 0.010162602, + "ndv": 6 + }, + { + "start": "10.8", + "size": 0.010162602, + "ndv": 6 + }, + { + "start": "11.4", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "12.1", + "size": 0.010162602, + "ndv": 6 + }, + { + "start": "12.8", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "13.8", + "size": 0.010162602, + "ndv": 6 + }, + { + "start": "14.6", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "16.1", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "17.1", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "19.0", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "20.3", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "22.7", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "23.8", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "29.7", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "32.1", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "34.8", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "39.9", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "44.6", + "size": 0.010162602, + "ndv": 10 + }, + { + "start": "49.1", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "52.0", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "58.4", + "size": 0.010162602, + "ndv": 10 + }, + { + "start": "64.7", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "69.9", + "size": 0.010162602, + "ndv": 10 + }, + { + "start": "76.7", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "80.0", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "85.0", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "87.0", + "size": 0.010162602, + "ndv": 9 + }, + { + "start": "89.5", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "92.0", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "93.6", + "size": 0.010162602, + "ndv": 8 + }, + { + "start": "95.7", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "96.9", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "98.0", + "size": 0.010162602, + "ndv": 7 + }, + { + "start": "99.0", + "size": 0.006097561, + "ndv": 4 + }, + { + "start": "99.9", + "end": "99.9", + "size": 0.015243902, + "ndv": 1 + } + ] +} +select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,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 240 +hist_type JSON_HB +decode_histogram(hist_type,histogram) { + "target_histogram_size": 254, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "42", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1636", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "5808", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "16243", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "29034", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "71000", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "89200", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "89447", + "size": 0.004167688, + "ndv": 10 + }, + { + "start": "90000", + "size": 0.004167688, + "ndv": 11 + }, + { + "start": "90500", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "90814", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "91100", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "91700", + "size": 0.004167688, + "ndv": 13 + }, + { + "start": "92044", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "92574", + "size": 0.004167688, + "ndv": 13 + }, + { + "start": "92988", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "93342", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "93900", + "size": 0.004167688, + "ndv": 13 + }, + { + "start": "94200", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "94700", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "95052", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "95521", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "96100", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "96626", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "96938", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "97300", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "97929", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "98293", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "98640", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "99300", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "99781", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "100118", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "100490", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "100924", + "size": 0.004167688, + "ndv": 12 + }, + { + "start": "101295", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "101660", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "102121", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "102379", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "102820", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "103300", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "103653", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "104400", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "105080", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "105530", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "106000", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "106400", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "106996", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "107329", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "107770", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "108254", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "108600", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "109225", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "109600", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "110034", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "110700", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "111454", + "size": 0.004167688, + "ndv": 12 + }, + { + "start": "112007", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "112673", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "113494", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "114065", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "114815", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "115483", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "116132", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "116695", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "117258", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "118080", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "118815", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "119391", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "119990", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "120700", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "121197", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "121842", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "122400", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "123273", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "123776", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "124072", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "124600", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "125236", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "125700", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "126282", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "126872", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "127350", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "127898", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "128651", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "129688", + "size": 0.004167688, + "ndv": 13 + }, + { + "start": "130215", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "131149", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "132127", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "132820", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "133443", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "133936", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "134835", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "136062", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "137000", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "137700", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "138418", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "139357", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "140169", + "size": 0.004167688, + "ndv": 13 + }, + { + "start": "141132", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "142170", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "142990", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "144126", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "145150", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "146105", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "147124", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "147939", + "size": 0.004167688, + "ndv": 14 + }, + { + "start": "148867", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "149900", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "151000", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "152194", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "153344", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "154980", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "155941", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "157358", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "158720", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "160359", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "161500", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "163100", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "164367", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "165583", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "167183", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "168953", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "170123", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "171363", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "172648", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "173878", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "174984", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "176576", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "178200", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "179258", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "180400", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "182148", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "183261", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "184500", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "185951", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "187557", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "189036", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "190255", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "192509", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "194100", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "195468", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "197000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "199000", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "200901", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "202451", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "204900", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "206338", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "208054", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "211068", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "213271", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "215373", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "217499", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "219761", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "222030", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "224044", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "226573", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "229212", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "232811", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "235760", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "239124", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "241769", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "243825", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "246535", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "249200", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "253587", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "255617", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "259537", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "262947", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "265211", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "269393", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "272058", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "275990", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "278829", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "282197", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "286848", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "291000", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "294125", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "299118", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "301504", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "305699", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "311200", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "315083", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "319373", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "324662", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "328711", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "332800", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "337966", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "342200", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "348100", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "353400", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "358663", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "362470", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "366712", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "375000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "381725", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "386236", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "395402", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "403151", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "411542", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "419000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "425579", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "433180", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "441649", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "450180", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "459884", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "469533", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "476668", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "483155", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "495540", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "510000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "520000", + "size": 0.004167688, + "ndv": 15 + }, + { + "start": "530965", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "554636", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "568855", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "587211", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "606932", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "624269", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "650100", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "669181", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "701827", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "728060", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "762000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "794246", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "830000", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "879000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "947483", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1002239", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "1060257", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "1119117", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1186926", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "1248700", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1346176", + "size": 0.004167688, + "ndv": 16 + }, + { + "start": "1458483", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1615369", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "1861265", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "2117500", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "2500000", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "2896016", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "4017733", + "size": 0.004167688, + "ndv": 17 + }, + { + "start": "6758845", + "end": "10500000", + "size": 0.00392253, + "ndv": 16 + } + ] +} +set histogram_type=@SINGLE_PREC_TYPE; +set histogram_size=0; +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.1172 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, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type decode_histogram(hist_type,histogram) +test t1 a 1 3 0.0000 1.0000 3 JSON_HB { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.333333333, + "ndv": 1 + }, + { + "start": "2", + "size": 0.333333333, + "ndv": 1 + }, + { + "start": "3", + "end": "3", + "size": 0.333333333, + "ndv": 1 + } + ] +} +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_TYPE; +show variables like 'histogram%'; +Variable_name Value +histogram_size 10 +histogram_type JSON_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, decode_histogram(hist_type,histogram) +FROM mysql.column_stats +ORDER BY db_name, table_name, column_name; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type decode_histogram(hist_type,histogram) +test t1 a 1 5 0.0000 1.0000 5 JSON_HB { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.2, + "ndv": 1 + }, + { + "start": "2", + "size": 0.2, + "ndv": 1 + }, + { + "start": "3", + "size": 0.2, + "ndv": 1 + }, + { + "start": "4", + "size": 0.2, + "ndv": 1 + }, + { + "start": "5", + "end": "5", + "size": 0.2, + "ndv": 1 + } + ] +} +set histogram_size=0; +set histogram_type=@SINGLE_PREC_TYPE; +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, decode_histogram(hist_type,histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type decode_histogram(hist_type,histogram) +test t2 id 1 1024 0.0000 8.0000 63 JSON_HB { + "target_histogram_size": 63, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "17", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "33", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "50", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "66", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "82", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "99", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "115", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "132", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "148", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "164", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "181", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "197", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "213", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "230", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "246", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "263", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "279", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "295", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "312", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "328", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "344", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "361", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "377", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "394", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "410", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "426", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "443", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "459", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "475", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "492", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "508", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "525", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "541", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "557", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "574", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "590", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "606", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "623", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "639", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "656", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "672", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "688", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "705", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "721", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "737", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "754", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "770", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "787", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "803", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "819", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "836", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "852", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "868", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "885", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "901", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "918", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "934", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "950", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "967", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "983", + "size": 0.015991211, + "ndv": 17 + }, + { + "start": "999", + "size": 0.015991211, + "ndv": 18 + }, + { + "start": "1016", + "end": "1024", + "size": 0.008544922, + "ndv": 9 + } + ] +} +set histogram_size=0; +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, decode_histogram(hist_type,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 decode_histogram(hist_type,histogram) +test t1 a D879626AF872675F73E662F8 D879626AF872675F73E662F8 0.0000 1.0000 0 NULL NULL +drop table t1; +# +# MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): +# Encountered illegal value '' when converting to DECIMAL +# +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=3, use_stat_tables=preferably; +create table t1 (id int(10),cost decimal(9,2)) engine=innodb; +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 +create temporary table t2 (id int); +insert into t2 (id) select id from t1 where cost > 0; +select * from t2; +id +set use_stat_tables=@save_use_stat_tables; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; +# +# MDEV-16507: statistics for temporary tables should not be used +# +SET +@save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +SET @@use_stat_tables = preferably ; +SET @@optimizer_use_condition_selectivity = 4; +CREATE TABLE t1 ( +TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP +); +SET @had_t1_table= @@warning_count != 0; +CREATE TEMPORARY TABLE tmp_t1 LIKE t1; +INSERT INTO tmp_t1 VALUES (now()); +INSERT INTO t1 SELECT * FROM tmp_t1 WHERE @had_t1_table=0; +DROP TABLE t1; +SET +use_stat_tables=@save_use_stat_tables; +SET +optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +# End of 10.0 tests +# +# 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; +# +# MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed. +# +create temporary table t1(a int); +insert into t1 values (1),(2),(3); +set use_stat_tables=preferably; +set @optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=4; +select * from t1 where a >= 2; +a +2 +3 +drop table t1; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; +# +# Start of 10.2 tests +# +# +# MDEV-10134 Add full support for DEFAULT +# +# +# End of 10.2 tests +# +set histogram_size=@save_histogram_size, histogram_type=@save_hist_type; +# +# Start of 10.4 tests +# +# +# Test analyze_sample_percentage system variable. +# +set @save_use_stat_tables=@@use_stat_tables; +set @save_analyze_sample_percentage=@@analyze_sample_percentage; +set session rand_seed1=42; +set session rand_seed2=62; +set use_stat_tables=PREFERABLY; +set histogram_size=10; +CREATE TABLE t1 (id int); +INSERT INTO t1 (id) VALUES (1), (1), (1), (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 t1 SELECT id+1024 FROM t1; +INSERT INTO t1 SELECT id+2048 FROM t1; +INSERT INTO t1 SELECT id+4096 FROM t1; +INSERT INTO t1 SELECT id+9192 FROM t1; +# +# This query will should show a full table scan analysis. +# +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, +DECODE_HISTOGRAM(hist_type, histogram) +from mysql.column_stats; +table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) +t1 id 1 17384 0.0000 4.0000 14.0000 { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "1639", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "3277", + "size": 0.100001744, + "ndv": 1640 + }, + { + "start": "4916", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "6554", + "size": 0.100001744, + "ndv": 1640 + }, + { + "start": "9193", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "10831", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "12470", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "14108", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "15746", + "end": "17384", + "size": 0.099984305, + "ndv": 1639 + } + ] +} +set analyze_sample_percentage=0.1; +# +# This query will show an innacurate avg_frequency value. +# +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 table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, +DECODE_HISTOGRAM(hist_type, histogram) +from mysql.column_stats; +table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) +t1 id 111 17026 0.0000 4.0000 10.4739 { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "111", + "size": 0.103773585, + "ndv": 21 + }, + { + "start": "1074", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "2504", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "4395", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "6165", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "8082", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "10671", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "12738", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "14487", + "size": 0.103773585, + "ndv": 22 + }, + { + "start": "15785", + "end": "17026", + "size": 0.066037736, + "ndv": 14 + } + ] +} +# +# This query will show a better avg_frequency value. +# +set analyze_sample_percentage=25; +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 table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, +DECODE_HISTOGRAM(hist_type, histogram) +from mysql.column_stats; +table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) +t1 id 1 17384 0.0000 4.0000 14.0401 { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.100015657, + "ndv": 1591 + }, + { + "start": "1624", + "size": 0.100015657, + "ndv": 1599 + }, + { + "start": "3252", + "size": 0.100015657, + "ndv": 1587 + }, + { + "start": "4868", + "size": 0.100015657, + "ndv": 1594 + }, + { + "start": "6483", + "size": 0.100015657, + "ndv": 1632 + }, + { + "start": "8153", + "size": 0.100015657, + "ndv": 1607 + }, + { + "start": "10791", + "size": 0.100015657, + "ndv": 1619 + }, + { + "start": "12435", + "size": 0.100015657, + "ndv": 1627 + }, + { + "start": "14080", + "size": 0.100015657, + "ndv": 1613 + }, + { + "start": "15727", + "end": "17384", + "size": 0.099859084, + "ndv": 1622 + } + ] +} +set analyze_sample_percentage=0; +# +# Test self adjusting sampling level. +# +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 table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, +DECODE_HISTOGRAM(hist_type, histogram) +from mysql.column_stats; +table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) +t1 id 1 17384 0.0000 4.0000 13.9812 { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.100007372, + "ndv": 1651 + }, + { + "start": "1651", + "size": 0.100007372, + "ndv": 1656 + }, + { + "start": "3306", + "size": 0.100007372, + "ndv": 1643 + }, + { + "start": "4949", + "size": 0.100007372, + "ndv": 1648 + }, + { + "start": "6597", + "size": 0.100007372, + "ndv": 1644 + }, + { + "start": "9240", + "size": 0.100007372, + "ndv": 1624 + }, + { + "start": "10864", + "size": 0.100007372, + "ndv": 1633 + }, + { + "start": "12496", + "size": 0.100007372, + "ndv": 1619 + }, + { + "start": "14114", + "size": 0.100007372, + "ndv": 1645 + }, + { + "start": "15758", + "end": "17384", + "size": 0.099933656, + "ndv": 1627 + } + ] +} +# +# Test record estimation is working properly. +# +select count(*) from t1; +count(*) +229376 +explain select * from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 229060 +set analyze_sample_percentage=100; +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 table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, +DECODE_HISTOGRAM(hist_type, histogram) +from mysql.column_stats; +table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) +t1 id 1 17384 0.0000 4.0000 14.0000 { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "1639", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "3277", + "size": 0.100001744, + "ndv": 1640 + }, + { + "start": "4916", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "6554", + "size": 0.100001744, + "ndv": 1640 + }, + { + "start": "9193", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "10831", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "12470", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "14108", + "size": 0.100001744, + "ndv": 1639 + }, + { + "start": "15746", + "end": "17384", + "size": 0.099984305, + "ndv": 1639 + } + ] +} +explain select * from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 229376 +drop table t0; +drop table t1; +set analyze_sample_percentage=@save_analyze_sample_percentage; +set histogram_size=@save_histogram_size; +set use_stat_tables=@save_use_stat_tables; +set @@global.histogram_size=@save_histogram_size; +drop table if exists t1; +set @save_histogram_type=@@histogram_type; +set @save_histogram_size=@@histogram_size; +call mtr.add_suppression("Failed to parse histogram for table .*"); +create table ten(a int primary key); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set histogram_size=100; +set histogram_type='double_prec_hb'; +create table t1_bin (a varchar(255)); +insert into t1_bin select concat('a-', a) from ten; +analyze table t1_bin persistent for all; +Table Op Msg_type Msg_text +test.t1_bin analyze status Engine-independent statistics collected +test.t1_bin analyze status OK +select hex(histogram) from mysql.column_stats where table_name='t1_bin'; +hex(histogram) +00000000000000000000711C711C711C711C711CE338E338E338E338E33855555555555555555555C671C671C671C671C671388E388E388E388E388EAAAAAAAAAAAAAAAAAAAA1BC71BC71BC71BC71BC78DE38DE38DE38DE38DE3FFFFFFFFFFFFFFFFFFFF +explain extended select * from t1_bin where a between 'a-3a' and 'zzzzzzzzz'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1_bin ALL NULL NULL NULL NULL 10 58.82 Using where +Warnings: +Note 1003 select `test`.`t1_bin`.`a` AS `a` from `test`.`t1_bin` where `test`.`t1_bin`.`a` between 'a-3a' and 'zzzzzzzzz' +analyze select * from t1_bin where a between 'a-3a' and 'zzzzzzzzz'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1_bin ALL NULL NULL NULL NULL 10 10.00 58.82 60.00 Using where +set histogram_type=json_hb; +create table t1_json (a varchar(255)); +insert into t1_json select concat('a-', a) from ten; +analyze table t1_json persistent for all; +Table Op Msg_type Msg_text +test.t1_json analyze status Engine-independent statistics collected +test.t1_json analyze status OK +select * from mysql.column_stats where table_name='t1_json'; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1_json a a-0 a-9 0.0000 3.0000 1.0000 10 JSON_HB { + "target_histogram_size": 100, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "a-0", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-1", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-2", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-3", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-4", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-5", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-6", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-7", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-8", + "size": 0.1, + "ndv": 1 + }, + { + "start": "a-9", + "end": "a-9", + "size": 0.1, + "ndv": 1 + } + ] +} +explain extended select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 60.00 Using where +Warnings: +Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` between 'a-3a' and 'zzzzzzzzz' +analyze select * from t1_json where a between 'a-3a' and 'zzzzzzzzz'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 60.00 60.00 Using where +explain extended select * from t1_json where a < 'b-1a'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 100.00 Using where +Warnings: +Note 1003 select `test`.`t1_json`.`a` AS `a` from `test`.`t1_json` where `test`.`t1_json`.`a` < 'b-1a' +analyze select * from t1_json where a > 'zzzzzzzzz'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 10.00 0.00 0.00 Using where +drop table ten; +UPDATE mysql.column_stats +SET histogram='["not-what-you-expect"]' WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: Root JSON element must be a JSON object at offset 1. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":"not-histogram"}' WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: histogram_hb must contain an array at offset 32. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":["not-a-bucket"]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: Expected an object in the buckets array at offset 32. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[{"no-expected-members":1}]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: "start" element not present at offset 42. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[{"start":{}}]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: String or number expected at offset 27. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[{"start":"aaa", "size":"not-an-integer"}]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: "ndv" element not present at offset 57. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[{"start":"aaa", "size":0.25}]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: "ndv" element not present at offset 45. +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[{"start":"aaa", "size":0.25, "ndv":1}]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +UPDATE mysql.column_stats +SET histogram='{"histogram_hb":[]}' +WHERE table_name='t1_json'; +FLUSH TABLES; +explain select * from t1_json limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1_json ALL NULL NULL NULL NULL 10 +Warnings: +Warning 4186 Failed to parse histogram for table test.t1_json: Histogram must have at least one bucket at offset 19. +create table t2 ( +city varchar(100) +); +set histogram_size=50; +insert into t2 select 'Moscow' from seq_1_to_99; +insert into t2 select 'Helsinki' from seq_1_to_2; +set histogram_type=json_hb; +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 +explain extended select * from t2 where city = 'Moscow'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 98.02 Using where +Warnings: +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` = 'Moscow' +analyze select * from t2 where city = 'Moscow'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 98.02 98.02 Using where +explain extended select * from t2 where city = 'Helsinki'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 1.98 Using where +Warnings: +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` = 'Helsinki' +analyze select * from t2 where city = 'helsinki'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 101.00 1.98 1.98 Using where +explain extended select * from t2 where city < 'Lagos'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 101 1.98 Using where +Warnings: +Note 1003 select `test`.`t2`.`city` AS `city` from `test`.`t2` where `test`.`t2`.`city` < 'Lagos' +drop table t1_bin; +drop table t1_json; +drop table t2; +DELETE FROM mysql.column_stats; +create schema world; +use world; +set histogram_type='JSON_HB'; +set histogram_size=50; +ANALYZE TABLE Country, City, CountryLanguage persistent for all; +SELECT column_name, min_value, max_value, hist_size, hist_type, histogram FROM mysql.column_stats; +column_name min_value max_value hist_size hist_type histogram +Code ABW ZWE NULL NULL NULL +Name Afghanistan Zimbabwe 48 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "Afghanistan", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Angola", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Armenia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Bahamas", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Belgium", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Bolivia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "British Indian Ocean Territory", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Cambodia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Central African Republic", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Cocos (Keeling) Islands", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Cook Islands", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Czech Republic", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Dominican Republic", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Equatorial Guinea", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Faroe Islands", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "French Polynesia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Germany", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Grenada", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Guinea-Bissau", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Honduras", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Indonesia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Italy", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Kenya", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Latvia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Liechtenstein", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Madagascar", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Malta", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Mayotte", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Mongolia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Namibia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "New Caledonia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Niue", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Oman", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Papua New Guinea", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Poland", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Russian Federation", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Saint Lucia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Sao Tome and Principe", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Singapore", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "South Africa", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Sudan", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Switzerland", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Thailand", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Tunisia", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Uganda", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "United States Minor Outlying Islands", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Vietnam", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "Yemen", + "end": "Zimbabwe", + "size": 0.016736402, + "ndv": 4 + } + ] +} +ID 1 4079 NULL NULL NULL +Country ABW ZWE 39 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "ABW", + "size": 0.020102966, + "ndv": 11 + }, + { + "start": "ATG", + "size": 0.020102966, + "ndv": 14 + }, + { + "start": "BLR", + "size": 0.006619269, + "ndv": 4 + }, + { + "start": "BRA", + "size": 0.061289532, + "ndv": 1 + }, + { + "start": "BRB", + "size": 0.020102966, + "ndv": 9 + }, + { + "start": "CHL", + "size": 0.002206423, + "ndv": 1 + }, + { + "start": "CHN", + "size": 0.0889924, + "ndv": 1 + }, + { + "start": "CIV", + "size": 0.020102966, + "ndv": 10 + }, + { + "start": "CUB", + "size": 0.020102966, + "ndv": 6 + }, + { + "start": "DEU", + "size": 0.020102966, + "ndv": 8 + }, + { + "start": "EGY", + "size": 0.020102966, + "ndv": 4 + }, + { + "start": "ESP", + "size": 0.020102966, + "ndv": 11 + }, + { + "start": "GBR", + "size": 0.020102966, + "ndv": 3 + }, + { + "start": "GIB", + "size": 0.020102966, + "ndv": 19 + }, + { + "start": "IDN", + "size": 0.012503064, + "ndv": 1 + }, + { + "start": "IND", + "size": 0.083598921, + "ndv": 1 + }, + { + "start": "IRL", + "size": 0.020102966, + "ndv": 3 + }, + { + "start": "IRQ", + "size": 0.020102966, + "ndv": 6 + }, + { + "start": "JOR", + "size": 2.451581e-4, + "ndv": 1 + }, + { + "start": "JPN", + "size": 0.060799215, + "ndv": 1 + }, + { + "start": "KAZ", + "size": 0.020102966, + "ndv": 7 + }, + { + "start": "KOR", + "size": 0.020102966, + "ndv": 16 + }, + { + "start": "MDA", + "size": 0.002451581, + "ndv": 3 + }, + { + "start": "MEX", + "size": 0.042412356, + "ndv": 1 + }, + { + "start": "MHL", + "size": 0.020102966, + "ndv": 20 + }, + { + "start": "NGA", + "size": 0.020102966, + "ndv": 4 + }, + { + "start": "NLD", + "size": 0.020102966, + "ndv": 7 + }, + { + "start": "PAK", + "size": 0.007354744, + "ndv": 4 + }, + { + "start": "PHL", + "size": 0.033341505, + "ndv": 1 + }, + { + "start": "PLW", + "size": 0.020102966, + "ndv": 8 + }, + { + "start": "PSE", + "size": 0.008580534, + "ndv": 5 + }, + { + "start": "RUS", + "size": 0.046334886, + "ndv": 1 + }, + { + "start": "RWA", + "size": 0.020102966, + "ndv": 18 + }, + { + "start": "SWE", + "size": 0.020102966, + "ndv": 16 + }, + { + "start": "TUR", + "size": 0.020102966, + "ndv": 4 + }, + { + "start": "TZA", + "size": 0.015199804, + "ndv": 4 + }, + { + "start": "USA", + "size": 0.067173327, + "ndv": 1 + }, + { + "start": "UZB", + "size": 0.020102966, + "ndv": 7 + }, + { + "start": "VNM", + "end": "ZWE", + "size": 0.018632018, + "ndv": 9 + } + ] +} +SurfaceArea 0.40 17075400.00 48 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0.40", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "16.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "49.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "96.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "181.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "242.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "314.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "373.00", + "size": 0.020920502, + "ndv": 4 + }, + { + "start": "455.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "618.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "726.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1102.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "2510.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "4033.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "8875.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "11295.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "17364.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "21041.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "26338.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "28896.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "36125.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "45227.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "51197.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "65301.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "75517.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "88946.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "102173.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "111369.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "120538.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "147181.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "185180.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "236800.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "245857.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "283561.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "323250.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "342000.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "438317.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "475442.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "551500.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "622984.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "756626.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "883749.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1098581.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1246700.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1648195.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "2166090.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "2780400.00", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "9572900.00", + "end": "17075400.00", + "size": 0.016736402, + "ndv": 4 + } + ] +} +Population 0 1277558000 48 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0", + "size": 0.029288703, + "ndv": 1 + }, + { + "start": "50", + "size": 0.020920502, + "ndv": 4 + }, + { + "start": "2000", + "size": 0.020920502, + "ndv": 4 + }, + { + "start": "7000", + "size": 0.020920502, + "ndv": 4 + }, + { + "start": "15000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "25000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "38000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "68000", + "size": 0.020920502, + "ndv": 4 + }, + { + "start": "78000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "103000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "154000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "214000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "279000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "380200", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "444000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "599000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "817000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1213000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1622000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "2124000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "2583000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "3101000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "3520000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "3850000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "4380000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "4699000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "5083000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "5496000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "6276000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "7430000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "8190900", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "9169000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "10097000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "10640000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "11234000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "12878000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "15942000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "18886000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "22244000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "23930000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "28351000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "33517000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "42321000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "57680000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "66591000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "82164700", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "146934000", + "size": 0.020920502, + "ndv": 5 + }, + { + "start": "1013662000", + "end": "1277558000", + "size": 0.008368201, + "ndv": 2 + } + ] +} +Capital 1 4074 47 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "54", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "65", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "144", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "179", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "194", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "537", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "553", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "586", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "652", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "764", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "905", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "919", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "928", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "939", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "1449", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "1530", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "1792", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "1864", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2256", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2317", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2413", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2437", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2447", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2460", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2484", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2511", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2695", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2726", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2754", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2821", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2885", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "2919", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3014", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3048", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3067", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3171", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3207", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3217", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3248", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3315", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3336", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3419", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3483", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3520", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "3580", + "size": 0.021551724, + "ndv": 5 + }, + { + "start": "4068", + "end": "4074", + "size": 0.00862069, + "ndv": 2 + } + ] +} +Name A Coruña (La Coruña) Ürgenc 50 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "A Coruña (La Coruña)", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Almería", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Araras", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Bakersfield", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Bayamo", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Bilaspur", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Bridgeport", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Cambridge", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Chatsworth", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Cizah", + "size": 0.020102966, + "ndv": 78 + }, + { + "start": "Da Nang", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Djougou", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Emeishan", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Freiburg im Breisgau", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Giugliano in Campania", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "Györ", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "Herat", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Hyesan", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Itabira", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Jiangyou", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "Kamjanets-Podilskyi", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Khouribga", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Koudougou", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Lahore", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Linköping", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Machilipatnam (Masulipatam)", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Marikina", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Miami Beach", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Moscow", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Nanded (Nander)", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Nizni Tagil", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Okazaki", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Pak Kret", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Petah Tiqwa", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Porto-Novo", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Qomsheh", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Rimini", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Salamanca", + "size": 0.020102966, + "ndv": 70 + }, + { + "start": "Sanaa", + "size": 0.020102966, + "ndv": 78 + }, + { + "start": "Secunderabad", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "Silay", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Subotica", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Tagum", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Tema", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Tongling", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Udine", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "Verona", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "Wichita Falls", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "Yibin", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "Zixing", + "end": "Ürgenc", + "size": 0.014954646, + "ndv": 61 + } + ] +} +Population 42 10500000 50 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "42", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "56601", + "size": 0.020102966, + "ndv": 64 + }, + { + "start": "90674", + "size": 0.020102966, + "ndv": 70 + }, + { + "start": "92700", + "size": 0.020102966, + "ndv": 76 + }, + { + "start": "94800", + "size": 0.020102966, + "ndv": 74 + }, + { + "start": "96984", + "size": 0.020102966, + "ndv": 75 + }, + { + "start": "99296", + "size": 0.020102966, + "ndv": 73 + }, + { + "start": "101144", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "103211", + "size": 0.020102966, + "ndv": 73 + }, + { + "start": "105700", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "107800", + "size": 0.020102966, + "ndv": 76 + }, + { + "start": "110048", + "size": 0.020102966, + "ndv": 76 + }, + { + "start": "113336", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "116485", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "119675", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "122700", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "125300", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "127898", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "131831", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "135621", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "139712", + "size": 0.020102966, + "ndv": 75 + }, + { + "start": "144282", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "149000", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "154976", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "161191", + "size": 0.020102966, + "ndv": 78 + }, + { + "start": "167795", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "174381", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "180650", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "187691", + "size": 0.020102966, + "ndv": 76 + }, + { + "start": "195400", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "203500", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "214901", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "224897", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "239810", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "253587", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "268013", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "285114", + "size": 0.020102966, + "ndv": 77 + }, + { + "start": "303346", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "325790", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "348845", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "374945", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "410000", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "445555", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "487148", + "size": 0.020102966, + "ndv": 79 + }, + { + "start": "559249", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "651154", + "size": 0.020102966, + "ndv": 82 + }, + { + "start": "791926", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "1040000", + "size": 0.020102966, + "ndv": 80 + }, + { + "start": "1398800", + "size": 0.020102966, + "ndv": 81 + }, + { + "start": "2641312", + "end": "10500000", + "size": 0.014954646, + "ndv": 61 + } + ] +} +Country ABW ZWE 50 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "ABW", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "ALB", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "ATG", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "AZE", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "BFA", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "BLR", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "BRN", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "CAN", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "CHN", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "CMR", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "COK", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "CXR", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "DJI", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "ERI", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "ETH", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "FSM", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "GHA", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "GNB", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "GUM", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "HUN", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "IND", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "IRQ", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "JOR", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "KEN", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "KWT", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "LCA", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "LVA", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "MDA", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "MLI", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "MNP", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "MRT", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "MYS", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "NFK", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "NLD", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "OMN", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "PHL", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "PRI", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "REU", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "RWA", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "SGP", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "SPM", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "SWZ", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "TGO", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "TON", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "TZA", + "size": 0.020325203, + "ndv": 2 + }, + { + "start": "UGA", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "USA", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "VNM", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "YUG", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "ZWE", + "end": "ZWE", + "size": 0.004065041, + "ndv": 1 + } + ] +} +Language Abhyasi [South]Mande 48 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "Abhyasi", + "size": 0.020325203, + "ndv": 12 + }, + { + "start": "Ami", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "Arabic", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "Armenian", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "Balochi", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Belorussian", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Bullom-sherbro", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "Chechen", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "Chinese", + "size": 0.020325203, + "ndv": 12 + }, + { + "start": "Creole English", + "size": 0.020325203, + "ndv": 2 + }, + { + "start": "Creole French", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Dorbet", + "size": 0.012195122, + "ndv": 8 + }, + { + "start": "English", + "size": 0.06097561, + "ndv": 1 + }, + { + "start": "Eskimo Languages", + "size": 0.020325203, + "ndv": 9 + }, + { + "start": "French", + "size": 0.020325203, + "ndv": 2 + }, + { + "start": "Friuli", + "size": 0.020325203, + "ndv": 9 + }, + { + "start": "Ganda", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "German", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "Guaymí", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "Hehet", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "Hungarian", + "size": 0.020325203, + "ndv": 10 + }, + { + "start": "Italian", + "size": 0.020325203, + "ndv": 10 + }, + { + "start": "Kanuri", + "size": 0.020325203, + "ndv": 10 + }, + { + "start": "Khoekhoe", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "Kotokoli", + "size": 0.020325203, + "ndv": 14 + }, + { + "start": "Lithuanian", + "size": 0.020325203, + "ndv": 16 + }, + { + "start": "Macedonian", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Malenasian Languages", + "size": 0.020325203, + "ndv": 12 + }, + { + "start": "Maranao", + "size": 0.020325203, + "ndv": 18 + }, + { + "start": "Miao", + "size": 0.020325203, + "ndv": 17 + }, + { + "start": "Muong", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "Norwegian", + "size": 0.020325203, + "ndv": 18 + }, + { + "start": "Paiwan", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Polish", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "Portuguese", + "size": 0.020325203, + "ndv": 9 + }, + { + "start": "Romanian", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "Russian", + "size": 0.020325203, + "ndv": 10 + }, + { + "start": "Saraiki", + "size": 0.020325203, + "ndv": 10 + }, + { + "start": "Sidamo", + "size": 0.020325203, + "ndv": 12 + }, + { + "start": "Soninke", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "Spanish", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "Sunda", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "Tamil", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "Tigre", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "Turkish", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "Ukrainian", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "Uzbek", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "Yap", + "end": "[South]Mande", + "size": 0.012195122, + "ndv": 9 + } + ] +} +Percentage 0.0 99.9 47 JSON_HB { + "target_histogram_size": 50, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0.0", + "size": 0.066056911, + "ndv": 1 + }, + { + "start": "0.1", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.2", + "size": 0.022357724, + "ndv": 1 + }, + { + "start": "0.3", + "size": 0.017276423, + "ndv": 1 + }, + { + "start": "0.4", + "size": 0.025406504, + "ndv": 1 + }, + { + "start": "0.5", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.6", + "size": 0.020325203, + "ndv": 1 + }, + { + "start": "0.7", + "size": 0.020325203, + "ndv": 2 + }, + { + "start": "0.8", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "1.0", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "1.3", + "size": 0.020325203, + "ndv": 2 + }, + { + "start": "1.4", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "1.6", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "1.8", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "2.1", + "size": 0.020325203, + "ndv": 3 + }, + { + "start": "2.3", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "2.6", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "2.9", + "size": 0.020325203, + "ndv": 4 + }, + { + "start": "3.2", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "3.6", + "size": 0.020325203, + "ndv": 5 + }, + { + "start": "4.1", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "4.6", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "5.1", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "5.7", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "6.2", + "size": 0.020325203, + "ndv": 8 + }, + { + "start": "6.9", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "7.6", + "size": 0.020325203, + "ndv": 6 + }, + { + "start": "8.2", + "size": 0.020325203, + "ndv": 7 + }, + { + "start": "8.9", + "size": 0.020325203, + "ndv": 9 + }, + { + "start": "9.7", + "size": 0.020325203, + "ndv": 11 + }, + { + "start": "11.0", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "12.4", + "size": 0.020325203, + "ndv": 14 + }, + { + "start": "14.1", + "size": 0.020325203, + "ndv": 13 + }, + { + "start": "16.5", + "size": 0.020325203, + "ndv": 17 + }, + { + "start": "19.7", + "size": 0.020325203, + "ndv": 14 + }, + { + "start": "23.3", + "size": 0.020325203, + "ndv": 16 + }, + { + "start": "31.7", + "size": 0.020325203, + "ndv": 16 + }, + { + "start": "37.5", + "size": 0.020325203, + "ndv": 19 + }, + { + "start": "47.4", + "size": 0.020325203, + "ndv": 18 + }, + { + "start": "55.1", + "size": 0.020325203, + "ndv": 19 + }, + { + "start": "66.7", + "size": 0.020325203, + "ndv": 18 + }, + { + "start": "78.1", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "86.2", + "size": 0.020325203, + "ndv": 18 + }, + { + "start": "90.7", + "size": 0.020325203, + "ndv": 15 + }, + { + "start": "95.1", + "size": 0.020325203, + "ndv": 14 + }, + { + "start": "97.6", + "size": 0.020325203, + "ndv": 14 + }, + { + "start": "99.9", + "end": "99.9", + "size": 0.015243902, + "ndv": 1 + } + ] +} +analyze select * from Country use index () where Code between 'BBC' and 'GGG'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 239.00 20.00 25.52 Using where +analyze select * from Country use index () where Code < 'BBC'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 239.00 4.00 7.11 Using where +set histogram_type=@save_histogram_type; +set histogram_size=@save_histogram_size; +DROP SCHEMA world; +use test; +create table t10 ( +a varchar(10) +); +# +# Histograms are not collected for empty tables: +# +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status Table is already up to date +select histogram +from mysql.column_stats where table_name='t10' and db_name=database(); +histogram +NULL +# +# Try with n_buckets > n_rows +# +insert into t10 values ('Berlin'),('Paris'),('Rome'); +set histogram_size=10, histogram_type='json_hb'; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +select histogram +from mysql.column_stats where table_name='t10' and db_name=database(); +histogram +{ + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "Berlin", + "size": 0.333333333, + "ndv": 1 + }, + { + "start": "Paris", + "size": 0.333333333, + "ndv": 1 + }, + { + "start": "Rome", + "end": "Rome", + "size": 0.333333333, + "ndv": 1 + } + ] +} +drop table t10; +# +# MDEV-26590: Stack smashing/buffer overflow in Histogram_json_hb::parse upon UPDATE on table with long VARCHAR +# +CREATE TABLE t1 (b INT, a VARCHAR(3176)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +SET histogram_type= JSON_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 * FROM t1; +b a +1 foo +2 bar +drop table t1; +# +# MDEV-26589: Assertion failure upon DECODE_HISTOGRAM with NULLs in first column +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (NULL,1), (NULL,2); +SET histogram_type = JSON_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 DECODE_HISTOGRAM(hist_type, histogram) from mysql.column_stats; +DECODE_HISTOGRAM(hist_type, histogram) +NULL +{ + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.5, + "ndv": 1 + }, + { + "start": "2", + "end": "2", + "size": 0.5, + "ndv": 1 + } + ] +} +drop table t1; +# +# MDEV-26711: Values in JSON histograms are not properly quoted +# +create table t1 (a varchar(32)); +insert into t1 values ('this is "quoted" text'); +set histogram_type= JSON_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 * from t1 where a = 'foo'; +a +drop table t1; +# +# MDEV-26724 Endless loop in json_escape_to_string upon ... empty string +# +CREATE TABLE t1 (f VARCHAR(8)); +INSERT INTO t1 VALUES ('a'),(''),('b'); +SET histogram_type=JSON_HB; +ANALYZE TABLE t PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t analyze Error Table 'test.t' doesn't exist +test.t analyze status Operation failed +select * from t1; +f +a + +b +drop table t1; +create table t1 (a char(1)) character set latin1; +insert into t1 values (0xD1); +select hex(a) from t1; +hex(a) +D1 +set histogram_type='json_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 decode_histogram(hist_type, histogram) +from mysql.column_stats +where db_name=database() and table_name='t1'; +decode_histogram(hist_type, histogram) +{ + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "Ñ", + "end": "Ñ", + "size": 1, + "ndv": 1 + } + ] +} +select * from t1; +a +Ñ +drop table t1; +# +# Another testcase: use a character that cannot be represented in utf8: +# Also, now it's testcase for: +# MDEV-26764: JSON_HB Histograms: handle BINARY and unassigned characters +# +create table t1 ( a varchar(100) character set cp1251); +insert into t1 values ( _cp1251 x'88'),( _cp1251 x'88'), ( _cp1251 x'88'); +insert into t1 values ( _cp1251 x'98'),( _cp1251 x'98'); +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 hist_type, histogram +from mysql.column_stats +where db_name=database() and table_name='t1'; +hist_type histogram +JSON_HB { + "target_histogram_size": 10, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "€", + "size": 0.6, + "ndv": 1 + }, + { + "start_hex": "98", + "end_hex": "98", + "size": 0.4, + "ndv": 1 + } + ] +} +analyze select * from t1 where a=_cp1251 x'88'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 5.00 60.00 60.00 Using where +drop table t1; +# +# ASAN use-after-poison my_strnxfrm_simple_internal / Histogram_json_hb::range_selectivity ... +# (Just the testcase) +# +CREATE TABLE t1 (f CHAR(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +SET histogram_type = JSON_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 * FROM t1 WHERE f > 'qux'; +f +DROP TABLE t1; +# +# MDEV-26737: Outdated VARIABLE_COMMENT for HISTOGRAM_TYPE in I_S.SYSTEM_VARIABLES +# +select variable_comment from information_schema.system_variables where VARIABLE_NAME='HISTOGRAM_TYPE'; +variable_comment +Specifies type of the histograms created by ANALYZE. Possible values are: SINGLE_PREC_HB - single precision height-balanced, DOUBLE_PREC_HB - double precision height-balanced, JSON_HB - height-balanced, stored as JSON. +# +# MDEV-26709: JSON histogram may contain bucketS than histogram_size allows +# +create table t1 (a int); +insert into t1 values (1),(3),(5),(7); +insert into t1 select 2 from seq_1_to_25; +insert into t1 select 4 from seq_1_to_25; +insert into t1 select 6 from seq_1_to_25; +set histogram_size=4, histogram_type=JSON_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 histogram from mysql.column_stats where table_name = 't1'; +histogram +{ + "target_histogram_size": 4, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "1", + "size": 0.253164557, + "ndv": 2 + }, + { + "start": "2", + "size": 0.253164557, + "ndv": 3 + }, + { + "start": "4", + "size": 0.253164557, + "ndv": 3 + }, + { + "start": "6", + "end": "7", + "size": 0.240506329, + "ndv": 2 + } + ] +} +drop table t1; +# +# MDEV-26750: Estimation for filtered rows is far off with JSON_HB histogram +# +create table t1 (c char(8)); +insert into t1 values ('1x'); +insert into t1 values ('1x'); +insert into t1 values ('1xx'); +insert into t1 values ('0xx'); +insert into t1 select * from t1; +insert into t1 select * from t1; +set histogram_type= JSON_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 +analyze +select c from t1 where c > '1'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 16 16.00 75.00 75.00 Using where +drop table t1; +# +# MDEV-26849: JSON Histograms: point selectivity estimates are off for non-existent values +# +create table t0(a int); +insert into t0 (a) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select 100*A.a from t0 A, t0 B, t0 C; +select a, count(*) from t1 group by a order by a; +a count(*) +0 100 +100 100 +200 100 +300 100 +400 100 +500 100 +600 100 +700 100 +800 100 +900 100 +set histogram_type=json_hb, histogram_size=default; +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 * from mysql.column_stats where table_name='t1'; +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 900 0.0000 4.0000 100.0000 10 JSON_HB { + "target_histogram_size": 254, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start": "0", + "size": 0.1, + "ndv": 1 + }, + { + "start": "100", + "size": 0.1, + "ndv": 1 + }, + { + "start": "200", + "size": 0.1, + "ndv": 1 + }, + { + "start": "300", + "size": 0.1, + "ndv": 1 + }, + { + "start": "400", + "size": 0.1, + "ndv": 1 + }, + { + "start": "500", + "size": 0.1, + "ndv": 1 + }, + { + "start": "600", + "size": 0.1, + "ndv": 1 + }, + { + "start": "700", + "size": 0.1, + "ndv": 1 + }, + { + "start": "800", + "size": 0.1, + "ndv": 1 + }, + { + "start": "900", + "end": "900", + "size": 0.1, + "ndv": 1 + } + ] +} +analyze select * from t1 where a=0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 10.00 10.00 Using where +analyze select * from t1 where a=50; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 0.10 0.00 Using where +analyze select * from t1 where a=70; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 0.10 0.00 Using where +analyze select * from t1 where a=100; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 10.00 10.00 Using where +analyze select * from t1 where a=150; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 0.10 0.00 Using where +analyze select * from t1 where a=200; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 10.00 10.00 Using where +drop table t0,t1; +# +# MDEV-26892: JSON histograms become invalid with a specific (corrupt) value in t +# +create table t1 (a varchar(32)) DEFAULT CHARSET=cp1257; +set histogram_type= JSON_HB, histogram_size= 1; +insert into t1 values ('foo'),(unhex('9C')); +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 * from t1; +a +foo +? +drop table t1; +# +# MDEV-26911: Unexpected ER_DUP_KEY, ASAN errors, double free detected in tcache with JSON_HB histogram +# +SET histogram_type= JSON_HB; +CREATE TABLE t1 (pk INT AUTO_INCREMENT, f VARCHAR(8), PRIMARY KEY (pk)); +INSERT INTO t1 (f) VALUES ('foo'); +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 +ALTER TABLE t1 MODIFY f TEXT, ORDER BY pk; +INSERT INTO t1 (f) VALUES ('bar'); +DROP TABLE t1; +# +# MDEV-26886: Estimation for filtered rows less precise with JSON histogram +# +create table t1 (a tinyint) as select if(seq%3,seq,0) as a from seq_1_to_100; +select count(*) from t1 where a <= 0; +count(*) +33 +set histogram_type = JSON_HB, histogram_size=default; +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 +analyze select * from t1 where a <= 0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 33.00 33.00 Using where +analyze select * from t1 where a < 0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 1.00 0.00 Using where +analyze select * from t1 where a > 0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 67.00 67.00 Using where +analyze select * from t1 where a >= 0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 100.00 100.00 Using where +drop table t1; +# +# More test coverage +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 (a int); +insert into t2 select 1 from t1; +insert into t2 select (a+1)*10 from t0; +insert into t2 values (0); +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 +analyze select * from t2 where a < 1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1011 1011.00 0.10 0.10 Using where +analyze select * from t2 where a =100; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1011 1011.00 0.10 0.10 Using where +drop table t0,t1,t2; +# +# MDEV-27230: Estimation for filtered rows less precise ... +# +create table t1 (a char(1)); +insert into t1 select chr(seq%26+97) from seq_1_to_50; +insert into t1 select ':' from t1; +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 +analyze select COUNT(*) FROM t1 WHERE a <> 'a'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 99.00 99.00 Using where +analyze select COUNT(*) FROM t1 WHERE a < 'a'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 50.00 50.00 Using where +drop table t1; +# +# MDEV-27229: Estimation for filtered rows less precise ... #5 +# +create table t1 (id int, a varchar(8)); +insert into t1 select seq, 'bar' from seq_1_to_100; +insert into t1 select id, 'qux' from t1; +set histogram_type=JSON_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 +analyze select COUNT(*) FROM t1 WHERE a > 'foo'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 50.00 50.00 Using where +analyze select COUNT(*) FROM t1 WHERE a > 'aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 100.00 100.00 Using where +analyze select COUNT(*) FROM t1 WHERE a >='aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 100.00 100.00 Using where +analyze select COUNT(*) FROM t1 WHERE a > 'bar'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 50.00 50.00 Using where +analyze select COUNT(*) FROM t1 WHERE a >='bar'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 100.00 100.00 Using where +analyze select COUNT(*) FROM t1 WHERE a < 'aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where +analyze select COUNT(*) FROM t1 WHERE a <='aaa'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where +analyze select COUNT(*) FROM t1 WHERE a < 'bar'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 0.50 0.00 Using where +analyze select COUNT(*) FROM t1 WHERE a <='bar'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 200 200.00 50.00 50.00 Using where +drop table t1; +# +# MDEV-27243: Estimation for filtered rows less precise ... #7 +# (Testcase only) +CREATE TABLE t1 (f TIME); +INSERT INTO t1 SELECT IF(seq%2,'00:00:00',SEC_TO_TIME(seq+7200)) FROM seq_1_to_1000; +SET histogram_type= JSON_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 +ANALYZE SELECT * FROM t1 WHERE f > '00:01:00'; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 1000.00 50.00 50.00 Using where +drop table t1; +# +# MDEV-26901: Estimation for filtered rows less precise ... #4 +# +create table t1 (f int); +insert into t1 values +(7),(5),(0),(5),(112),(9),(9),(7),(5),(9), +(1),(7),(0),(6),(6),(2),(1),(6),(169),(7); +select f from t1 where f in (77, 1, 144, 73, 14, 12); +f +1 +1 +set histogram_type= JSON_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 +analyze select f from t1 where f in (77, 1, 144, 73, 14, 12); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 20 20.00 10.00 10.00 Using where +drop table t1; +# +# Test that histograms over BIT fields use hex +# +create table t1 (a BIT(64)); +insert into t1 values +(x'01'),(x'10'),(x'BE562B1A99001918'); +set histogram_type= JSON_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 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '13715197108439488792' +select histogram +from mysql.column_stats where table_name='t1' and db_name=database(); +histogram +{ + "target_histogram_size": 254, + "collected_at": "REPLACED", + "collected_by": "REPLACED", + "histogram_hb": [ + { + "start_hex": "0000000000000001", + "size": 0.333333333, + "ndv": 1 + }, + { + "start_hex": "0000000000000010", + "size": 0.333333333, + "ndv": 1 + }, + { + "start_hex": "BE562B1A99001918", + "end_hex": "BE562B1A99001918", + "size": 0.333333333, + "ndv": 1 + } + ] +} +drop table t1; +# +# MDEV-28882: Assertion `tmp >= 0' failed in best_access_path +# +CREATE TABLE t1 (a varchar(1)); +INSERT INTO t1 VALUES ('o'),('s'),('j'),('s'),('y'),('s'),('l'), +('q'),('x'),('m'),('t'),('d'),('v'),('j'),('p'),('t'),('b'),('q'); +set histogram_type=json_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 +# filtered must not be negative: +explain format=json select * from t1 where a > 'y'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 18, + "filtered": 5.555555344, + "attached_condition": "t1.a > 'y'" + } + } + ] + } +} +drop table t1; |