1drop table if exists t1,t2; 2set @save_use_stat_tables=@@use_stat_tables; 3set @save_histogram_size=@@global.histogram_size; 4set @@global.histogram_size=0,@@local.histogram_size=0; 5set @save_hist_type=@@histogram_type; 6set histogram_type='single_prec_hb'; 7DELETE FROM mysql.table_stats; 8DELETE FROM mysql.column_stats; 9DELETE FROM mysql.index_stats; 10set use_stat_tables='preferably'; 11CREATE TABLE t1 ( 12a int NOT NULL PRIMARY KEY, 13b varchar(32), 14c char(16), 15d date, 16e double, 17f bit(3), 18INDEX idx1 (b, e), 19INDEX idx2 (c, d), 20INDEX idx3 (d), 21INDEX idx4 (e, b, d) 22) ENGINE= MYISAM; 23INSERT INTO t1 VALUES 24(0, NULL, NULL, NULL, NULL, NULL), 25(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'), 26(17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'), 27(1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'), 28(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'), 29(23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'), 30(8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'), 31(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'), 32(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'), 33(10, NULL, 'aaaa', NULL, 0.01, b'010'), 34(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'), 35(15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'), 36(30, NULL, 'bbbbbb', NULL, NULL, b'100'), 37(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL), 38(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'), 39(9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL), 40(29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'), 41(3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'), 42(39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'), 43(14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'), 44(40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL), 45(44, NULL, 'aaaa', '1989-03-12', NULL, b'010'), 46(19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'), 47(21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'), 48(45, NULL, NULL, '1989-03-12', NULL, b'011'), 49(2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'), 50(35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'), 51(4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'), 52(47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'), 53(42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'), 54(32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'), 55(49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL), 56(43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'), 57(37, 'yyy', NULL, '1989-03-12', 0.05, b'011'), 58(41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL), 59(34, 'yyy', NULL, NULL, NULL, NULL), 60(33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'), 61(24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'), 62(11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL), 63(25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101'); 64ANALYZE TABLE t1; 65Table Op Msg_type Msg_text 66test.t1 analyze status Engine-independent statistics collected 67test.t1 analyze status OK 68SELECT * FROM mysql.table_stats; 69db_name table_name cardinality 70test t1 40 71SELECT * FROM mysql.column_stats; 72db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 73test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 74test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 75test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 76test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 77test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 78test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 79SELECT * FROM mysql.index_stats; 80db_name table_name index_name prefix_arity avg_frequency 81test t1 PRIMARY 1 1.0000 82test t1 idx1 1 6.4000 83test t1 idx1 2 1.6875 84test t1 idx2 1 7.0000 85test t1 idx2 2 2.3846 86test t1 idx3 1 8.5000 87test t1 idx4 1 6.2000 88test t1 idx4 2 1.6875 89test t1 idx4 3 1.1304 90SELECT COUNT(*) FROM t1; 91COUNT(*) 9240 93SELECT * FROM mysql.column_stats 94WHERE db_name='test' AND table_name='t1' AND column_name='a'; 95db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 96test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 97SELECT MIN(t1.a), MAX(t1.a), 98(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / 99(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)", 100(SELECT COUNT(t1.a) FROM t1) / 101(SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)" 102FROM t1; 103MIN(t1.a) MAX(t1.a) NULLS_RATIO(t1.a) AVG_FREQUENCY(t1.a) 1040 49 0.2000 1.0000 105SELECT * FROM mysql.column_stats 106WHERE db_name='test' AND table_name='t1' AND column_name='b'; 107db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 108test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 109SELECT MIN(t1.b), MAX(t1.b), 110(SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / 111(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)", 112(SELECT COUNT(t1.b) FROM t1) / 113(SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)" 114FROM t1; 115MIN(t1.b) MAX(t1.b) NULLS_RATIO(t1.b) AVG_FREQUENCY(t1.b) 116vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 117SELECT * FROM mysql.column_stats 118WHERE db_name='test' AND table_name='t1' AND column_name='c'; 119db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 120test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 121SELECT MIN(t1.c), MAX(t1.c), 122(SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) / 123(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)", 124(SELECT COUNT(t1.c) FROM t1) / 125(SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)" 126FROM t1; 127MIN(t1.c) MAX(t1.c) NULLS_RATIO(t1.c) AVG_FREQUENCY(t1.c) 128aaaa dddddddd 0.1250 7.0000 129SELECT * FROM mysql.column_stats 130WHERE db_name='test' AND table_name='t1' AND column_name='d'; 131db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 132test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 133SELECT MIN(t1.d), MAX(t1.d), 134(SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) / 135(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)", 136(SELECT COUNT(t1.d) FROM t1) / 137(SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)" 138FROM t1; 139MIN(t1.d) MAX(t1.d) NULLS_RATIO(t1.d) AVG_FREQUENCY(t1.d) 1401989-03-12 1999-07-23 0.1500 8.5000 141SELECT * FROM mysql.column_stats 142WHERE db_name='test' AND table_name='t1' AND column_name='e'; 143db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 144test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 145SELECT MIN(t1.e), MAX(t1.e), 146(SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) / 147(SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)", 148(SELECT COUNT(t1.e) FROM t1) / 149(SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)" 150FROM t1; 151MIN(t1.e) MAX(t1.e) NULLS_RATIO(t1.e) AVG_FREQUENCY(t1.e) 1520.01 0.112 0.2250 6.2000 153SELECT * FROM mysql.index_stats 154WHERE db_name='test' AND table_name='t1' AND index_name='idx1'; 155db_name table_name index_name prefix_arity avg_frequency 156test t1 idx1 1 6.4000 157test t1 idx1 2 1.6875 158SELECT 159(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) / 160(SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL) 161AS 'ARITY 1', 162(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) / 163(SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1 164WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) 165AS 'ARITY 2'; 166ARITY 1 ARITY 2 1676.4000 1.6875 168SELECT * FROM mysql.index_stats 169WHERE db_name='test' AND table_name='t1' AND index_name='idx2'; 170db_name table_name index_name prefix_arity avg_frequency 171test t1 idx2 1 7.0000 172test t1 idx2 2 2.3846 173SELECT 174(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) / 175(SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) 176AS 'ARITY 1', 177(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) / 178(SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1 179WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) 180AS 'ARITY 2'; 181ARITY 1 ARITY 2 1827.0000 2.3846 183SELECT * FROM mysql.index_stats 184WHERE db_name='test' AND table_name='t1' AND index_name='idx3'; 185db_name table_name index_name prefix_arity avg_frequency 186test t1 idx3 1 8.5000 187SELECT 188(SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) / 189(SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL) 190AS 'ARITY 1'; 191ARITY 1 1928.5000 193SELECT * FROM mysql.index_stats 194WHERE db_name='test' AND table_name='t1' AND index_name='idx4'; 195db_name table_name index_name prefix_arity avg_frequency 196test t1 idx4 1 6.2000 197test t1 idx4 2 1.6875 198test t1 idx4 3 1.1304 199SELECT 200(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) / 201(SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL) 202AS 'ARITY 1', 203(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) / 204(SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1 205WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) 206AS 'ARITY 2', 207(SELECT COUNT(*) FROM t1 208WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) / 209(SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1 210WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) 211AS 'ARITY 3'; 212ARITY 1 ARITY 2 ARITY 3 2136.2000 1.6875 1.1304 214DELETE FROM mysql.column_stats; 215set histogram_size=4; 216ANALYZE TABLE t1; 217Table Op Msg_type Msg_text 218test.t1 analyze status Engine-independent statistics collected 219test.t1 analyze status Table is already up to date 220SELECT db_name, table_name, column_name, 221min_value, max_value, 222nulls_ratio, avg_frequency, 223hist_size, hist_type, HEX(histogram) 224FROM mysql.column_stats; 225db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 226test t1 a 0 49 0.0000 1.0000 4 SINGLE_PREC_HB 2E62A1D0 227test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 SINGLE_PREC_HB 003FBFFF 228test t1 c aaaa dddddddd 0.1250 7.0000 4 SINGLE_PREC_HB 0055AAFF 229test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 SINGLE_PREC_HB 001919FF 230test t1 e 0.01 0.112 0.2250 6.2000 4 SINGLE_PREC_HB 000564E1 231test t1 f 1 5 0.2000 6.4000 4 SINGLE_PREC_HB 3F7FBFBF 232DELETE FROM mysql.column_stats; 233set histogram_size=8; 234set histogram_type='DOUBLE_PREC_HB'; 235ANALYZE TABLE t1; 236Table Op Msg_type Msg_text 237test.t1 analyze status Engine-independent statistics collected 238test.t1 analyze status Table is already up to date 239SELECT db_name, table_name, column_name, 240min_value, max_value, 241nulls_ratio, avg_frequency, 242hist_size, hist_type, HEX(histogram) 243FROM mysql.column_stats; 244db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 245test t1 a 0 49 0.0000 1.0000 8 DOUBLE_PREC_HB 052F4363F4A1F9D0 246test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 8 DOUBLE_PREC_HB 0000FF3FFFBFFFFF 247test t1 c aaaa dddddddd 0.1250 7.0000 8 DOUBLE_PREC_HB 00005555AAAAFFFF 248test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 8 DOUBLE_PREC_HB 0000031A031AFFFF 249test t1 e 0.01 0.112 0.2250 6.2000 8 DOUBLE_PREC_HB 000005056464E1E1 250test t1 f 1 5 0.2000 6.4000 8 DOUBLE_PREC_HB FF3FFF7FFFBFFFBF 251DELETE FROM mysql.column_stats; 252set histogram_size= 0; 253set histogram_type='single_prec_hb'; 254ANALYZE TABLE t1; 255Table Op Msg_type Msg_text 256test.t1 analyze status Engine-independent statistics collected 257test.t1 analyze status Table is already up to date 258CREATE TABLE t3 ( 259a int NOT NULL PRIMARY KEY, 260b varchar(32), 261c char(16), 262INDEX idx (c) 263) ENGINE=MYISAM; 264INSERT INTO t3 VALUES 265(0, NULL, NULL), 266(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd'), 267(17, 'vvvvvvvvvvvvv', 'aaaa'), 268(1, 'vvvvvvvvvvvvv', NULL), 269(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'), 270(23, 'vvvvvvvvvvvvv', 'dddddddd'), 271(8, 'vvvvvvvvvvvvv', 'aaaa'), 272(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa'), 273(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa'), 274(10, NULL, 'aaaa'), 275(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd'), 276(15, 'vvvvvvvvvvvvv', 'ccccccccc'), 277(30, NULL, 'bbbbbb'), 278(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb'), 279(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc'), 280(9, 'yyy', 'bbbbbb'), 281(29, 'vvvvvvvvvvvvv', 'dddddddd'); 282ANALYZE TABLE t3; 283Table Op Msg_type Msg_text 284test.t3 analyze status Engine-independent statistics collected 285test.t3 analyze status OK 286SELECT * FROM mysql.table_stats; 287db_name table_name cardinality 288test t1 40 289test t3 17 290SELECT * FROM mysql.column_stats; 291db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 292test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 293test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 294test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 295test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 296test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 297test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 298test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL 299test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL 300test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL 301SELECT * FROM mysql.index_stats; 302db_name table_name index_name prefix_arity avg_frequency 303test t1 PRIMARY 1 1.0000 304test t1 idx1 1 6.4000 305test t1 idx1 2 1.6875 306test t1 idx2 1 7.0000 307test t1 idx2 2 2.3846 308test t1 idx3 1 8.5000 309test t1 idx4 1 6.2000 310test t1 idx4 2 1.6875 311test t1 idx4 3 1.1304 312test t3 PRIMARY 1 1.0000 313test t3 idx 1 3.7500 314ALTER TABLE t1 RENAME TO s1; 315SELECT * FROM mysql.table_stats; 316db_name table_name cardinality 317test s1 40 318test t3 17 319SELECT * FROM mysql.column_stats; 320db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 321test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 322test s1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 323test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 324test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 325test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 326test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 327test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL 328test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL 329test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL 330SELECT * FROM mysql.index_stats; 331db_name table_name index_name prefix_arity avg_frequency 332test s1 PRIMARY 1 1.0000 333test s1 idx1 1 6.4000 334test s1 idx1 2 1.6875 335test s1 idx2 1 7.0000 336test s1 idx2 2 2.3846 337test s1 idx3 1 8.5000 338test s1 idx4 1 6.2000 339test s1 idx4 2 1.6875 340test s1 idx4 3 1.1304 341test t3 PRIMARY 1 1.0000 342test t3 idx 1 3.7500 343RENAME TABLE s1 TO t1; 344SELECT * FROM mysql.table_stats; 345db_name table_name cardinality 346test t1 40 347test t3 17 348SELECT * FROM mysql.column_stats; 349db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 350test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 351test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 352test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 353test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 354test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 355test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 356test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL NULL 357test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL NULL 358test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL NULL 359SELECT * FROM mysql.index_stats; 360db_name table_name index_name prefix_arity avg_frequency 361test t1 PRIMARY 1 1.0000 362test t1 idx1 1 6.4000 363test t1 idx1 2 1.6875 364test t1 idx2 1 7.0000 365test t1 idx2 2 2.3846 366test t1 idx3 1 8.5000 367test t1 idx4 1 6.2000 368test t1 idx4 2 1.6875 369test t1 idx4 3 1.1304 370test t3 PRIMARY 1 1.0000 371test t3 idx 1 3.7500 372DROP TABLE t3; 373SELECT * FROM mysql.table_stats; 374db_name table_name cardinality 375test t1 40 376SELECT * FROM mysql.column_stats; 377db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 378test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 379test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 380test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 381test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 382test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 383test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 384SELECT * FROM mysql.index_stats; 385db_name table_name index_name prefix_arity avg_frequency 386test t1 PRIMARY 1 1.0000 387test t1 idx1 1 6.4000 388test t1 idx1 2 1.6875 389test t1 idx2 1 7.0000 390test t1 idx2 2 2.3846 391test t1 idx3 1 8.5000 392test t1 idx4 1 6.2000 393test t1 idx4 2 1.6875 394test t1 idx4 3 1.1304 395CREATE TEMPORARY TABLE t0 ( 396a int NOT NULL PRIMARY KEY, 397b varchar(32) 398); 399INSERT INTO t0 SELECT a,b FROM t1; 400ALTER TABLE t1 CHANGE COLUMN b x varchar(32), 401CHANGE COLUMN e y double; 402SHOW CREATE TABLE t1; 403Table Create Table 404t1 CREATE TABLE `t1` ( 405 `a` int(11) NOT NULL, 406 `x` varchar(32) DEFAULT NULL, 407 `c` char(16) DEFAULT NULL, 408 `d` date DEFAULT NULL, 409 `y` double DEFAULT NULL, 410 `f` bit(3) DEFAULT NULL, 411 PRIMARY KEY (`a`), 412 KEY `idx1` (`x`,`y`), 413 KEY `idx2` (`c`,`d`), 414 KEY `idx3` (`d`), 415 KEY `idx4` (`y`,`x`,`d`) 416) ENGINE=MyISAM DEFAULT CHARSET=latin1 417SELECT * FROM mysql.column_stats; 418db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 419test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 420test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 421test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 422test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 423test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 424test t1 y 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 425ALTER TABLE t1 CHANGE COLUMN x b varchar(32), 426CHANGE COLUMN y e double; 427SHOW CREATE TABLE t1; 428Table Create Table 429t1 CREATE TABLE `t1` ( 430 `a` int(11) NOT NULL, 431 `b` varchar(32) DEFAULT NULL, 432 `c` char(16) DEFAULT NULL, 433 `d` date DEFAULT NULL, 434 `e` double DEFAULT NULL, 435 `f` bit(3) DEFAULT NULL, 436 PRIMARY KEY (`a`), 437 KEY `idx1` (`b`,`e`), 438 KEY `idx2` (`c`,`d`), 439 KEY `idx3` (`d`), 440 KEY `idx4` (`e`,`b`,`d`) 441) ENGINE=MyISAM DEFAULT CHARSET=latin1 442SELECT * FROM mysql.column_stats; 443db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 444test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 445test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 446test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 447test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 448test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 449test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 450ALTER TABLE t1 RENAME TO s1, CHANGE COLUMN b x varchar(32); 451SHOW CREATE TABLE s1; 452Table Create Table 453s1 CREATE TABLE `s1` ( 454 `a` int(11) NOT NULL, 455 `x` varchar(32) DEFAULT NULL, 456 `c` char(16) DEFAULT NULL, 457 `d` date DEFAULT NULL, 458 `e` double DEFAULT NULL, 459 `f` bit(3) DEFAULT NULL, 460 PRIMARY KEY (`a`), 461 KEY `idx1` (`x`,`e`), 462 KEY `idx2` (`c`,`d`), 463 KEY `idx3` (`d`), 464 KEY `idx4` (`e`,`x`,`d`) 465) ENGINE=MyISAM DEFAULT CHARSET=latin1 466SELECT * FROM mysql.table_stats; 467db_name table_name cardinality 468test s1 40 469SELECT * FROM mysql.column_stats; 470db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 471test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 472test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 473test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 474test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 475test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 476test s1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 477SELECT * FROM mysql.index_stats; 478db_name table_name index_name prefix_arity avg_frequency 479test s1 PRIMARY 1 1.0000 480test s1 idx1 1 6.4000 481test s1 idx1 2 1.6875 482test s1 idx2 1 7.0000 483test s1 idx2 2 2.3846 484test s1 idx3 1 8.5000 485test s1 idx4 1 6.2000 486test s1 idx4 2 1.6875 487test s1 idx4 3 1.1304 488ALTER TABLE s1 RENAME TO t1, CHANGE COLUMN x b varchar(32); 489SHOW CREATE TABLE t1; 490Table Create Table 491t1 CREATE TABLE `t1` ( 492 `a` int(11) NOT NULL, 493 `b` varchar(32) DEFAULT NULL, 494 `c` char(16) DEFAULT NULL, 495 `d` date DEFAULT NULL, 496 `e` double DEFAULT NULL, 497 `f` bit(3) DEFAULT NULL, 498 PRIMARY KEY (`a`), 499 KEY `idx1` (`b`,`e`), 500 KEY `idx2` (`c`,`d`), 501 KEY `idx3` (`d`), 502 KEY `idx4` (`e`,`b`,`d`) 503) ENGINE=MyISAM DEFAULT CHARSET=latin1 504SELECT * FROM mysql.table_stats; 505db_name table_name cardinality 506test t1 40 507SELECT * FROM mysql.column_stats; 508db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 509test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 510test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 511test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 512test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 513test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 514test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 515SELECT * FROM mysql.index_stats; 516db_name table_name index_name prefix_arity avg_frequency 517test t1 PRIMARY 1 1.0000 518test t1 idx1 1 6.4000 519test t1 idx1 2 1.6875 520test t1 idx2 1 7.0000 521test t1 idx2 2 2.3846 522test t1 idx3 1 8.5000 523test t1 idx4 1 6.2000 524test t1 idx4 2 1.6875 525test t1 idx4 3 1.1304 526ALTER TABLE t1 CHANGE COLUMN b x varchar(30); 527SHOW CREATE TABLE t1; 528Table Create Table 529t1 CREATE TABLE `t1` ( 530 `a` int(11) NOT NULL, 531 `x` varchar(30) DEFAULT NULL, 532 `c` char(16) DEFAULT NULL, 533 `d` date DEFAULT NULL, 534 `e` double DEFAULT NULL, 535 `f` bit(3) DEFAULT NULL, 536 PRIMARY KEY (`a`), 537 KEY `idx1` (`x`,`e`), 538 KEY `idx2` (`c`,`d`), 539 KEY `idx3` (`d`), 540 KEY `idx4` (`e`,`x`,`d`) 541) ENGINE=MyISAM DEFAULT CHARSET=latin1 542SELECT * FROM mysql.column_stats; 543db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 544test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 545test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 546test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 547test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 548test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 549SELECT * FROM mysql.index_stats; 550db_name table_name index_name prefix_arity avg_frequency 551test t1 PRIMARY 1 1.0000 552test t1 idx2 1 7.0000 553test t1 idx2 2 2.3846 554test t1 idx3 1 8.5000 555ALTER TABLE t1 CHANGE COLUMN x b varchar(32); 556SHOW CREATE TABLE t1; 557Table Create Table 558t1 CREATE TABLE `t1` ( 559 `a` int(11) NOT NULL, 560 `b` varchar(32) DEFAULT NULL, 561 `c` char(16) DEFAULT NULL, 562 `d` date DEFAULT NULL, 563 `e` double DEFAULT NULL, 564 `f` bit(3) DEFAULT NULL, 565 PRIMARY KEY (`a`), 566 KEY `idx1` (`b`,`e`), 567 KEY `idx2` (`c`,`d`), 568 KEY `idx3` (`d`), 569 KEY `idx4` (`e`,`b`,`d`) 570) ENGINE=MyISAM DEFAULT CHARSET=latin1 571SELECT * FROM mysql.column_stats; 572db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 573test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 574test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 575test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 576test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 577test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 578SELECT * FROM mysql.index_stats; 579db_name table_name index_name prefix_arity avg_frequency 580test t1 PRIMARY 1 1.0000 581test t1 idx2 1 7.0000 582test t1 idx2 2 2.3846 583test t1 idx3 1 8.5000 584ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx4); 585Table Op Msg_type Msg_text 586test.t1 analyze status Engine-independent statistics collected 587test.t1 analyze status OK 588SELECT * FROM mysql.column_stats; 589db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 590test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 591test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 592test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 593test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 594test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 595test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 596SELECT * FROM mysql.index_stats; 597db_name table_name index_name prefix_arity avg_frequency 598test t1 PRIMARY 1 1.0000 599test t1 idx1 1 6.4000 600test t1 idx1 2 1.6875 601test t1 idx2 1 7.0000 602test t1 idx2 2 2.3846 603test t1 idx3 1 8.5000 604test t1 idx4 1 6.2000 605test t1 idx4 2 1.6875 606test t1 idx4 3 1.1304 607SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/save_column_stats' 608 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 609 FROM mysql.column_stats WHERE column_name='b'; 610SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/save_index_stats' 611 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' 612 FROM mysql.index_stats WHERE index_name IN ('idx1', 'idx4'); 613ALTER TABLE t1 CHANGE COLUMN b x varchar(30); 614SHOW CREATE TABLE t1; 615Table Create Table 616t1 CREATE TABLE `t1` ( 617 `a` int(11) NOT NULL, 618 `x` varchar(30) DEFAULT NULL, 619 `c` char(16) DEFAULT NULL, 620 `d` date DEFAULT NULL, 621 `e` double DEFAULT NULL, 622 `f` bit(3) DEFAULT NULL, 623 PRIMARY KEY (`a`), 624 KEY `idx1` (`x`,`e`), 625 KEY `idx2` (`c`,`d`), 626 KEY `idx3` (`d`), 627 KEY `idx4` (`e`,`x`,`d`) 628) ENGINE=MyISAM DEFAULT CHARSET=latin1 629SELECT * FROM mysql.column_stats; 630db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 631test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 632test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 633test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 634test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 635test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 636SELECT * FROM mysql.index_stats; 637db_name table_name index_name prefix_arity avg_frequency 638test t1 PRIMARY 1 1.0000 639test t1 idx2 1 7.0000 640test t1 idx2 2 2.3846 641test t1 idx3 1 8.5000 642ALTER TABLE t1 CHANGE COLUMN x b varchar(32); 643SHOW CREATE TABLE t1; 644Table Create Table 645t1 CREATE TABLE `t1` ( 646 `a` int(11) NOT NULL, 647 `b` varchar(32) DEFAULT NULL, 648 `c` char(16) DEFAULT NULL, 649 `d` date DEFAULT NULL, 650 `e` double DEFAULT NULL, 651 `f` bit(3) DEFAULT NULL, 652 PRIMARY KEY (`a`), 653 KEY `idx1` (`b`,`e`), 654 KEY `idx2` (`c`,`d`), 655 KEY `idx3` (`d`), 656 KEY `idx4` (`e`,`b`,`d`) 657) ENGINE=MyISAM DEFAULT CHARSET=latin1 658SELECT * FROM mysql.column_stats; 659db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 660test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 661test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 662test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 663test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 664test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 665SELECT * FROM mysql.index_stats; 666db_name table_name index_name prefix_arity avg_frequency 667test t1 PRIMARY 1 1.0000 668test t1 idx2 1 7.0000 669test t1 idx2 2 2.3846 670test t1 idx3 1 8.5000 671LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/save_column_stats' 672 INTO TABLE mysql.column_stats 673FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; 674LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/save_index_stats' 675 INTO TABLE mysql.index_stats 676FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; 677SELECT * FROM mysql.column_stats; 678db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 679test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 680test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 681test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 682test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 683test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 684test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 685SELECT * FROM mysql.index_stats; 686db_name table_name index_name prefix_arity avg_frequency 687test t1 PRIMARY 1 1.0000 688test t1 idx1 1 6.4000 689test t1 idx1 2 1.6875 690test t1 idx2 1 7.0000 691test t1 idx2 2 2.3846 692test t1 idx3 1 8.5000 693test t1 idx4 1 6.2000 694test t1 idx4 2 1.6875 695test t1 idx4 3 1.1304 696ALTER TABLE t1 DROP COLUMN b; 697SHOW CREATE TABLE t1; 698Table Create Table 699t1 CREATE TABLE `t1` ( 700 `a` int(11) NOT NULL, 701 `c` char(16) DEFAULT NULL, 702 `d` date DEFAULT NULL, 703 `e` double DEFAULT NULL, 704 `f` bit(3) DEFAULT NULL, 705 PRIMARY KEY (`a`), 706 KEY `idx1` (`e`), 707 KEY `idx2` (`c`,`d`), 708 KEY `idx3` (`d`), 709 KEY `idx4` (`e`,`d`) 710) ENGINE=MyISAM DEFAULT CHARSET=latin1 711SELECT * FROM mysql.column_stats; 712db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 713test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 714test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 715test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 716test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 717test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 718SELECT * FROM mysql.index_stats; 719db_name table_name index_name prefix_arity avg_frequency 720test t1 PRIMARY 1 1.0000 721test t1 idx2 1 7.0000 722test t1 idx2 2 2.3846 723test t1 idx3 1 8.5000 724DROP INDEX idx2 ON t1; 725SHOW CREATE TABLE t1; 726Table Create Table 727t1 CREATE TABLE `t1` ( 728 `a` int(11) NOT NULL, 729 `c` char(16) DEFAULT NULL, 730 `d` date DEFAULT NULL, 731 `e` double DEFAULT NULL, 732 `f` bit(3) DEFAULT NULL, 733 PRIMARY KEY (`a`), 734 KEY `idx1` (`e`), 735 KEY `idx3` (`d`), 736 KEY `idx4` (`e`,`d`) 737) ENGINE=MyISAM DEFAULT CHARSET=latin1 738SELECT * FROM mysql.index_stats; 739db_name table_name index_name prefix_arity avg_frequency 740test t1 PRIMARY 1 1.0000 741test t1 idx3 1 8.5000 742DROP INDEX idx1 ON t1; 743DROP INDEX idx4 ON t1; 744SHOW CREATE TABLE t1; 745Table Create Table 746t1 CREATE TABLE `t1` ( 747 `a` int(11) NOT NULL, 748 `c` char(16) DEFAULT NULL, 749 `d` date DEFAULT NULL, 750 `e` double DEFAULT NULL, 751 `f` bit(3) DEFAULT NULL, 752 PRIMARY KEY (`a`), 753 KEY `idx3` (`d`) 754) ENGINE=MyISAM DEFAULT CHARSET=latin1 755ALTER TABLE t1 ADD COLUMN b varchar(32); 756CREATE INDEX idx1 ON t1(b, e); 757CREATE INDEX idx2 ON t1(c, d); 758CREATE INDEX idx4 ON t1(e, b, d); 759SHOW CREATE TABLE t1; 760Table Create Table 761t1 CREATE TABLE `t1` ( 762 `a` int(11) NOT NULL, 763 `c` char(16) DEFAULT NULL, 764 `d` date DEFAULT NULL, 765 `e` double DEFAULT NULL, 766 `f` bit(3) DEFAULT NULL, 767 `b` varchar(32) DEFAULT NULL, 768 PRIMARY KEY (`a`), 769 KEY `idx3` (`d`), 770 KEY `idx1` (`b`,`e`), 771 KEY `idx2` (`c`,`d`), 772 KEY `idx4` (`e`,`b`,`d`) 773) ENGINE=MyISAM DEFAULT CHARSET=latin1 774SELECT * FROM mysql.column_stats; 775db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 776test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 777test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 778test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 779test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 780test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 781SELECT * FROM mysql.index_stats; 782db_name table_name index_name prefix_arity avg_frequency 783test t1 PRIMARY 1 1.0000 784test t1 idx3 1 8.5000 785ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 786Table Op Msg_type Msg_text 787test.t1 analyze status Engine-independent statistics collected 788test.t1 analyze status OK 789SELECT * FROM mysql.column_stats; 790db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 791test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 792test t1 b NULL NULL 1.0000 NULL NULL 0 NULL NULL 793test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 794test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 795test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 796test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 797SELECT * FROM mysql.index_stats; 798db_name table_name index_name prefix_arity avg_frequency 799test t1 PRIMARY 1 1.0000 800test t1 idx1 1 NULL 801test t1 idx1 2 NULL 802test t1 idx2 1 7.0000 803test t1 idx2 2 2.3846 804test t1 idx3 1 8.5000 805test t1 idx4 1 6.2000 806test t1 idx4 2 NULL 807test t1 idx4 3 NULL 808UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a); 809ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 810Table Op Msg_type Msg_text 811test.t1 analyze status Engine-independent statistics collected 812test.t1 analyze status OK 813SELECT * FROM mysql.column_stats; 814db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 815test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 816test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 817test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 818test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 819test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 820test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 821SELECT * FROM mysql.index_stats; 822db_name table_name index_name prefix_arity avg_frequency 823test t1 PRIMARY 1 1.0000 824test t1 idx1 1 6.4000 825test t1 idx1 2 1.6875 826test t1 idx2 1 7.0000 827test t1 idx2 2 2.3846 828test t1 idx3 1 8.5000 829test t1 idx4 1 6.2000 830test t1 idx4 2 1.6875 831test t1 idx4 3 1.1304 832ALTER TABLE t1 DROP COLUMN b, 833DROP INDEX idx1, DROP INDEX idx2, DROP INDEX idx4; 834SHOW CREATE TABLE t1; 835Table Create Table 836t1 CREATE TABLE `t1` ( 837 `a` int(11) NOT NULL, 838 `c` char(16) DEFAULT NULL, 839 `d` date DEFAULT NULL, 840 `e` double DEFAULT NULL, 841 `f` bit(3) DEFAULT NULL, 842 PRIMARY KEY (`a`), 843 KEY `idx3` (`d`) 844) ENGINE=MyISAM DEFAULT CHARSET=latin1 845SELECT * FROM mysql.column_stats; 846db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 847test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 848test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 849test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 850test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 851test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 852SELECT * FROM mysql.index_stats; 853db_name table_name index_name prefix_arity avg_frequency 854test t1 PRIMARY 1 1.0000 855test t1 idx3 1 8.5000 856ALTER TABLE t1 ADD COLUMN b varchar(32); 857ALTER TABLE t1 858ADD INDEX idx1 (b, e), ADD INDEX idx2 (c, d), ADD INDEX idx4 (e, b, d); 859UPDATE t1 SET b=(SELECT b FROM t0 WHERE t0.a= t1.a); 860SHOW CREATE TABLE t1; 861Table Create Table 862t1 CREATE TABLE `t1` ( 863 `a` int(11) NOT NULL, 864 `c` char(16) DEFAULT NULL, 865 `d` date DEFAULT NULL, 866 `e` double DEFAULT NULL, 867 `f` bit(3) DEFAULT NULL, 868 `b` varchar(32) DEFAULT NULL, 869 PRIMARY KEY (`a`), 870 KEY `idx3` (`d`), 871 KEY `idx1` (`b`,`e`), 872 KEY `idx2` (`c`,`d`), 873 KEY `idx4` (`e`,`b`,`d`) 874) ENGINE=MyISAM DEFAULT CHARSET=latin1 875SELECT * FROM mysql.column_stats; 876db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 877test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 878test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 879test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 880test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 881test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 882SELECT * FROM mysql.index_stats; 883db_name table_name index_name prefix_arity avg_frequency 884test t1 PRIMARY 1 1.0000 885test t1 idx3 1 8.5000 886ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 887Table Op Msg_type Msg_text 888test.t1 analyze status Engine-independent statistics collected 889test.t1 analyze status OK 890SELECT * FROM mysql.column_stats; 891db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 892test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 893test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 894test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 895test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 896test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 897test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 898SELECT * FROM mysql.index_stats; 899db_name table_name index_name prefix_arity avg_frequency 900test t1 PRIMARY 1 1.0000 901test t1 idx1 1 6.4000 902test t1 idx1 2 1.6875 903test t1 idx2 1 7.0000 904test t1 idx2 2 2.3846 905test t1 idx3 1 8.5000 906test t1 idx4 1 6.2000 907test t1 idx4 2 1.6875 908test t1 idx4 3 1.1304 909DELETE FROM mysql.table_stats; 910DELETE FROM mysql.column_stats; 911DELETE FROM mysql.index_stats; 912ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES(); 913Table Op Msg_type Msg_text 914test.t1 analyze status Engine-independent statistics collected 915test.t1 analyze status Table is already up to date 916SELECT * FROM mysql.table_stats; 917db_name table_name cardinality 918test t1 40 919SELECT * FROM mysql.column_stats; 920db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 921SELECT * FROM mysql.index_stats; 922db_name table_name index_name prefix_arity avg_frequency 923ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(c,e,b) INDEXES(idx2,idx4); 924Table Op Msg_type Msg_text 925test.t1 analyze status Engine-independent statistics collected 926test.t1 analyze status Table is already up to date 927SELECT * FROM mysql.table_stats; 928db_name table_name cardinality 929test t1 40 930SELECT * FROM mysql.column_stats; 931db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 932test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 933test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 934test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 935SELECT * FROM mysql.index_stats; 936db_name table_name index_name prefix_arity avg_frequency 937test t1 idx2 1 7.0000 938test t1 idx2 2 2.3846 939test t1 idx4 1 6.2000 940test t1 idx4 2 1.6875 941test t1 idx4 3 1.1304 942DELETE FROM mysql.index_stats WHERE table_name='t1' AND index_name='primary'; 943SELECT * FROM mysql.index_stats; 944db_name table_name index_name prefix_arity avg_frequency 945test t1 idx2 1 7.0000 946test t1 idx2 2 2.3846 947test t1 idx4 1 6.2000 948test t1 idx4 2 1.6875 949test t1 idx4 3 1.1304 950ANALYZE TABLE t1 PERSISTENT FOR COLUMNS() INDEXES(primary); 951Table Op Msg_type Msg_text 952test.t1 analyze status Engine-independent statistics collected 953test.t1 analyze status Table is already up to date 954SELECT * FROM mysql.index_stats; 955db_name table_name index_name prefix_arity avg_frequency 956test t1 PRIMARY 1 1.0000 957test t1 idx2 1 7.0000 958test t1 idx2 2 2.3846 959test t1 idx4 1 6.2000 960test t1 idx4 2 1.6875 961test t1 idx4 3 1.1304 962DELETE FROM mysql.table_stats; 963DELETE FROM mysql.column_stats; 964DELETE FROM mysql.index_stats; 965ANALYZE TABLE t1 PERSISTENT FOR COLUMNS ALL INDEXES ALL; 966Table Op Msg_type Msg_text 967test.t1 analyze status Engine-independent statistics collected 968test.t1 analyze status Table is already up to date 969SELECT * FROM mysql.table_stats; 970db_name table_name cardinality 971test t1 40 972SELECT * FROM mysql.column_stats; 973db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 974test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 975test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 976test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 977test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 978test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 979test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 980SELECT * FROM mysql.index_stats; 981db_name table_name index_name prefix_arity avg_frequency 982test t1 PRIMARY 1 1.0000 983test t1 idx1 1 6.4000 984test t1 idx1 2 1.6875 985test t1 idx2 1 7.0000 986test t1 idx2 2 2.3846 987test t1 idx3 1 8.5000 988test t1 idx4 1 6.2000 989test t1 idx4 2 1.6875 990test t1 idx4 3 1.1304 991CREATE TABLE t2 LIKE t1; 992ALTER TABLE t2 ENGINE=InnoDB; 993INSERT INTO t2 SELECT * FROM t1; 994set optimizer_switch='extended_keys=off'; 995ANALYZE TABLE t2; 996Table Op Msg_type Msg_text 997test.t2 analyze status Engine-independent statistics collected 998test.t2 analyze status OK 999SELECT * FROM mysql.table_stats; 1000db_name table_name cardinality 1001test t1 40 1002test t2 40 1003SELECT * FROM mysql.column_stats ORDER BY column_name, table_name; 1004db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1005test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1006test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1007test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 1008test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 1009test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1010test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1011test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1012test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1013test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1014test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1015test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1016test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1017SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1018db_name table_name index_name prefix_arity avg_frequency 1019test t1 PRIMARY 1 1.0000 1020test t2 PRIMARY 1 1.0000 1021test t1 idx1 1 6.4000 1022test t2 idx1 1 6.4000 1023test t1 idx1 2 1.6875 1024test t2 idx1 2 1.6875 1025test t1 idx2 1 7.0000 1026test t2 idx2 1 7.0000 1027test t1 idx2 2 2.3846 1028test t2 idx2 2 2.3846 1029test t1 idx3 1 8.5000 1030test t2 idx3 1 8.5000 1031test t1 idx4 1 6.2000 1032test t2 idx4 1 6.2000 1033test t1 idx4 2 1.6875 1034test t2 idx4 2 1.6875 1035test t1 idx4 3 1.1304 1036test t2 idx4 3 1.1304 1037DELETE FROM mysql.table_stats; 1038DELETE FROM mysql.column_stats; 1039DELETE FROM mysql.index_stats; 1040set optimizer_switch='extended_keys=on'; 1041ANALYZE TABLE t2; 1042Table Op Msg_type Msg_text 1043test.t2 analyze status Engine-independent statistics collected 1044test.t2 analyze status OK 1045SELECT * FROM mysql.table_stats; 1046db_name table_name cardinality 1047test t2 40 1048SELECT * FROM mysql.column_stats ORDER BY column_name; 1049db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1050test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1051test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL NULL 1052test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1053test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1054test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1055test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1056SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1057db_name table_name index_name prefix_arity avg_frequency 1058test t2 PRIMARY 1 1.0000 1059test t2 idx1 1 6.4000 1060test t2 idx1 2 1.6875 1061test t2 idx1 3 1.0000 1062test t2 idx2 1 7.0000 1063test t2 idx2 2 2.3846 1064test t2 idx2 3 1.0000 1065test t2 idx3 1 8.5000 1066test t2 idx3 2 1.0000 1067test t2 idx4 1 6.2000 1068test t2 idx4 2 1.6875 1069test t2 idx4 3 1.1304 1070test t2 idx4 4 1.0000 1071ALTER TABLE t2 DROP PRIMARY KEY, DROP INDEX idx1; 1072SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1073db_name table_name index_name prefix_arity avg_frequency 1074test t2 idx2 1 7.0000 1075test t2 idx2 2 2.3846 1076test t2 idx3 1 8.5000 1077test t2 idx4 1 6.2000 1078test t2 idx4 2 1.6875 1079test t2 idx4 3 1.1304 1080UPDATE t2 SET b=0 WHERE b IS NULL; 1081ALTER TABLE t2 ADD PRIMARY KEY (a,b); 1082SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1083db_name table_name index_name prefix_arity avg_frequency 1084test t2 idx2 1 7.0000 1085test t2 idx2 2 2.3846 1086test t2 idx3 1 8.5000 1087test t2 idx4 1 6.2000 1088test t2 idx4 2 1.6875 1089test t2 idx4 3 1.1304 1090ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; 1091Table Op Msg_type Msg_text 1092test.t2 analyze status Engine-independent statistics collected 1093test.t2 analyze status OK 1094SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1095db_name table_name index_name prefix_arity avg_frequency 1096test t2 PRIMARY 1 1.0000 1097test t2 PRIMARY 2 1.0000 1098test t2 idx2 1 7.0000 1099test t2 idx2 2 2.3846 1100test t2 idx2 3 1.0000 1101test t2 idx2 4 1.0000 1102test t2 idx3 1 8.5000 1103test t2 idx3 2 1.0000 1104test t2 idx3 3 1.0000 1105test t2 idx4 1 6.2000 1106test t2 idx4 2 1.7222 1107test t2 idx4 3 1.1154 1108test t2 idx4 4 1.0000 1109ALTER TABLE t2 CHANGE COLUMN b b varchar(30); 1110SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1111db_name table_name index_name prefix_arity avg_frequency 1112test t2 idx2 1 7.0000 1113test t2 idx2 2 2.3846 1114test t2 idx3 1 8.5000 1115ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; 1116Table Op Msg_type Msg_text 1117test.t2 analyze status Engine-independent statistics collected 1118test.t2 analyze status OK 1119SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1120db_name table_name index_name prefix_arity avg_frequency 1121test t2 PRIMARY 1 1.0000 1122test t2 PRIMARY 2 1.0000 1123test t2 idx2 1 7.0000 1124test t2 idx2 2 2.3846 1125test t2 idx2 3 1.0000 1126test t2 idx2 4 1.0000 1127test t2 idx3 1 8.5000 1128test t2 idx3 2 1.0000 1129test t2 idx3 3 1.0000 1130test t2 idx4 1 6.2000 1131test t2 idx4 2 1.7222 1132test t2 idx4 3 1.1154 1133test t2 idx4 4 1.0000 1134ALTER TABLE t2 CHANGE COLUMN b b varchar(32); 1135SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1136db_name table_name index_name prefix_arity avg_frequency 1137test t2 PRIMARY 1 1.0000 1138test t2 PRIMARY 2 1.0000 1139test t2 idx2 1 7.0000 1140test t2 idx2 2 2.3846 1141test t2 idx2 3 1.0000 1142test t2 idx2 4 1.0000 1143test t2 idx3 1 8.5000 1144test t2 idx3 2 1.0000 1145test t2 idx3 3 1.0000 1146test t2 idx4 1 6.2000 1147test t2 idx4 2 1.7222 1148test t2 idx4 3 1.1154 1149test t2 idx4 4 1.0000 1150ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; 1151Table Op Msg_type Msg_text 1152test.t2 analyze status Engine-independent statistics collected 1153test.t2 analyze status OK 1154SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1155db_name table_name index_name prefix_arity avg_frequency 1156test t2 PRIMARY 1 1.0000 1157test t2 PRIMARY 2 1.0000 1158test t2 idx2 1 7.0000 1159test t2 idx2 2 2.3846 1160test t2 idx2 3 1.0000 1161test t2 idx2 4 1.0000 1162test t2 idx3 1 8.5000 1163test t2 idx3 2 1.0000 1164test t2 idx3 3 1.0000 1165test t2 idx4 1 6.2000 1166test t2 idx4 2 1.7222 1167test t2 idx4 3 1.1154 1168test t2 idx4 4 1.0000 1169ALTER TABLE t2 DROP COLUMN b, DROP PRIMARY KEY, ADD PRIMARY KEY(a); 1170SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1171db_name table_name index_name prefix_arity avg_frequency 1172test t2 idx2 1 7.0000 1173test t2 idx2 2 2.3846 1174test t2 idx3 1 8.5000 1175ANALYZE TABLE t2 PERSISTENT FOR COLUMNS() INDEXES ALL; 1176Table Op Msg_type Msg_text 1177test.t2 analyze status Engine-independent statistics collected 1178test.t2 analyze status OK 1179SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; 1180db_name table_name index_name prefix_arity avg_frequency 1181test t2 PRIMARY 1 1.0000 1182test t2 idx2 1 7.0000 1183test t2 idx2 2 2.3846 1184test t2 idx2 3 1.0000 1185test t2 idx3 1 8.5000 1186test t2 idx3 2 1.0000 1187test t2 idx4 1 6.2000 1188test t2 idx4 2 2.2308 1189test t2 idx4 3 1.0000 1190set optimizer_switch='extended_keys=off'; 1191ALTER TABLE t1 1192DROP INDEX idx1, 1193DROP INDEX idx4; 1194ALTER TABLE t1 1195MODIFY COLUMN b text, 1196ADD INDEX idx1 (b(4), e), 1197ADD INDEX idx4 (e, b(4), d); 1198SELECT * FROM mysql.column_stats; 1199db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1200test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1201test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1202test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1203test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1204test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1205SELECT * FROM mysql.index_stats; 1206db_name table_name index_name prefix_arity avg_frequency 1207test t2 PRIMARY 1 1.0000 1208test t2 idx2 1 7.0000 1209test t2 idx2 2 2.3846 1210test t2 idx2 3 1.0000 1211test t2 idx3 1 8.5000 1212test t2 idx3 2 1.0000 1213test t2 idx4 1 6.2000 1214test t2 idx4 2 2.2308 1215test t2 idx4 3 1.0000 1216ANALYZE TABLE t1; 1217Table Op Msg_type Msg_text 1218test.t1 analyze status Engine-independent statistics collected 1219test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' 1220test.t1 analyze status OK 1221SELECT * FROM mysql.column_stats; 1222db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1223test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1224test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1225test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1226test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1227test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1228test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1229test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1230test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1231test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1232test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1233SELECT * FROM mysql.index_stats; 1234db_name table_name index_name prefix_arity avg_frequency 1235test t1 PRIMARY 1 1.0000 1236test t1 idx1 1 NULL 1237test t1 idx1 2 NULL 1238test t1 idx2 1 7.0000 1239test t1 idx2 2 2.3846 1240test t1 idx3 1 8.5000 1241test t1 idx4 1 6.2000 1242test t1 idx4 2 NULL 1243test t1 idx4 3 NULL 1244test t2 PRIMARY 1 1.0000 1245test t2 idx2 1 7.0000 1246test t2 idx2 2 2.3846 1247test t2 idx2 3 1.0000 1248test t2 idx3 1 8.5000 1249test t2 idx3 2 1.0000 1250test t2 idx4 1 6.2000 1251test t2 idx4 2 2.2308 1252test t2 idx4 3 1.0000 1253DELETE FROM mysql.table_stats; 1254DELETE FROM mysql.column_stats; 1255DELETE FROM mysql.index_stats; 1256ANALYZE TABLE mysql.column_stats PERSISTENT FOR ALL; 1257Table Op Msg_type Msg_text 1258mysql.column_stats analyze error Invalid argument 1259ANALYZE TABLE mysql.column_stats; 1260Table Op Msg_type Msg_text 1261mysql.column_stats analyze status OK 1262SELECT * FROM mysql.table_stats; 1263db_name table_name cardinality 1264SELECT * FROM mysql.column_stats; 1265db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1266SELECT * FROM mysql.index_stats; 1267db_name table_name index_name prefix_arity avg_frequency 1268set use_stat_tables='never'; 1269ANALYZE TABLE t1 PERSISTENT FOR ALL; 1270Table Op Msg_type Msg_text 1271test.t1 analyze status Engine-independent statistics collected 1272test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' 1273test.t1 analyze status Table is already up to date 1274SELECT * FROM mysql.table_stats; 1275db_name table_name cardinality 1276test t1 40 1277SELECT * FROM mysql.column_stats; 1278db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram 1279test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL NULL 1280test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL NULL 1281test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL NULL 1282test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL NULL 1283test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL 1284SELECT * FROM mysql.index_stats; 1285db_name table_name index_name prefix_arity avg_frequency 1286test t1 PRIMARY 1 1.0000 1287test t1 idx1 1 NULL 1288test t1 idx1 2 NULL 1289test t1 idx2 1 7.0000 1290test t1 idx2 2 2.3846 1291test t1 idx3 1 8.5000 1292test t1 idx4 1 6.2000 1293test t1 idx4 2 NULL 1294test t1 idx4 3 NULL 1295DELETE FROM mysql.table_stats; 1296DELETE FROM mysql.column_stats; 1297DELETE FROM mysql.index_stats; 1298ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(); 1299Table Op Msg_type Msg_text 1300test.t1 analyze status Engine-independent statistics collected 1301test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' 1302test.t1 analyze status Table is already up to date 1303ANALYZE TABLE t1 PERSISTENT FOR columns(a,b) INDEXES(); 1304Table Op Msg_type Msg_text 1305test.t1 analyze status Engine-independent statistics collected 1306test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' 1307test.t1 analyze status Table is already up to date 1308ANALYZE TABLE t1 PERSISTENT FOR columns(b) indexes(idx2); 1309Table Op Msg_type Msg_text 1310test.t1 analyze status Engine-independent statistics collected 1311test.t1 analyze Warning Engine-independent statistics are not collected for column 'b' 1312test.t1 analyze status Table is already up to date 1313ANALYZE TABLE t1 PERSISTENT FOR columns() indexes(idx2); 1314Table Op Msg_type Msg_text 1315test.t1 analyze status Engine-independent statistics collected 1316test.t1 analyze status Table is already up to date 1317DELETE FROM mysql.table_stats; 1318DELETE FROM mysql.column_stats; 1319DELETE FROM mysql.index_stats; 1320DROP TABLE t1,t2; 1321set names utf8; 1322CREATE DATABASE world; 1323use world; 1324CREATE TABLE Country ( 1325Code char(3) NOT NULL default '', 1326Name char(52) NOT NULL default '', 1327SurfaceArea float(10,2) NOT NULL default '0.00', 1328Population int(11) NOT NULL default '0', 1329Capital int(11) default NULL, 1330PRIMARY KEY (Code), 1331UNIQUE INDEX (Name) 1332) CHARACTER SET utf8 COLLATE utf8_bin; 1333CREATE TABLE City ( 1334ID int(11) NOT NULL auto_increment, 1335Name char(35) NOT NULL default '', 1336Country char(3) NOT NULL default '', 1337Population int(11) NOT NULL default '0', 1338PRIMARY KEY (ID), 1339INDEX (Population), 1340INDEX (Country) 1341) CHARACTER SET utf8 COLLATE utf8_bin; 1342CREATE TABLE CountryLanguage ( 1343Country char(3) NOT NULL default '', 1344Language char(30) NOT NULL default '', 1345Percentage float(3,1) NOT NULL default '0.0', 1346PRIMARY KEY (Country, Language), 1347INDEX (Percentage) 1348) CHARACTER SET utf8 COLLATE utf8_bin; 1349set use_stat_tables='preferably'; 1350ANALYZE TABLE Country, City, CountryLanguage; 1351SELECT UPPER(db_name), UPPER(table_name), cardinality 1352FROM mysql.table_stats; 1353UPPER(db_name) UPPER(table_name) cardinality 1354WORLD CITY 4079 1355WORLD COUNTRY 239 1356WORLD COUNTRYLANGUAGE 984 1357SELECT UPPER(db_name), UPPER(table_name), 1358column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency 1359FROM mysql.column_stats; 1360UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency 1361WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 1362WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 1363WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 1364WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 1365WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 1366WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 1367WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 1368WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 1369WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 1370WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 1371WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 1372WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 1373SELECT UPPER(db_name), UPPER(table_name), 1374index_name, prefix_arity, avg_frequency 1375FROM mysql.index_stats; 1376UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency 1377WORLD CITY Country 1 17.5819 1378WORLD CITY PRIMARY 1 1.0000 1379WORLD CITY Population 1 1.0467 1380WORLD COUNTRY Name 1 1.0000 1381WORLD COUNTRY PRIMARY 1 1.0000 1382WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 1383WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 1384WORLD COUNTRYLANGUAGE Percentage 1 2.7640 1385use test; 1386set use_stat_tables='never'; 1387CREATE DATABASE world_innodb; 1388use world_innodb; 1389CREATE TABLE Country ( 1390Code char(3) NOT NULL default '', 1391Name char(52) NOT NULL default '', 1392SurfaceArea float(10,2) NOT NULL default '0.00', 1393Population int(11) NOT NULL default '0', 1394Capital int(11) default NULL, 1395PRIMARY KEY (Code), 1396UNIQUE INDEX (Name) 1397) CHARACTER SET utf8 COLLATE utf8_bin; 1398CREATE TABLE City ( 1399ID int(11) NOT NULL auto_increment, 1400Name char(35) NOT NULL default '', 1401Country char(3) NOT NULL default '', 1402Population int(11) NOT NULL default '0', 1403PRIMARY KEY (ID), 1404INDEX (Population), 1405INDEX (Country) 1406) CHARACTER SET utf8 COLLATE utf8_bin; 1407CREATE TABLE CountryLanguage ( 1408Country char(3) NOT NULL default '', 1409Language char(30) NOT NULL default '', 1410Percentage float(3,1) NOT NULL default '0.0', 1411PRIMARY KEY (Country, Language), 1412INDEX (Percentage) 1413) CHARACTER SET utf8 COLLATE utf8_bin; 1414ALTER TABLE Country ENGINE=InnoDB; 1415ALTER TABLE City ENGINE=InnoDB; 1416ALTER TABLE CountryLanguage ENGINE=InnoDB; 1417set use_stat_tables='preferably'; 1418ANALYZE TABLE Country, City, CountryLanguage; 1419SELECT UPPER(db_name), UPPER(table_name), cardinality 1420FROM mysql.table_stats; 1421UPPER(db_name) UPPER(table_name) cardinality 1422WORLD CITY 4079 1423WORLD COUNTRY 239 1424WORLD COUNTRYLANGUAGE 984 1425WORLD_INNODB CITY 4079 1426WORLD_INNODB COUNTRY 239 1427WORLD_INNODB COUNTRYLANGUAGE 984 1428SELECT UPPER(db_name), UPPER(table_name), 1429column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency 1430FROM mysql.column_stats; 1431UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency 1432WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 1433WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 1434WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 1435WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 1436WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 1437WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 1438WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 1439WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 1440WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 1441WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 1442WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 1443WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 1444WORLD_INNODB CITY Country ABW ZWE 0.0000 3.0000 17.5819 1445WORLD_INNODB CITY ID 1 4079 0.0000 4.0000 1.0000 1446WORLD_INNODB CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 1447WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 1448WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 1449WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 1450WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 1451WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 1452WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 1453WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 1454WORLD_INNODB COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 1455WORLD_INNODB COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 1456SELECT UPPER(db_name), UPPER(table_name), 1457index_name, prefix_arity, avg_frequency 1458FROM mysql.index_stats; 1459UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency 1460WORLD CITY Country 1 17.5819 1461WORLD CITY PRIMARY 1 1.0000 1462WORLD CITY Population 1 1.0467 1463WORLD COUNTRY Name 1 1.0000 1464WORLD COUNTRY PRIMARY 1 1.0000 1465WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 1466WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 1467WORLD COUNTRYLANGUAGE Percentage 1 2.7640 1468WORLD_INNODB CITY Country 1 17.5819 1469WORLD_INNODB CITY PRIMARY 1 1.0000 1470WORLD_INNODB CITY Population 1 1.0467 1471WORLD_INNODB COUNTRY Name 1 1.0000 1472WORLD_INNODB COUNTRY PRIMARY 1 1.0000 1473WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 1474WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 1475WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 1476use world; 1477set use_stat_tables='preferably'; 1478set histogram_size=100; 1479set histogram_type='SINGLE_PREC_HB'; 1480ANALYZE TABLE CountryLanguage; 1481set histogram_size=254; 1482set histogram_type='DOUBLE_PREC_HB'; 1483ANALYZE TABLE City; 1484FLUSH TABLES; 1485select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='COUNTRYLANGUAGE' and UPPER(column_name) = 'PERCENTAGE';; 1486UPPER(db_name) WORLD 1487UPPER(table_name) COUNTRYLANGUAGE 1488UPPER(column_name) PERCENTAGE 1489min_value 0.0 1490max_value 99.9 1491nulls_ratio 0.0000 1492avg_length 4.0000 1493avg_frequency 2.7640 1494hist_size 100 1495hist_type SINGLE_PREC_HB 1496hex(histogram) 0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF 1497decode_histogram(hist_type,histogram) 0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 1498select UPPER(db_name),UPPER(table_name),UPPER(column_name),min_value,max_value,nulls_ratio,avg_length,avg_frequency,hist_size,hist_type,hex(histogram),decode_histogram(hist_type,histogram) from mysql.column_stats where UPPER(db_name)='WORLD' and UPPER(table_name)='CITY' and UPPER(column_name) = 'POPULATION';; 1499UPPER(db_name) WORLD 1500UPPER(table_name) CITY 1501UPPER(column_name) POPULATION 1502min_value 42 1503max_value 10500000 1504nulls_ratio 0.0000 1505avg_length 4.0000 1506avg_frequency 1.0467 1507hist_size 254 1508hist_type DOUBLE_PREC_HB 1509hex(histogram) 1F00A1002B023002350238023F02430249024E02520258025D02630268026E02720276027B02800285028C02920297029D02A102A802AC02B402BC02C402CC02D302DA02E302EA02F102F802010305030C03120319031F03290333033D0343034F03590363036D037803840390039A03A603B303C303D103E003F203020412042404330440045304600472047F049104A204B804C804DE04F2040A0526053F0558056F058E05B305D905F4051306380667068406AB06DA06020731075C079407C507F8072E085E08A508DF0824096909CC092E0A760AD50A400BA90B150CAD0C310D240E130F0E103B11B9126B14F0166B192F1CB71FFF240630483FC567 1510decode_histogram(hist_type,histogram) 0.00047,0.00198,0.00601,0.00008,0.00008,0.00005,0.00011,0.00006,0.00009,0.00008,0.00006,0.00009,0.00008,0.00009,0.00008,0.00009,0.00006,0.00006,0.00008,0.00008,0.00008,0.00011,0.00009,0.00008,0.00009,0.00006,0.00011,0.00006,0.00012,0.00012,0.00012,0.00012,0.00011,0.00011,0.00014,0.00011,0.00011,0.00011,0.00014,0.00006,0.00011,0.00009,0.00011,0.00009,0.00015,0.00015,0.00015,0.00009,0.00018,0.00015,0.00015,0.00015,0.00017,0.00018,0.00018,0.00015,0.00018,0.00020,0.00024,0.00021,0.00023,0.00027,0.00024,0.00024,0.00027,0.00023,0.00020,0.00029,0.00020,0.00027,0.00020,0.00027,0.00026,0.00034,0.00024,0.00034,0.00031,0.00037,0.00043,0.00038,0.00038,0.00035,0.00047,0.00056,0.00058,0.00041,0.00047,0.00056,0.00072,0.00044,0.00060,0.00072,0.00061,0.00072,0.00066,0.00085,0.00075,0.00078,0.00082,0.00073,0.00108,0.00089,0.00105,0.00105,0.00151,0.00150,0.00110,0.00145,0.00163,0.00160,0.00165,0.00232,0.00201,0.00371,0.00365,0.00383,0.00459,0.00583,0.00662,0.00984,0.00969,0.01080,0.01379,0.02063,0.04308,0.05960,0.15816,0.59464 1511set histogram_type='single_prec_hb'; 1512set histogram_size=0; 1513use test; 1514DROP DATABASE world; 1515SELECT UPPER(db_name), UPPER(table_name), cardinality 1516FROM mysql.table_stats; 1517UPPER(db_name) UPPER(table_name) cardinality 1518WORLD_INNODB CITY 4079 1519WORLD_INNODB COUNTRY 239 1520WORLD_INNODB COUNTRYLANGUAGE 984 1521SELECT UPPER(db_name), UPPER(table_name), 1522column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency 1523FROM mysql.column_stats; 1524UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency 1525WORLD_INNODB CITY Country ABW ZWE 0.0000 3.0000 17.5819 1526WORLD_INNODB CITY ID 1 4079 0.0000 4.0000 1.0000 1527WORLD_INNODB CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 1528WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 1529WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 1530WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 1531WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 1532WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 1533WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 1534WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 1535WORLD_INNODB COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 1536WORLD_INNODB COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 1537SELECT UPPER(db_name), UPPER(table_name), 1538index_name, prefix_arity, avg_frequency 1539FROM mysql.index_stats; 1540UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency 1541WORLD_INNODB CITY Country 1 17.5819 1542WORLD_INNODB CITY PRIMARY 1 1.0000 1543WORLD_INNODB CITY Population 1 1.0467 1544WORLD_INNODB COUNTRY Name 1 1.0000 1545WORLD_INNODB COUNTRY PRIMARY 1 1.0000 1546WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 1547WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 1548WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 1549DROP DATABASE world_innodb; 1550SELECT UPPER(db_name), UPPER(table_name), cardinality 1551FROM mysql.table_stats; 1552UPPER(db_name) UPPER(table_name) cardinality 1553SELECT UPPER(db_name), UPPER(table_name), 1554column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency 1555FROM mysql.column_stats; 1556UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency 1557SELECT UPPER(db_name), UPPER(table_name), 1558index_name, prefix_arity, avg_frequency 1559FROM mysql.index_stats; 1560UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency 1561DELETE FROM mysql.table_stats; 1562DELETE FROM mysql.column_stats; 1563DELETE FROM mysql.index_stats; 1564# 1565# Bug mdev-4357: empty string as a value of the HIST_SIZE column 1566# from mysql.column_stats 1567# 1568create table t1 (a int); 1569insert into t1 values (1),(2),(3); 1570set histogram_size=10; 1571analyze table t1 persistent for all; 1572Table Op Msg_type Msg_text 1573test.t1 analyze status Engine-independent statistics collected 1574test.t1 analyze status OK 1575select db_name, table_name, column_name, 1576min_value, max_value, 1577nulls_ratio, avg_frequency, 1578hist_size, hist_type, HEX(histogram) 1579FROM mysql.column_stats; 1580db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 1581test t1 a 1 3 0.0000 1.0000 10 SINGLE_PREC_HB 0000007F7F7F7FFFFFFF 1582set histogram_size=default; 1583drop table t1; 1584# 1585# Bug mdev-4359: wrong setting of the HIST_SIZE column 1586# (see also mdev-4357) from mysql.column_stats 1587# 1588create table t1 ( a int); 1589insert into t1 values (1),(2),(3),(4),(5); 1590set histogram_size=10; 1591set histogram_type='double_prec_hb'; 1592show variables like 'histogram%'; 1593Variable_name Value 1594histogram_size 10 1595histogram_type DOUBLE_PREC_HB 1596analyze table t1 persistent for all; 1597Table Op Msg_type Msg_text 1598test.t1 analyze status Engine-independent statistics collected 1599test.t1 analyze status OK 1600select db_name, table_name, column_name, 1601min_value, max_value, 1602nulls_ratio, avg_frequency, 1603hist_size, hist_type, HEX(histogram) 1604FROM mysql.column_stats; 1605db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 1606test t1 a 1 5 0.0000 1.0000 10 DOUBLE_PREC_HB 0000FF3FFF7FFFBFFFFF 1607set histogram_size=0; 1608set histogram_type='single_prec_hb'; 1609drop table t1; 1610# 1611# Bug mdev-4369: histogram for a column with many distinct values 1612# 1613CREATE TABLE t1 (id int); 1614CREATE TABLE t2 (id int); 1615INSERT INTO t1 (id) VALUES (1), (1), (1),(1); 1616INSERT INTO t1 (id) SELECT id FROM t1; 1617INSERT INTO t1 SELECT id+1 FROM t1; 1618INSERT INTO t1 SELECT id+2 FROM t1; 1619INSERT INTO t1 SELECT id+4 FROM t1; 1620INSERT INTO t1 SELECT id+8 FROM t1; 1621INSERT INTO t1 SELECT id+16 FROM t1; 1622INSERT INTO t1 SELECT id+32 FROM t1; 1623INSERT INTO t1 SELECT id+64 FROM t1; 1624INSERT INTO t1 SELECT id+128 FROM t1; 1625INSERT INTO t1 SELECT id+256 FROM t1; 1626INSERT INTO t1 SELECT id+512 FROM t1; 1627INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); 1628SELECT COUNT(*) FROM t2; 1629COUNT(*) 16308192 1631SELECT COUNT(DISTINCT id) FROM t2; 1632COUNT(DISTINCT id) 16331024 1634set @@tmp_table_size=1024*16; 1635set @@max_heap_table_size=1024*16; 1636set histogram_size=63; 1637analyze table t2 persistent for all; 1638Table Op Msg_type Msg_text 1639test.t2 analyze status Engine-independent statistics collected 1640test.t2 analyze status OK 1641select db_name, table_name, column_name, 1642min_value, max_value, 1643nulls_ratio, avg_frequency, 1644hist_size, hist_type, HEX(histogram) 1645FROM mysql.column_stats; 1646db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 1647test t2 id 1 1024 0.0000 8.0000 63 SINGLE_PREC_HB 03070B0F13171B1F23272B2F33373B3F43474B4F53575B5F63676B6F73777B7F83878B8F93979B9FA3A7ABAFB3B7BBBFC3C7CBCFD3D7DBDFE3E7EBEFF3F7FB 1648set histogram_size=0; 1649drop table t1, t2; 1650set use_stat_tables=@save_use_stat_tables; 1651# 1652# Bug MDEV-7383: min/max value for a column not utf8 compatible 1653# 1654create table t1 (a varchar(100)) engine=MyISAM; 1655insert into t1 values(unhex('D879626AF872675F73E662F8')); 1656analyze table t1 persistent for all; 1657Table Op Msg_type Msg_text 1658test.t1 analyze status Engine-independent statistics collected 1659test.t1 analyze status OK 1660show warnings; 1661Level Code Message 1662select db_name, table_name, column_name, 1663HEX(min_value), HEX(max_value), 1664nulls_ratio, avg_frequency, 1665hist_size, hist_type, HEX(histogram) 1666FROM mysql.column_stats; 1667db_name table_name column_name HEX(min_value) HEX(max_value) nulls_ratio avg_frequency hist_size hist_type HEX(histogram) 1668test t1 a D879626AF872675F73E662F8 D879626AF872675F73E662F8 0.0000 1.0000 0 NULL NULL 1669drop table t1; 1670# 1671# MDEB-9744: session optimizer_use_condition_selectivity=5 causing SQL Error (1918): 1672# Encountered illegal value '' when converting to DECIMAL 1673# 1674set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 1675set optimizer_use_condition_selectivity=3, use_stat_tables=preferably; 1676create table t1 (id int(10),cost decimal(9,2)) engine=innodb; 1677ANALYZE TABLE t1 PERSISTENT FOR ALL; 1678Table Op Msg_type Msg_text 1679test.t1 analyze status Engine-independent statistics collected 1680test.t1 analyze status OK 1681create temporary table t2 (id int); 1682insert into t2 (id) select id from t1 where cost > 0; 1683select * from t2; 1684id 1685set use_stat_tables=@save_use_stat_tables; 1686set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1687drop table t1,t2; 1688# 1689# MDEV-16507: statistics for temporary tables should not be used 1690# 1691SET 1692@save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 1693SET @@use_stat_tables = preferably ; 1694SET @@optimizer_use_condition_selectivity = 4; 1695CREATE TABLE t1 ( 1696TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 1697ON UPDATE CURRENT_TIMESTAMP 1698); 1699SET @had_t1_table= @@warning_count != 0; 1700CREATE TEMPORARY TABLE tmp_t1 LIKE t1; 1701INSERT INTO tmp_t1 VALUES (now()); 1702INSERT INTO t1 SELECT * FROM tmp_t1 WHERE @had_t1_table=0; 1703DROP TABLE t1; 1704SET 1705use_stat_tables=@save_use_stat_tables; 1706SET 1707optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1708# End of 10.0 tests 1709# 1710# MDEV-9590: Always print "Engine-independent statistic" warnings and 1711# might be filtering columns unintentionally from engines 1712# 1713set use_stat_tables='NEVER'; 1714create table t1 (test blob); 1715show variables like 'use_stat_tables'; 1716Variable_name Value 1717use_stat_tables NEVER 1718analyze table t1; 1719Table Op Msg_type Msg_text 1720test.t1 analyze status Table is already up to date 1721drop table t1; 1722# 1723# MDEV-10435 crash with bad stat tables 1724# 1725set use_stat_tables='preferably'; 1726call mtr.add_suppression("Column count of mysql.table_stats is wrong. Expected 3, found 1. The table is probably corrupted"); 1727rename table mysql.table_stats to test.table_stats; 1728flush tables; 1729create table t1 (a int); 1730rename table t1 to t2, t3 to t4; 1731ERROR 42S02: Table 'test.t3' doesn't exist 1732drop table t1; 1733rename table test.table_stats to mysql.table_stats; 1734rename table mysql.table_stats to test.table_stats; 1735create table mysql.table_stats (a int); 1736flush tables; 1737create table t1 (a int); 1738rename table t1 to t2, t3 to t4; 1739ERROR 42S02: Table 'test.t3' doesn't exist 1740drop table t1, mysql.table_stats; 1741rename table test.table_stats to mysql.table_stats; 1742# 1743# MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed. 1744# 1745create temporary table t1(a int); 1746insert into t1 values (1),(2),(3); 1747set use_stat_tables=preferably; 1748set @optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 1749set optimizer_use_condition_selectivity=4; 1750select * from t1 where a >= 2; 1751a 17522 17533 1754drop table t1; 1755set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 1756set use_stat_tables=@save_use_stat_tables; 1757# 1758# Start of 10.2 tests 1759# 1760# 1761# MDEV-10134 Add full support for DEFAULT 1762# 1763CREATE TABLE t1 (a BLOB, b TEXT DEFAULT DECODE_HISTOGRAM('SINGLE_PREC_HB',a)); 1764SHOW CREATE TABLE t1; 1765Table Create Table 1766t1 CREATE TABLE `t1` ( 1767 `a` blob DEFAULT NULL, 1768 `b` text DEFAULT decode_histogram('SINGLE_PREC_HB',`a`) 1769) ENGINE=MyISAM DEFAULT CHARSET=latin1 1770INSERT INTO t1 (a) VALUES (0x0000000000000000000000000101010101010101010202020303030304040404050505050606070707080809090A0A0B0C0D0D0E0E0F10111213131415161718191B1C1E202224292A2E33373B4850575F6A76818C9AA7B9C4CFDADFE5EBF0F4F8FAFCFF); 1771SELECT b FROM t1; 1772b 17730.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.000,0.004,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.000,0.000,0.004,0.000,0.004,0.000,0.000,0.004,0.000,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.000,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.000,0.004,0.004,0.004,0.004,0.004,0.004,0.008,0.004,0.008,0.008,0.008,0.008,0.020,0.004,0.016,0.020,0.016,0.016,0.051,0.031,0.027,0.031,0.043,0.047,0.043,0.043,0.055,0.051,0.071,0.043,0.043,0.043,0.020,0.024,0.024,0.020,0.016,0.016,0.008,0.008,0.012,0.000 1774DROP TABLE t1; 1775# 1776# End of 10.2 tests 1777# 1778set histogram_size=@save_histogram_size, histogram_type=@save_hist_type; 1779# 1780# Start of 10.4 tests 1781# 1782# 1783# Test analyze_sample_percentage system variable. 1784# 1785set @save_use_stat_tables=@@use_stat_tables; 1786set @save_analyze_sample_percentage=@@analyze_sample_percentage; 1787set session rand_seed1=42; 1788set session rand_seed2=62; 1789set use_stat_tables=PREFERABLY; 1790set histogram_size=10; 1791CREATE TABLE t1 (id int); 1792INSERT INTO t1 (id) VALUES (1), (1), (1), (1), (1), (1), (1); 1793INSERT INTO t1 (id) SELECT id FROM t1; 1794INSERT INTO t1 SELECT id+1 FROM t1; 1795INSERT INTO t1 SELECT id+2 FROM t1; 1796INSERT INTO t1 SELECT id+4 FROM t1; 1797INSERT INTO t1 SELECT id+8 FROM t1; 1798INSERT INTO t1 SELECT id+16 FROM t1; 1799INSERT INTO t1 SELECT id+32 FROM t1; 1800INSERT INTO t1 SELECT id+64 FROM t1; 1801INSERT INTO t1 SELECT id+128 FROM t1; 1802INSERT INTO t1 SELECT id+256 FROM t1; 1803INSERT INTO t1 SELECT id+512 FROM t1; 1804INSERT INTO t1 SELECT id+1024 FROM t1; 1805INSERT INTO t1 SELECT id+2048 FROM t1; 1806INSERT INTO t1 SELECT id+4096 FROM t1; 1807INSERT INTO t1 SELECT id+9192 FROM t1; 1808# 1809# This query will should show a full table scan analysis. 1810# 1811ANALYZE TABLE t1; 1812Table Op Msg_type Msg_text 1813test.t1 analyze status Engine-independent statistics collected 1814test.t1 analyze status OK 1815select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, 1816DECODE_HISTOGRAM(hist_type, histogram) 1817from mysql.column_stats; 1818table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) 1819t1 id 1 17384 0.0000 4.0000 14.0000 0.15705,0.15711,0.21463,0.15705,0.15711,0.15706 1820set analyze_sample_percentage=0.1; 1821# 1822# This query will show an innacurate avg_frequency value. 1823# 1824ANALYZE TABLE t1; 1825Table Op Msg_type Msg_text 1826test.t1 analyze status Engine-independent statistics collected 1827test.t1 analyze status Table is already up to date 1828select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, 1829DECODE_HISTOGRAM(hist_type, histogram) 1830from mysql.column_stats; 1831table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) 1832t1 id 111 17026 0.0000 4.0000 10.4739 0.13649,0.14922,0.16921,0.21141,0.18355,0.15012 1833# 1834# This query will show a better avg_frequency value. 1835# 1836set analyze_sample_percentage=25; 1837ANALYZE TABLE t1; 1838Table Op Msg_type Msg_text 1839test.t1 analyze status Engine-independent statistics collected 1840test.t1 analyze status Table is already up to date 1841select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, 1842DECODE_HISTOGRAM(hist_type, histogram) 1843from mysql.column_stats; 1844table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) 1845t1 id 1 17384 0.0000 4.0000 14.0401 0.15566,0.15590,0.15729,0.21538,0.15790,0.15787 1846set analyze_sample_percentage=0; 1847# 1848# Test self adjusting sampling level. 1849# 1850ANALYZE TABLE t1; 1851Table Op Msg_type Msg_text 1852test.t1 analyze status Engine-independent statistics collected 1853test.t1 analyze status Table is already up to date 1854select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, 1855DECODE_HISTOGRAM(hist_type, histogram) 1856from mysql.column_stats; 1857table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) 1858t1 id 1 17384 0.0000 4.0000 13.9812 0.15860,0.15767,0.21515,0.15573,0.15630,0.15654 1859# 1860# Test record estimation is working properly. 1861# 1862select count(*) from t1; 1863count(*) 1864229376 1865explain select * from t1; 1866id select_type table type possible_keys key key_len ref rows Extra 18671 SIMPLE t1 ALL NULL NULL NULL NULL 229060 1868set analyze_sample_percentage=100; 1869ANALYZE TABLE t1; 1870Table Op Msg_type Msg_text 1871test.t1 analyze status Engine-independent statistics collected 1872test.t1 analyze status Table is already up to date 1873select table_name, column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency, 1874DECODE_HISTOGRAM(hist_type, histogram) 1875from mysql.column_stats; 1876table_name column_name min_value max_value nulls_ratio avg_length avg_frequency DECODE_HISTOGRAM(hist_type, histogram) 1877t1 id 1 17384 0.0000 4.0000 14.0000 0.15705,0.15711,0.21463,0.15705,0.15711,0.15706 1878explain select * from t1; 1879id select_type table type possible_keys key key_len ref rows Extra 18801 SIMPLE t1 ALL NULL NULL NULL NULL 229376 1881drop table t1; 1882set analyze_sample_percentage=@save_analyze_sample_percentage; 1883set histogram_size=@save_histogram_size; 1884set use_stat_tables=@save_use_stat_tables; 1885set @@global.histogram_size=@save_histogram_size; 1886