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_innodb.inc
14 --source include/have_innodb_16k.inc
15 --source include/have_partition.inc
16
17 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
18 SET GLOBAL innodb_stats_persistent = 0;
19
20 # Check index merge threshold by create index on all datatypes
21
22 CREATE TABLE tab(a BIGINT PRIMARY KEY,c1 TINYTEXT,c2 TEXT,c3 MEDIUMTEXT,
23 c4 TINYBLOB,c5 BLOB,c6 MEDIUMBLOB,c7 LONGBLOB) ENGINE=InnoDB;
24
25 # check index merge threshold on all datatypes
26 CREATE INDEX index1 ON tab(c1(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=51';
27 CREATE INDEX index2 ON tab(c2(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=-1';
28 CREATE INDEX index3 ON tab(c3(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=20';
29 CREATE INDEX index4 ON tab(c4(255)) COMMENT 'Check index level merge MERGE_THRESHOLD=25';
30 CREATE INDEX index5 ON tab(c5(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=30';
31 CREATE INDEX index6 ON tab(c6(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=35';
32 CREATE INDEX index7 ON tab(c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=40';
33
34 SHOW CREATE TABLE tab;
35
36 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
37 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
38 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
39
40 ALTER TABLE tab comment='MERGE_THRESHOLD=49';
41
42 SHOW CREATE TABLE tab;
43
44 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
45 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
46 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
47
48 ALTER TABLE tab MODIFY COLUMN c7 VARCHAR(2048) ;
49
50 SHOW CREATE TABLE tab;
51
52 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
53 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
54 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
55
56 ALTER TABLE tab ADD INDEX index8 (c7(750)) COMMENT 'Check index level merge MERGE_THRESHOLD=45';
57
58 SHOW CREATE TABLE tab;
59
60 SELECT t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD
61 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
62 WHERE t.TABLE_ID = i.TABLE_ID AND t.NAME = 'test/tab';
63 # Cleanup
64 DROP TABLE tab;
65
66
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;
74
75 --source suite/innodb/include/innodb_merge_threshold_delete.inc
76
77 DROP TABLE tab1;
78
79
80 --echo # test to confirm behavior (MERGE_THRESHOLD=35)
81 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
82 COMMENT='MERGE_THRESHOLD=35';
83
84 --source suite/innodb/include/innodb_merge_threshold_delete.inc
85
86 DROP TABLE tab1;
87
88
89 --echo # test to confirm behavior (MERGE_THRESHOLD=25)
90 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
91 COMMENT='MERGE_THRESHOLD=25';
92
93 --source suite/innodb/include/innodb_merge_threshold_delete.inc
94
95 DROP TABLE tab1;
96
97
98 --echo # test to confirm partitioned table (MERGE_THRESHOLD=35)
99 CREATE TABLE tab1 (a bigint primary key, b varchar(2048))
100 COMMENT='MERGE_THRESHOLD=35'
101 PARTITION BY RANGE (a)
102 (PARTITION p0 VALUES LESS THAN (20) ENGINE = InnoDB,
103 PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
104
105 --source suite/innodb/include/innodb_merge_threshold_delete.inc
106
107 DROP TABLE tab1;
108
109
110
111 --echo #
112 --echo # behavior for updating to smaller records
113 --echo #
114
115 --echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
116 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;
117
118 --source suite/innodb/include/innodb_merge_threshold_update.inc
119
120 DROP TABLE tab1;
121
122
123 --echo # test to confirm behavior (MERGE_THRESHOLD=35)
124 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
125 COMMENT='MERGE_THRESHOLD=35';
126
127 --source suite/innodb/include/innodb_merge_threshold_update.inc
128
129 DROP TABLE tab1;
130
131
132 --echo # test to confirm behavior (MERGE_THRESHOLD=25)
133 CREATE TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
134 COMMENT='MERGE_THRESHOLD=25';
135
136 --source suite/innodb/include/innodb_merge_threshold_update.inc
137
138 DROP TABLE tab1;
139
140
141 --echo # test to confirm explicit temporary table (MERGE_THRESHOLD=35)
142 --echo # (though not registered to SYS_TABLES,SYS_INDEXES, it works correctly)
143 # Temporary tables are not purged. so deleting records is not caused
144 # So, should be tested by updating case only
145 CREATE TEMPORARY TABLE tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB
146 COMMENT='MERGE_THRESHOLD=35';
147
148 --source suite/innodb/include/innodb_merge_threshold_update.inc
149
150 DROP TABLE tab1;
151
152
153 --echo #
154 --echo # behavior for secondary index with blob
155 --echo #
156
157 --echo # test to confirm behavior (MERGE_THRESHOLD=50 (default))
158 # not to cause page operation at primary key, row_format=dynamic and the key is blob
159 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
160 CREATE INDEX index1 ON tab1(b(750));
161
162 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
163
164 DROP TABLE tab1;
165
166
167 --echo # test to confirm behavior (MERGE_THRESHOLD=45)
168 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
169 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
170
171 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
172
173 DROP TABLE tab1;
174
175
176 --echo # test to confirm behavior (MERGE_THRESHOLD=40)
177 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;
178 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=40';
179
180 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
181
182 DROP TABLE tab1;
183
184
185 --echo # compressed table behaves same (MERGE_THRESHOLD=45)
186 CREATE TABLE tab1 (a bigint primary key, b blob) engine=InnoDB
187 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
188 CREATE INDEX index1 ON tab1(b(750)) COMMENT 'MERGE_THRESHOLD=45';
189
190 --source suite/innodb/include/innodb_merge_threshold_secondary.inc
191
192 DROP TABLE tab1;
193
194 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;