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 eval CREATE TABLE t1(
39 class INT,
40 id INT,
41 title VARCHAR(100)
42 ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4;
43 }
44
45 -- disable_query_log
46 # Load half records
47 CALL populate_t1(1);
48 -- enable_query_log
49
50 SELECT COUNT(*) FROM t1;
51
52 /* Create index. */
53 CREATE INDEX idx_id ON t1(id);
54
55 CREATE INDEX idx_title ON t1(title);
56
57 /* Check table. */
58 CHECK TABLE t1;
59
60 /* Select by index. */
61 EXPLAIN SELECT * FROM t1 WHERE id = 10;
62 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
63
64 SELECT * FROM t1 WHERE id = 10;
65 SELECT * FROM t1 WHERE title = 'a10';
66
67 SELECT * FROM t1 WHERE id = 5000;
68 SELECT * FROM t1 WHERE title = 'a5000';
69
70 SELECT * FROM t1 WHERE id = 10000;
71 SELECT * FROM t1 WHERE title = 'a10000';
72
73 SELECT * FROM t1 WHERE id = 10010;
74 SELECT * FROM t1 WHERE title = 'a10010';
75
76 /*Insert/Update/Delete. */
77 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
78 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
79 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
80
81 SELECT * FROM t1 WHERE id = 3000;
82 SELECT * FROM t1 WHERE title = 'a3000';
83 SELECT * FROM t1 WHERE title = 'b3000';
84
85 SELECT * FROM t1 WHERE id = 4000;
86 SELECT * FROM t1 WHERE title = 'a4000';
87 SELECT * FROM t1 WHERE title = 'b4000';
88
89 SELECT * FROM t1 WHERE id = 4001;
90 SELECT * FROM t1 WHERE title = 'a4001';
91
92 -- disable_query_log
93 # Load half records (follow up load)
94 CALL populate_t1(0);
95 -- enable_query_log
96 SELECT COUNT(*) FROM t1;
97
98
99 /* Add column. */
100 ALTER TABLE t1 ADD COLUMN content TEXT;
101
102 CHECK TABLE t1;
103
104 SELECT * FROM t1 WHERE id = 10;
105 SELECT * FROM t1 WHERE title = 'a10';
106
107 SELECT * FROM t1 WHERE id = 5000;
108 SELECT * FROM t1 WHERE title = 'a5000';
109
110 SELECT * FROM t1 WHERE id = 10000;
111 SELECT * FROM t1 WHERE title = 'a10000';
112
113 SELECT * FROM t1 WHERE id = 10010;
114 SELECT * FROM t1 WHERE title = 'a10010';
115
116 /* Drop column. */
117 ALTER TABLE t1 DROP COLUMN content;
118
119 CHECK TABLE t1;
120
121 SELECT * FROM t1 WHERE id = 10;
122 SELECT * FROM t1 WHERE title = 'a10';
123
124 SELECT * FROM t1 WHERE id = 5000;
125 SELECT * FROM t1 WHERE title = 'a5000';
126
127 SELECT * FROM t1 WHERE id = 10000;
128 SELECT * FROM t1 WHERE title = 'a10000';
129
130 SELECT * FROM t1 WHERE id = 10010;
131 SELECT * FROM t1 WHERE title = 'a10010';
132
133 DROP TABLE t1;
134
135 # Test Blob
136 if ($row_format != 'COMPRESSED') {
137 eval CREATE TABLE t1(
138 a INT PRIMARY KEY,
139 b TEXT,
140 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format;
141 }
142
143 if ($row_format == 'COMPRESSED') {
144 eval CREATE TABLE t1(
145 a INT PRIMARY KEY,
146 b BLOB,
147 c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4;
148 }
149
150 let $cnt= 5000;
151 -- disable_query_log
152 WHILE ($cnt>=4950)
153 {
154 EVAL INSERT INTO t1 VALUES
155 ($cnt, REPEAT(CONCAT('a', $cnt),2000), CONCAT('a', $cnt));
156 dec $cnt;
157 }
158 -- enable_query_log
159 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
160
161 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
162 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
163 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
164 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
165 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
166 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
167 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
168
169 ALTER TABLE t1 DROP COLUMN c;
170
171 CHECK TABLE t1;
172
173 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
174
175 DROP TABLE t1;
176
177 DROP PROCEDURE populate_t1;