Debian Codegraph @

mysql-test/suite/innodb/r/innodb_stats_fetch.result

raw
1 CREATE TABLE test_ps_fetch
2 (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d))
3 ENGINE=INNODB STATS_PERSISTENT=1;
4 ANALYZE TABLE test_ps_fetch;
5 Table test.test_ps_fetch
6 Op analyze
7 Msg_type status
8 Msg_text OK
9 SELECT n_rows, clustered_index_size, sum_of_other_index_sizes
10 FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_fetch';
11 n_rows 0
12 clustered_index_size 1
13 sum_of_other_index_sizes 1
14 SELECT index_name, stat_name, stat_value, sample_size, stat_description
15 FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_fetch'
16 ORDER BY index_name, stat_name;
17 index_name PRIMARY
18 stat_name n_diff_pfx01
19 stat_value 0
20 sample_size 1
21 stat_description a
22 index_name PRIMARY
23 stat_name n_diff_pfx02
24 stat_value 0
25 sample_size 1
26 stat_description a,b
27 index_name PRIMARY
28 stat_name n_leaf_pages
29 stat_value 1
30 sample_size NULL
31 stat_description Number of leaf pages in the index
32 index_name PRIMARY
33 stat_name size
34 stat_value 1
35 sample_size NULL
36 stat_description Number of pages in the index
37 index_name idx
38 stat_name n_diff_pfx01
39 stat_value 0
40 sample_size 1
41 stat_description c
42 index_name idx
43 stat_name n_diff_pfx02
44 stat_value 0
45 sample_size 1
46 stat_description c,d
47 index_name idx
48 stat_name n_diff_pfx03
49 stat_value 0
50 sample_size 1
51 stat_description c,d,a
52 index_name idx
53 stat_name n_diff_pfx04
54 stat_value 0
55 sample_size 1
56 stat_description c,d,a,b
57 index_name idx
58 stat_name n_leaf_pages
59 stat_value 1
60 sample_size NULL
61 stat_description Number of leaf pages in the index
62 index_name idx
63 stat_name size
64 stat_value 1
65 sample_size NULL
66 stat_description Number of pages in the index
67 SELECT index_name, seq_in_index, column_name, cardinality
68 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
69 ORDER BY index_name, seq_in_index;
70 INDEX_NAME idx
71 SEQ_IN_INDEX 1
72 COLUMN_NAME c
73 CARDINALITY 0
74 INDEX_NAME idx
75 SEQ_IN_INDEX 2
76 COLUMN_NAME d
77 CARDINALITY 0
78 INDEX_NAME PRIMARY
79 SEQ_IN_INDEX 1
80 COLUMN_NAME a
81 CARDINALITY 0
82 INDEX_NAME PRIMARY
83 SEQ_IN_INDEX 2
84 COLUMN_NAME b
85 CARDINALITY 0
86 SELECT
87 table_rows, avg_row_length, max_data_length, index_length
88 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
89 TABLE_ROWS 0
90 AVG_ROW_LENGTH 0
91 MAX_DATA_LENGTH 0
92 INDEX_LENGTH 16384
93 UPDATE mysql.innodb_table_stats SET
94 n_rows = 1000,
95 clustered_index_size = 5
96 WHERE
97 table_name = 'test_ps_fetch';
98 UPDATE mysql.innodb_index_stats SET
99 stat_value = 20
100 WHERE
101 table_name = 'test_ps_fetch' AND
102 index_name = 'PRIMARY' AND
103 stat_name = 'n_diff_pfx01';
104 UPDATE mysql.innodb_index_stats SET
105 stat_value = 90
106 WHERE
107 table_name = 'test_ps_fetch' AND
108 index_name = 'PRIMARY' AND
109 stat_name = 'n_diff_pfx02';
110 UPDATE mysql.innodb_index_stats SET
111 stat_value = 3
112 WHERE
113 table_name = 'test_ps_fetch' AND
114 index_name = 'idx' AND
115 stat_name = 'n_diff_pfx01';
116 UPDATE mysql.innodb_index_stats SET
117 stat_value = 11
118 WHERE
119 table_name = 'test_ps_fetch' AND
120 index_name = 'idx' AND
121 stat_name = 'n_diff_pfx02';
122 FLUSH TABLE test_ps_fetch;
123 SET SESSION information_schema_stats_expiry=0;
124 SELECT seq_in_index, column_name, cardinality
125 FROM information_schema.statistics WHERE table_name = 'test_ps_fetch'
126 ORDER BY index_name, seq_in_index;
127 SEQ_IN_INDEX 1
128 COLUMN_NAME c
129 CARDINALITY 3
130 SEQ_IN_INDEX 2
131 COLUMN_NAME d
132 CARDINALITY 11
133 SEQ_IN_INDEX 1
134 COLUMN_NAME a
135 CARDINALITY 20
136 SEQ_IN_INDEX 2
137 COLUMN_NAME b
138 CARDINALITY 90
139 SELECT
140 table_rows, avg_row_length, max_data_length, index_length
141 FROM information_schema.tables WHERE table_name = 'test_ps_fetch';
142 TABLE_ROWS 1000
143 AVG_ROW_LENGTH 81
144 MAX_DATA_LENGTH 0
145 INDEX_LENGTH 16384
146 DROP TABLE test_ps_fetch;
147 SET SESSION information_schema_stats_expiry=default;