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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
CREATE TABLE t (
a INT,
b INT,
c INT GENERATED ALWAYS AS(a+b),
d INT GENERATED ALWAYS AS(a+b+b),
KEY idxa (a),
KEY vidxcd (c, d)
) ENGINE=INNODB STATS_PERSISTENT=1 STATS_AUTO_RECALC=1;
INSERT INTO t (a,b) VALUES (1, 2);
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxa n_diff_pfx01 a
idxa n_diff_pfx02 a,DB_ROW_ID
idxa n_leaf_pages Number of leaf pages in the index
idxa size Number of pages in the index
vidxcd n_diff_pfx01 c
vidxcd n_diff_pfx02 c,d
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
vidxcd n_leaf_pages Number of leaf pages in the index
vidxcd size Number of pages in the index
ALTER TABLE t ADD COLUMN e INT GENERATED ALWAYS AS(a+a+b), ADD INDEX idxb (b), ALGORITHM=INPLACE;
select count(*) from t;
count(*)
1
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxa n_diff_pfx01 a
idxa n_diff_pfx02 a,DB_ROW_ID
idxa n_leaf_pages Number of leaf pages in the index
idxa size Number of pages in the index
idxb n_diff_pfx01 b
idxb n_diff_pfx02 b,DB_ROW_ID
idxb n_leaf_pages Number of leaf pages in the index
idxb size Number of pages in the index
vidxcd n_diff_pfx01 c
vidxcd n_diff_pfx02 c,d
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
vidxcd n_leaf_pages Number of leaf pages in the index
vidxcd size Number of pages in the index
ALTER TABLE t DROP COLUMN c, DROP INDEX idxa, ALGORITHM=INPLACE;
select count(*) from t;
count(*)
1
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxb n_diff_pfx01 b
idxb n_diff_pfx02 b,DB_ROW_ID
idxb n_leaf_pages Number of leaf pages in the index
idxb size Number of pages in the index
vidxcd n_diff_pfx01 d
vidxcd n_diff_pfx02 d,DB_ROW_ID
vidxcd n_leaf_pages Number of leaf pages in the index
vidxcd size Number of pages in the index
ALTER TABLE t ADD INDEX vidxe (e), ALGORITHM=INPLACE;
select count(*) from t;
count(*)
1
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxb n_diff_pfx01 b
idxb n_diff_pfx02 b,DB_ROW_ID
idxb n_leaf_pages Number of leaf pages in the index
idxb size Number of pages in the index
vidxcd n_diff_pfx01 d
vidxcd n_diff_pfx02 d,DB_ROW_ID
vidxcd n_leaf_pages Number of leaf pages in the index
vidxcd size Number of pages in the index
vidxe n_diff_pfx01 e
vidxe n_diff_pfx02 e,DB_ROW_ID
vidxe n_leaf_pages Number of leaf pages in the index
vidxe size Number of pages in the index
ALTER TABLE t ADD COLUMN f INT GENERATED ALWAYS AS(a + a), ADD INDEX vidxf (f), ALGORITHM=INPLACE;
select count(*) from t;
count(*)
1
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxb n_diff_pfx01 b
idxb n_diff_pfx02 b,DB_ROW_ID
idxb n_leaf_pages Number of leaf pages in the index
idxb size Number of pages in the index
vidxcd n_diff_pfx01 d
vidxcd n_diff_pfx02 d,DB_ROW_ID
vidxcd n_leaf_pages Number of leaf pages in the index
vidxcd size Number of pages in the index
vidxe n_diff_pfx01 e
vidxe n_diff_pfx02 e,DB_ROW_ID
vidxe n_leaf_pages Number of leaf pages in the index
vidxe size Number of pages in the index
vidxf n_diff_pfx01 f
vidxf n_diff_pfx02 f,DB_ROW_ID
vidxf n_leaf_pages Number of leaf pages in the index
vidxf size Number of pages in the index
ALTER TABLE t DROP INDEX vidxcd;
SELECT index_name, stat_name, stat_description
FROM mysql.innodb_index_stats
WHERE database_name = 'test' AND table_name = 't';
index_name stat_name stat_description
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
GEN_CLUST_INDEX size Number of pages in the index
idxb n_diff_pfx01 b
idxb n_diff_pfx02 b,DB_ROW_ID
idxb n_leaf_pages Number of leaf pages in the index
idxb size Number of pages in the index
vidxe n_diff_pfx01 e
vidxe n_diff_pfx02 e,DB_ROW_ID
vidxe n_leaf_pages Number of leaf pages in the index
vidxe size Number of pages in the index
vidxf n_diff_pfx01 f
vidxf n_diff_pfx02 f,DB_ROW_ID
vidxf n_leaf_pages Number of leaf pages in the index
vidxf size Number of pages in the index
DROP TABLE t;
|
