Debian Codegraph @

mysql-test/suite/innodb/t/innodb_stats_fetch.test

raw
1 #
2 # Test fetching from stats tables after manual update (ie FLUSH TABLE
3 # can be used to forcibly (re)fetch the stats from disk)
4 #
5
6 # Various sizes printed in this test depend on the page size and the
7 # functionality tested here is not related to the page size, so we only
8 # test with 16k page size.
9
10 -- vertical_results
11
12 CREATE TABLE test_ps_fetch
13 (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
14 ENGINE=INNODB STATS_PERSISTENT=1;
15
16 ANALYZE TABLE test_ps_fetch;
17
18 SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
19 FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
20
21 SELECT index_name, stat_name, stat_value, sample_size, stat_description
22 FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
23 ORDER BY index_name, stat_name;
24
25 SELECT index_name, seq_in_index, column_name, cardinality
26 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
27 ORDER BY index_name, seq_in_index;
28
29 SELECT
30 table_rows, avg_row_length, max_data_length, index_length
31 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
32
33 UPDATE mysql.innodb_table_stats SET
34 n_rows = 1000,
35 clustered_index_size = 5
36 WHERE
37 table_name = 'test_ps_fetch';
38
39 UPDATE mysql.innodb_index_stats SET
40 stat_value = 20
41 WHERE
42 table_name = 'test_ps_fetch' AND
43 index_name = 'PRIMARY' AND
44 stat_name = 'n_diff_pfx01';
45
46 UPDATE mysql.innodb_index_stats SET
47 stat_value = 90
48 WHERE
49 table_name = 'test_ps_fetch' AND
50 index_name = 'PRIMARY' AND
51 stat_name = 'n_diff_pfx02';
52
53 UPDATE mysql.innodb_index_stats SET
54 stat_value = 3
55 WHERE
56 table_name = 'test_ps_fetch' AND
57 index_name = 'idx' AND
58 stat_name = 'n_diff_pfx01';
59
60 UPDATE mysql.innodb_index_stats SET
61 stat_value = 11
62 WHERE
63 table_name = 'test_ps_fetch' AND
64 index_name = 'idx' AND
65 stat_name = 'n_diff_pfx02';
66
67 FLUSH TABLE test_ps_fetch;
68
69 SET SESSION information_schema_stats_expiry=0;
70 SELECT seq_in_index, column_name, cardinality
71 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
72 ORDER BY index_name, seq_in_index;
73
74 SELECT
75 table_rows, avg_row_length, max_data_length, index_length
76 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
77
78 DROP TABLE test_ps_fetch;
79 SET SESSION information_schema_stats_expiry=default;