Debian Codegraph @

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

raw
1 # #############################################################
2 # wl6747 : Set merge threshold at index level
3 # Check with CREATE INDEX on all datatypes
4 # Check with by ALTER TABLE MODIFY COLUMN TYPE
5 # Check with ALTER TABLE ADD Index
6 # Check by setting at index level with CREATE TABLE
7 # Check with BLOB column at index level with CREATE Index
8 # Check with row_format=compressed and key_block_size=8k
9 # Check withe valid and invalid merge_threshold values.
10 #
11 # Check actual behavior for table, partitioned table and temporary table
12 # #############################################################
13 --source include/have_debug.inc
14
15 # purge the previous test results
16 --disable_query_log
17 set global innodb_purge_stop_now=ON;
18 set global innodb_purge_run_now=ON;
19 --enable_query_log
20 --source include/wait_innodb_all_purged.inc
21
22 # Check index merge threshold by create index on all datatypes
23
24 CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT,
25 c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) charset latin1 ENGINE=InnoDB STATS_PERSISTENT=0;
26
27 # check index merge threshold on all datatypes
28 CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51';
29 CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1';
30 CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20';
31 CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25';
32 CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30';
33 CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35';
34 CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40';
35
36 SHOW CREATE TABLE tab;
37
38 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
39 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
40 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
41
42 ALTER TABLE tab comment='MERGE_THRESHOLD=49';
43
44 SHOW CREATE TABLE tab;
45
46 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
47 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
48 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
49
50 ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ;
51
52 SHOW CREATE TABLE tab;
53
54 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
55 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
56 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
57
58 ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45';
59
60 SHOW CREATE TABLE tab;
61
62 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
63 FROM INFORMATION_SCHEMA.INNODB_TABLES t, INFORMATION_SCHEMA.INNODB_INDEXES i
64 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab' ORDER BY i.NAME;
65 # Cleanup
66 DROP TABLE tab;
67
68 --echo #
69 --echo # behavior for deleting records
70 --echo #
71
72 --echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
73 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0;
74
75 # Force purge
76 --source suite/innodb/include/force_purge.inc
77 --source suite/innodb/include/innodb_merge_threshold_delete.inc
78
79 DROP TABLE tab1;
80
81
82 --echo # test to confirm behavior (MERGE_THRESHOLD=35)
83 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0
84 COMMENT='MERGE_THRESHOLD=35';
85
86 # Force purge
87 --source suite/innodb/include/force_purge.inc
88 --source suite/innodb/include/innodb_merge_threshold_delete.inc
89
90 DROP TABLE tab1;
91
92
93 --echo # test to confirm behavior (MERGE_THRESHOLD=25)
94 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0
95 COMMENT='MERGE_THRESHOLD=25';
96
97 # Force purge
98 --source suite/innodb/include/force_purge.inc
99 --source suite/innodb/include/innodb_merge_threshold_delete.inc
100
101 DROP TABLE tab1;
102
103
104 --echo # test to confirm partitioned table (MERGE_THRESHOLD=35)
105 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) STATS_PERSISTENT=0
106 COMMENT='MERGE_THRESHOLD=35'
107 PARTITION BY RANGE (a)
108 (PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
109 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
110
111 # Force purge
112 --source suite/innodb/include/force_purge.inc
113 --source suite/innodb/include/innodb_merge_threshold_delete.inc
114
115 DROP TABLE tab1;
116
117
118
119 --echo #
120 --echo # behavior for updating to smaller records
121 --echo #
122
123 --echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
124 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB STATS_PERSISTENT=0;
125
126 # Force purge
127 --source suite/innodb/include/force_purge.inc
128 --source suite/innodb/include/innodb_merge_threshold_update.inc
129
130 DROP TABLE tab1;
131
132 --echo # test to confirm behavior (MERGE_THRESHOLD=35)
133 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
134 COMMENT='MERGE_THRESHOLD=35' STATS_PERSISTENT=0;
135
136 # Force purge
137 --source suite/innodb/include/force_purge.inc
138 --source suite/innodb/include/innodb_merge_threshold_update.inc
139
140 DROP TABLE tab1;
141
142
143 --echo # test to confirm behavior (MERGE_THRESHOLD=25)
144 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
145 COMMENT='MERGE_THRESHOLD=25' STATS_PERSISTENT=0;
146
147 # Force purge
148 --source suite/innodb/include/force_purge.inc
149 --source suite/innodb/include/innodb_merge_threshold_update.inc
150
151 DROP TABLE tab1;
152
153
154 --echo # test to confirm explicit temporary table (MERGE_THRESHOLD=35)
155 --echo # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly)
156
157 CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
158 COMMENT='MERGE_THRESHOLD=35' STATS_PERSISTENT=0;
159
160 --source suite/innodb/include/force_purge.inc
161 --source suite/innodb/include/innodb_merge_threshold_update.inc
162
163 DROP TABLE tab1;
164
165 --echo #
166 --echo # behavior for secondary index with blob
167 --echo #
168
169 --echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
170 # not to cause page operation at primary key, row_format=dynamic and the key is blob
171 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
172 CREATE INDEX index1 ON tab1(b(750));
173
174 --source suite/innodb/include/force_purge.inc
175 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
176
177 DROP TABLE tab1;
178
179
180 --echo # test to confirm behavior (MERGE_THRESHOLD=45)
181 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
182 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
183
184 --source suite/innodb/include/force_purge.inc
185 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
186
187 DROP TABLE tab1;
188
189
190 --echo # test to confirm behavior (MERGE_THRESHOLD=40)
191 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic STATS_PERSISTENT=0;
192 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
193
194 --source suite/innodb/include/force_purge.inc
195 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
196
197 DROP TABLE tab1;
198
199
200 --echo # compressed table behaves same (MERGE_THRESHOLD=45)
201 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB STATS_PERSISTENT=0
202 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
203 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
204
205 --source suite/innodb/include/force_purge.inc
206 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
207
208 DROP TABLE tab1;
209