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