Debian Codegraph @

mysql-test/suite/innodb/include/innodb_bulk_create_index.inc

raw
1 #
2 # wl#7277: InnoDB: Bulk Load for Create Index
3 #
4
5 # Create Insert Procedure
6 DELIMITER |;
7 CREATE PROCEDURE populate_t1(load_even INT)
8 BEGIN
9 DECLARE i int DEFAULT 1;
10
11 START TRANSACTION;
12 WHILE (i <= 10000) DO
13 IF i%2 = 0 AND load_even = 1 THEN
14 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
15 END IF;
16 IF i%2 != 0 AND load_even != 1 THEN
17 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
18 END IF;
19 SET i = i + 1;
20 END WHILE;
21 COMMIT;
22 END|
23 DELIMITER ;|
24
25 SELECT @@innodb_fill_factor;
26
27 if ($row_format != 'COMPRESSED')
28 {
29 eval CREATE TABLE t1(
30 class INT,
31 id INT,
32 title VARCHAR(100)
33 ) ENGINE=InnoDB ROW_FORMAT=$row_format;
34 }
35
36 if ($row_format == 'COMPRESSED')
37 {
38 SET GLOBAL innodb_file_per_table=1;
39
40 eval CREATE TABLE t1(
41 class INT,
42 id INT,
43 title VARCHAR(100)
44 ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4;
45 }
46
47 -- disable_query_log
48 # Load half records
49 CALL populate_t1(1);
50 -- enable_query_log
51
52 SELECT COUNT(*) FROM t1;
53
54 /* Create index. */
55 CREATE INDEX idx_id ON t1(id);
56
57 CREATE INDEX idx_title ON t1(title);
58
59 /* Check table. */
60 CHECK TABLE t1;
61
62 /* Select by index. */
63 EXPLAIN SELECT * FROM t1 WHERE id = 10;
64 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
65
66 SELECT * FROM t1 WHERE id = 10;
67 SELECT * FROM t1 WHERE title = 'a10';
68
69 SELECT * FROM t1 WHERE id = 5000;
70 SELECT * FROM t1 WHERE title = 'a5000';
71
72 SELECT * FROM t1 WHERE id = 10000;
73 SELECT * FROM t1 WHERE title = 'a10000';
74
75 SELECT * FROM t1 WHERE id = 10010;
76 SELECT * FROM t1 WHERE title = 'a10010';
77
78 /*Insert/Update/Delete. */
79 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
80 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
81 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
82
83 SELECT * FROM t1 WHERE id = 3000;
84 SELECT * FROM t1 WHERE title = 'a3000';
85 SELECT * FROM t1 WHERE title = 'b3000';
86
87 SELECT * FROM t1 WHERE id = 4000;
88 SELECT * FROM t1 WHERE title = 'a4000';
89 SELECT * FROM t1 WHERE title = 'b4000';
90
91 SELECT * FROM t1 WHERE id = 4001;
92 SELECT * FROM t1 WHERE title = 'a4001';
93
94 -- disable_query_log
95 # Load half records (follow up load)
96 CALL populate_t1(0);
97 -- enable_query_log
98 SELECT COUNT(*) FROM t1;
99
100
101 /* Add column. */
102 ALTER TABLE t1 ADD COLUMN content TEXT;
103
104 CHECK TABLE t1;
105
106 SELECT * FROM t1 WHERE id = 10;
107 SELECT * FROM t1 WHERE title = 'a10';
108
109 SELECT * FROM t1 WHERE id = 5000;
110 SELECT * FROM t1 WHERE title = 'a5000';
111
112 SELECT * FROM t1 WHERE id = 10000;
113 SELECT * FROM t1 WHERE title = 'a10000';
114
115 SELECT * FROM t1 WHERE id = 10010;
116 SELECT * FROM t1 WHERE title = 'a10010';
117
118 /* Drop column. */
119 ALTER TABLE t1 DROP COLUMN content;
120
121 CHECK TABLE t1;
122
123 SELECT * FROM t1 WHERE id = 10;
124 SELECT * FROM t1 WHERE title = 'a10';
125
126 SELECT * FROM t1 WHERE id = 5000;
127 SELECT * FROM t1 WHERE title = 'a5000';
128
129 SELECT * FROM t1 WHERE id = 10000;
130 SELECT * FROM t1 WHERE title = 'a10000';
131
132 SELECT * FROM t1 WHERE id = 10010;
133 SELECT * FROM t1 WHERE title = 'a10010';
134
135 DROP TABLE t1;
136
137 # Test Blob
138 if ($row_format != 'COMPRESSED') {
139 eval CREATE TABLE t1(
140 a INT PRIMARY KEY,
141 b TEXT,
142 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format;
143 }
144
145 if ($row_format == 'COMPRESSED') {
146 eval CREATE TABLE t1(
147 a INT PRIMARY KEY,
148 b BLOB,
149 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4;
150 }
151
152 let $cnt= 5000;
153 -- disable_query_log
154 WHILE ($cnt>=4950)
155 {
156 EVAL INSERT INTO t1 VALUES
157 ($cnt, REPEAT(CONCAT('a', $cnt),2000), CONCAT('a', $cnt));
158 dec $cnt;
159 }
160 -- enable_query_log
161 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
162
163 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
164 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
165 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
166 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
167 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
168 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
169 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
170
171 ALTER TABLE t1 DROP COLUMN c;
172
173 CHECK TABLE t1;
174
175 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
176
177 DROP TABLE t1;
178
179 # Restore global variables
180 if ($row_format == 'COMPRESSED')
181 {
182 SET GLOBAL innodb_file_per_table=default;
183 }
184
185 DROP PROCEDURE populate_t1;