Debian Codegraph @

mysql-test/suite/innodb/r/innodb_bulk_create_index.result

raw
1 CREATE PROCEDURE populate_t1(load_even INT)
2 BEGIN
3 DECLARE i int DEFAULT 1;
4 START TRANSACTION;
5 WHILE (i <= 10000) DO
6 IF i%2 = 0 AND load_even = 1 THEN
7 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
8 END IF;
9 IF i%2 != 0 AND load_even != 1 THEN
10 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
11 END IF;
12 SET i = i + 1;
13 END WHILE;
14 COMMIT;
15 END|
16 SELECT @@innodb_fill_factor;
17 @@innodb_fill_factor
18 100
19 CREATE TABLE t1(
20 class INT,
21 id INT,
22 title VARCHAR(100)
23 ) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
24 SELECT COUNT(*) FROM t1;
25 COUNT(*)
26 5000
27 /* Create index. */
28 CREATE INDEX idx_id ON t1(id);
29 CREATE INDEX idx_title ON t1(title);
30 /* Check table. */
31 CHECK TABLE t1;
32 Table Op Msg_type Msg_text
33 test.t1 check status OK
34 /* Select by index. */
35 EXPLAIN SELECT * FROM t1 WHERE id = 10;
36 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
37 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
38 Warnings:
39 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
40 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
41 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
42 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
43 Warnings:
44 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
45 SELECT * FROM t1 WHERE id = 10;
46 class id title
47 10 10 a10
48 SELECT * FROM t1 WHERE title = 'a10';
49 class id title
50 10 10 a10
51 SELECT * FROM t1 WHERE id = 5000;
52 class id title
53 5000 5000 a5000
54 SELECT * FROM t1 WHERE title = 'a5000';
55 class id title
56 5000 5000 a5000
57 SELECT * FROM t1 WHERE id = 10000;
58 class id title
59 10000 10000 a10000
60 SELECT * FROM t1 WHERE title = 'a10000';
61 class id title
62 10000 10000 a10000
63 SELECT * FROM t1 WHERE id = 10010;
64 class id title
65 SELECT * FROM t1 WHERE title = 'a10010';
66 class id title
67 /*Insert/Update/Delete. */
68 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
69 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
70 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
71 SELECT * FROM t1 WHERE id = 3000;
72 class id title
73 3000 3000 b3000
74 SELECT * FROM t1 WHERE title = 'a3000';
75 class id title
76 SELECT * FROM t1 WHERE title = 'b3000';
77 class id title
78 3000 3000 b3000
79 SELECT * FROM t1 WHERE id = 4000;
80 class id title
81 4000 4000 b4000
82 SELECT * FROM t1 WHERE title = 'a4000';
83 class id title
84 SELECT * FROM t1 WHERE title = 'b4000';
85 class id title
86 4000 4000 b4000
87 SELECT * FROM t1 WHERE id = 4001;
88 class id title
89 SELECT * FROM t1 WHERE title = 'a4001';
90 class id title
91 SELECT COUNT(*) FROM t1;
92 COUNT(*)
93 9992
94 /* Add column. */
95 ALTER TABLE t1 ADD COLUMN content TEXT;
96 CHECK TABLE t1;
97 Table Op Msg_type Msg_text
98 test.t1 check status OK
99 SELECT * FROM t1 WHERE id = 10;
100 class id title content
101 10 10 a10 NULL
102 SELECT * FROM t1 WHERE title = 'a10';
103 class id title content
104 10 10 a10 NULL
105 SELECT * FROM t1 WHERE id = 5000;
106 class id title content
107 5000 5000 a5000 NULL
108 SELECT * FROM t1 WHERE title = 'a5000';
109 class id title content
110 5000 5000 a5000 NULL
111 SELECT * FROM t1 WHERE id = 10000;
112 class id title content
113 10000 10000 a10000 NULL
114 SELECT * FROM t1 WHERE title = 'a10000';
115 class id title content
116 10000 10000 a10000 NULL
117 SELECT * FROM t1 WHERE id = 10010;
118 class id title content
119 SELECT * FROM t1 WHERE title = 'a10010';
120 class id title content
121 /* Drop column. */
122 ALTER TABLE t1 DROP COLUMN content;
123 CHECK TABLE t1;
124 Table Op Msg_type Msg_text
125 test.t1 check status OK
126 SELECT * FROM t1 WHERE id = 10;
127 class id title
128 10 10 a10
129 SELECT * FROM t1 WHERE title = 'a10';
130 class id title
131 10 10 a10
132 SELECT * FROM t1 WHERE id = 5000;
133 class id title
134 5000 5000 a5000
135 SELECT * FROM t1 WHERE title = 'a5000';
136 class id title
137 5000 5000 a5000
138 SELECT * FROM t1 WHERE id = 10000;
139 class id title
140 10000 10000 a10000
141 SELECT * FROM t1 WHERE title = 'a10000';
142 class id title
143 10000 10000 a10000
144 SELECT * FROM t1 WHERE id = 10010;
145 class id title
146 SELECT * FROM t1 WHERE title = 'a10010';
147 class id title
148 DROP TABLE t1;
149 CREATE TABLE t1(
150 a INT PRIMARY KEY,
151 b TEXT,
152 c TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
153 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
154 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
155 CHAR_LENGTH(b)
156 10000
157 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
158 b=REPEAT(CONCAT('a',4975),2000)
159 1
160 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
161 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
162 b=REPEAT(CONCAT('a',4975),2000)
163 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
164 b=REPEAT(CONCAT('b',4975),2000)
165 1
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 b=REPEAT(CONCAT('b',4975),2000)
169 ALTER TABLE t1 DROP COLUMN c;
170 CHECK TABLE t1;
171 Table Op Msg_type Msg_text
172 test.t1 check status OK
173 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
174 CHAR_LENGTH(b)
175 DROP TABLE t1;
176 DROP PROCEDURE populate_t1;
177 CREATE PROCEDURE populate_t1(load_even INT)
178 BEGIN
179 DECLARE i int DEFAULT 1;
180 START TRANSACTION;
181 WHILE (i <= 10000) DO
182 IF i%2 = 0 AND load_even = 1 THEN
183 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
184 END IF;
185 IF i%2 != 0 AND load_even != 1 THEN
186 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
187 END IF;
188 SET i = i + 1;
189 END WHILE;
190 COMMIT;
191 END|
192 SELECT @@innodb_fill_factor;
193 @@innodb_fill_factor
194 100
195 CREATE TABLE t1(
196 class INT,
197 id INT,
198 title VARCHAR(100)
199 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
200 SELECT COUNT(*) FROM t1;
201 COUNT(*)
202 5000
203 /* Create index. */
204 CREATE INDEX idx_id ON t1(id);
205 CREATE INDEX idx_title ON t1(title);
206 /* Check table. */
207 CHECK TABLE t1;
208 Table Op Msg_type Msg_text
209 test.t1 check status OK
210 /* Select by index. */
211 EXPLAIN SELECT * FROM t1 WHERE id = 10;
212 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
213 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
214 Warnings:
215 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
216 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
217 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
218 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
219 Warnings:
220 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
221 SELECT * FROM t1 WHERE id = 10;
222 class id title
223 10 10 a10
224 SELECT * FROM t1 WHERE title = 'a10';
225 class id title
226 10 10 a10
227 SELECT * FROM t1 WHERE id = 5000;
228 class id title
229 5000 5000 a5000
230 SELECT * FROM t1 WHERE title = 'a5000';
231 class id title
232 5000 5000 a5000
233 SELECT * FROM t1 WHERE id = 10000;
234 class id title
235 10000 10000 a10000
236 SELECT * FROM t1 WHERE title = 'a10000';
237 class id title
238 10000 10000 a10000
239 SELECT * FROM t1 WHERE id = 10010;
240 class id title
241 SELECT * FROM t1 WHERE title = 'a10010';
242 class id title
243 /*Insert/Update/Delete. */
244 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
245 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
246 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
247 SELECT * FROM t1 WHERE id = 3000;
248 class id title
249 3000 3000 b3000
250 SELECT * FROM t1 WHERE title = 'a3000';
251 class id title
252 SELECT * FROM t1 WHERE title = 'b3000';
253 class id title
254 3000 3000 b3000
255 SELECT * FROM t1 WHERE id = 4000;
256 class id title
257 4000 4000 b4000
258 SELECT * FROM t1 WHERE title = 'a4000';
259 class id title
260 SELECT * FROM t1 WHERE title = 'b4000';
261 class id title
262 4000 4000 b4000
263 SELECT * FROM t1 WHERE id = 4001;
264 class id title
265 SELECT * FROM t1 WHERE title = 'a4001';
266 class id title
267 SELECT COUNT(*) FROM t1;
268 COUNT(*)
269 9992
270 /* Add column. */
271 ALTER TABLE t1 ADD COLUMN content TEXT;
272 CHECK TABLE t1;
273 Table Op Msg_type Msg_text
274 test.t1 check status OK
275 SELECT * FROM t1 WHERE id = 10;
276 class id title content
277 10 10 a10 NULL
278 SELECT * FROM t1 WHERE title = 'a10';
279 class id title content
280 10 10 a10 NULL
281 SELECT * FROM t1 WHERE id = 5000;
282 class id title content
283 5000 5000 a5000 NULL
284 SELECT * FROM t1 WHERE title = 'a5000';
285 class id title content
286 5000 5000 a5000 NULL
287 SELECT * FROM t1 WHERE id = 10000;
288 class id title content
289 10000 10000 a10000 NULL
290 SELECT * FROM t1 WHERE title = 'a10000';
291 class id title content
292 10000 10000 a10000 NULL
293 SELECT * FROM t1 WHERE id = 10010;
294 class id title content
295 SELECT * FROM t1 WHERE title = 'a10010';
296 class id title content
297 /* Drop column. */
298 ALTER TABLE t1 DROP COLUMN content;
299 CHECK TABLE t1;
300 Table Op Msg_type Msg_text
301 test.t1 check status OK
302 SELECT * FROM t1 WHERE id = 10;
303 class id title
304 10 10 a10
305 SELECT * FROM t1 WHERE title = 'a10';
306 class id title
307 10 10 a10
308 SELECT * FROM t1 WHERE id = 5000;
309 class id title
310 5000 5000 a5000
311 SELECT * FROM t1 WHERE title = 'a5000';
312 class id title
313 5000 5000 a5000
314 SELECT * FROM t1 WHERE id = 10000;
315 class id title
316 10000 10000 a10000
317 SELECT * FROM t1 WHERE title = 'a10000';
318 class id title
319 10000 10000 a10000
320 SELECT * FROM t1 WHERE id = 10010;
321 class id title
322 SELECT * FROM t1 WHERE title = 'a10010';
323 class id title
324 DROP TABLE t1;
325 CREATE TABLE t1(
326 a INT PRIMARY KEY,
327 b TEXT,
328 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
329 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
330 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
331 CHAR_LENGTH(b)
332 10000
333 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
334 b=REPEAT(CONCAT('a',4975),2000)
335 1
336 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
337 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
338 b=REPEAT(CONCAT('a',4975),2000)
339 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
340 b=REPEAT(CONCAT('b',4975),2000)
341 1
342 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
343 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
344 b=REPEAT(CONCAT('b',4975),2000)
345 ALTER TABLE t1 DROP COLUMN c;
346 CHECK TABLE t1;
347 Table Op Msg_type Msg_text
348 test.t1 check status OK
349 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
350 CHAR_LENGTH(b)
351 DROP TABLE t1;
352 DROP PROCEDURE populate_t1;
353 CREATE PROCEDURE populate_t1(load_even INT)
354 BEGIN
355 DECLARE i int DEFAULT 1;
356 START TRANSACTION;
357 WHILE (i <= 10000) DO
358 IF i%2 = 0 AND load_even = 1 THEN
359 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
360 END IF;
361 IF i%2 != 0 AND load_even != 1 THEN
362 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
363 END IF;
364 SET i = i + 1;
365 END WHILE;
366 COMMIT;
367 END|
368 SELECT @@innodb_fill_factor;
369 @@innodb_fill_factor
370 100
371 CREATE TABLE t1(
372 class INT,
373 id INT,
374 title VARCHAR(100)
375 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
376 SELECT COUNT(*) FROM t1;
377 COUNT(*)
378 5000
379 /* Create index. */
380 CREATE INDEX idx_id ON t1(id);
381 CREATE INDEX idx_title ON t1(title);
382 /* Check table. */
383 CHECK TABLE t1;
384 Table Op Msg_type Msg_text
385 test.t1 check status OK
386 /* Select by index. */
387 EXPLAIN SELECT * FROM t1 WHERE id = 10;
388 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
389 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
390 Warnings:
391 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
392 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
393 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
394 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
395 Warnings:
396 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
397 SELECT * FROM t1 WHERE id = 10;
398 class id title
399 10 10 a10
400 SELECT * FROM t1 WHERE title = 'a10';
401 class id title
402 10 10 a10
403 SELECT * FROM t1 WHERE id = 5000;
404 class id title
405 5000 5000 a5000
406 SELECT * FROM t1 WHERE title = 'a5000';
407 class id title
408 5000 5000 a5000
409 SELECT * FROM t1 WHERE id = 10000;
410 class id title
411 10000 10000 a10000
412 SELECT * FROM t1 WHERE title = 'a10000';
413 class id title
414 10000 10000 a10000
415 SELECT * FROM t1 WHERE id = 10010;
416 class id title
417 SELECT * FROM t1 WHERE title = 'a10010';
418 class id title
419 /*Insert/Update/Delete. */
420 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
421 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
422 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
423 SELECT * FROM t1 WHERE id = 3000;
424 class id title
425 3000 3000 b3000
426 SELECT * FROM t1 WHERE title = 'a3000';
427 class id title
428 SELECT * FROM t1 WHERE title = 'b3000';
429 class id title
430 3000 3000 b3000
431 SELECT * FROM t1 WHERE id = 4000;
432 class id title
433 4000 4000 b4000
434 SELECT * FROM t1 WHERE title = 'a4000';
435 class id title
436 SELECT * FROM t1 WHERE title = 'b4000';
437 class id title
438 4000 4000 b4000
439 SELECT * FROM t1 WHERE id = 4001;
440 class id title
441 SELECT * FROM t1 WHERE title = 'a4001';
442 class id title
443 SELECT COUNT(*) FROM t1;
444 COUNT(*)
445 9992
446 /* Add column. */
447 ALTER TABLE t1 ADD COLUMN content TEXT;
448 CHECK TABLE t1;
449 Table Op Msg_type Msg_text
450 test.t1 check status OK
451 SELECT * FROM t1 WHERE id = 10;
452 class id title content
453 10 10 a10 NULL
454 SELECT * FROM t1 WHERE title = 'a10';
455 class id title content
456 10 10 a10 NULL
457 SELECT * FROM t1 WHERE id = 5000;
458 class id title content
459 5000 5000 a5000 NULL
460 SELECT * FROM t1 WHERE title = 'a5000';
461 class id title content
462 5000 5000 a5000 NULL
463 SELECT * FROM t1 WHERE id = 10000;
464 class id title content
465 10000 10000 a10000 NULL
466 SELECT * FROM t1 WHERE title = 'a10000';
467 class id title content
468 10000 10000 a10000 NULL
469 SELECT * FROM t1 WHERE id = 10010;
470 class id title content
471 SELECT * FROM t1 WHERE title = 'a10010';
472 class id title content
473 /* Drop column. */
474 ALTER TABLE t1 DROP COLUMN content;
475 CHECK TABLE t1;
476 Table Op Msg_type Msg_text
477 test.t1 check status OK
478 SELECT * FROM t1 WHERE id = 10;
479 class id title
480 10 10 a10
481 SELECT * FROM t1 WHERE title = 'a10';
482 class id title
483 10 10 a10
484 SELECT * FROM t1 WHERE id = 5000;
485 class id title
486 5000 5000 a5000
487 SELECT * FROM t1 WHERE title = 'a5000';
488 class id title
489 5000 5000 a5000
490 SELECT * FROM t1 WHERE id = 10000;
491 class id title
492 10000 10000 a10000
493 SELECT * FROM t1 WHERE title = 'a10000';
494 class id title
495 10000 10000 a10000
496 SELECT * FROM t1 WHERE id = 10010;
497 class id title
498 SELECT * FROM t1 WHERE title = 'a10010';
499 class id title
500 DROP TABLE t1;
501 CREATE TABLE t1(
502 a INT PRIMARY KEY,
503 b TEXT,
504 c TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
505 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
506 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
507 CHAR_LENGTH(b)
508 10000
509 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
510 b=REPEAT(CONCAT('a',4975),2000)
511 1
512 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
513 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
514 b=REPEAT(CONCAT('a',4975),2000)
515 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
516 b=REPEAT(CONCAT('b',4975),2000)
517 1
518 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
519 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
520 b=REPEAT(CONCAT('b',4975),2000)
521 ALTER TABLE t1 DROP COLUMN c;
522 CHECK TABLE t1;
523 Table Op Msg_type Msg_text
524 test.t1 check status OK
525 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
526 CHAR_LENGTH(b)
527 DROP TABLE t1;
528 DROP PROCEDURE populate_t1;
529 CREATE PROCEDURE populate_t1(load_even INT)
530 BEGIN
531 DECLARE i int DEFAULT 1;
532 START TRANSACTION;
533 WHILE (i <= 10000) DO
534 IF i%2 = 0 AND load_even = 1 THEN
535 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
536 END IF;
537 IF i%2 != 0 AND load_even != 1 THEN
538 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
539 END IF;
540 SET i = i + 1;
541 END WHILE;
542 COMMIT;
543 END|
544 SELECT @@innodb_fill_factor;
545 @@innodb_fill_factor
546 100
547 SET GLOBAL innodb_file_per_table=1;
548 CREATE TABLE t1(
549 class INT,
550 id INT,
551 title VARCHAR(100)
552 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
553 SELECT COUNT(*) FROM t1;
554 COUNT(*)
555 5000
556 /* Create index. */
557 CREATE INDEX idx_id ON t1(id);
558 CREATE INDEX idx_title ON t1(title);
559 /* Check table. */
560 CHECK TABLE t1;
561 Table Op Msg_type Msg_text
562 test.t1 check status OK
563 /* Select by index. */
564 EXPLAIN SELECT * FROM t1 WHERE id = 10;
565 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
566 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
567 Warnings:
568 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
569 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
570 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
571 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
572 Warnings:
573 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
574 SELECT * FROM t1 WHERE id = 10;
575 class id title
576 10 10 a10
577 SELECT * FROM t1 WHERE title = 'a10';
578 class id title
579 10 10 a10
580 SELECT * FROM t1 WHERE id = 5000;
581 class id title
582 5000 5000 a5000
583 SELECT * FROM t1 WHERE title = 'a5000';
584 class id title
585 5000 5000 a5000
586 SELECT * FROM t1 WHERE id = 10000;
587 class id title
588 10000 10000 a10000
589 SELECT * FROM t1 WHERE title = 'a10000';
590 class id title
591 10000 10000 a10000
592 SELECT * FROM t1 WHERE id = 10010;
593 class id title
594 SELECT * FROM t1 WHERE title = 'a10010';
595 class id title
596 /*Insert/Update/Delete. */
597 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
598 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
599 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
600 SELECT * FROM t1 WHERE id = 3000;
601 class id title
602 3000 3000 b3000
603 SELECT * FROM t1 WHERE title = 'a3000';
604 class id title
605 SELECT * FROM t1 WHERE title = 'b3000';
606 class id title
607 3000 3000 b3000
608 SELECT * FROM t1 WHERE id = 4000;
609 class id title
610 4000 4000 b4000
611 SELECT * FROM t1 WHERE title = 'a4000';
612 class id title
613 SELECT * FROM t1 WHERE title = 'b4000';
614 class id title
615 4000 4000 b4000
616 SELECT * FROM t1 WHERE id = 4001;
617 class id title
618 SELECT * FROM t1 WHERE title = 'a4001';
619 class id title
620 SELECT COUNT(*) FROM t1;
621 COUNT(*)
622 9992
623 /* Add column. */
624 ALTER TABLE t1 ADD COLUMN content TEXT;
625 CHECK TABLE t1;
626 Table Op Msg_type Msg_text
627 test.t1 check status OK
628 SELECT * FROM t1 WHERE id = 10;
629 class id title content
630 10 10 a10 NULL
631 SELECT * FROM t1 WHERE title = 'a10';
632 class id title content
633 10 10 a10 NULL
634 SELECT * FROM t1 WHERE id = 5000;
635 class id title content
636 5000 5000 a5000 NULL
637 SELECT * FROM t1 WHERE title = 'a5000';
638 class id title content
639 5000 5000 a5000 NULL
640 SELECT * FROM t1 WHERE id = 10000;
641 class id title content
642 10000 10000 a10000 NULL
643 SELECT * FROM t1 WHERE title = 'a10000';
644 class id title content
645 10000 10000 a10000 NULL
646 SELECT * FROM t1 WHERE id = 10010;
647 class id title content
648 SELECT * FROM t1 WHERE title = 'a10010';
649 class id title content
650 /* Drop column. */
651 ALTER TABLE t1 DROP COLUMN content;
652 CHECK TABLE t1;
653 Table Op Msg_type Msg_text
654 test.t1 check status OK
655 SELECT * FROM t1 WHERE id = 10;
656 class id title
657 10 10 a10
658 SELECT * FROM t1 WHERE title = 'a10';
659 class id title
660 10 10 a10
661 SELECT * FROM t1 WHERE id = 5000;
662 class id title
663 5000 5000 a5000
664 SELECT * FROM t1 WHERE title = 'a5000';
665 class id title
666 5000 5000 a5000
667 SELECT * FROM t1 WHERE id = 10000;
668 class id title
669 10000 10000 a10000
670 SELECT * FROM t1 WHERE title = 'a10000';
671 class id title
672 10000 10000 a10000
673 SELECT * FROM t1 WHERE id = 10010;
674 class id title
675 SELECT * FROM t1 WHERE title = 'a10010';
676 class id title
677 DROP TABLE t1;
678 CREATE TABLE t1(
679 a INT PRIMARY KEY,
680 b BLOB,
681 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
682 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
683 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
684 CHAR_LENGTH(b)
685 10000
686 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
687 b=REPEAT(CONCAT('a',4975),2000)
688 1
689 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
690 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
691 b=REPEAT(CONCAT('a',4975),2000)
692 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
693 b=REPEAT(CONCAT('b',4975),2000)
694 1
695 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
696 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
697 b=REPEAT(CONCAT('b',4975),2000)
698 ALTER TABLE t1 DROP COLUMN c;
699 CHECK TABLE t1;
700 Table Op Msg_type Msg_text
701 test.t1 check status OK
702 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
703 CHAR_LENGTH(b)
704 DROP TABLE t1;
705 SET GLOBAL innodb_file_per_table=default;
706 DROP PROCEDURE populate_t1;
707 SET GLOBAL innodb_fill_factor=10;
708 CREATE PROCEDURE populate_t1(load_even INT)
709 BEGIN
710 DECLARE i int DEFAULT 1;
711 START TRANSACTION;
712 WHILE (i <= 10000) DO
713 IF i%2 = 0 AND load_even = 1 THEN
714 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
715 END IF;
716 IF i%2 != 0 AND load_even != 1 THEN
717 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
718 END IF;
719 SET i = i + 1;
720 END WHILE;
721 COMMIT;
722 END|
723 SELECT @@innodb_fill_factor;
724 @@innodb_fill_factor
725 10
726 CREATE TABLE t1(
727 class INT,
728 id INT,
729 title VARCHAR(100)
730 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
731 SELECT COUNT(*) FROM t1;
732 COUNT(*)
733 5000
734 /* Create index. */
735 CREATE INDEX idx_id ON t1(id);
736 CREATE INDEX idx_title ON t1(title);
737 /* Check table. */
738 CHECK TABLE t1;
739 Table Op Msg_type Msg_text
740 test.t1 check status OK
741 /* Select by index. */
742 EXPLAIN SELECT * FROM t1 WHERE id = 10;
743 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
744 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
745 Warnings:
746 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
747 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
748 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
749 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
750 Warnings:
751 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
752 SELECT * FROM t1 WHERE id = 10;
753 class id title
754 10 10 a10
755 SELECT * FROM t1 WHERE title = 'a10';
756 class id title
757 10 10 a10
758 SELECT * FROM t1 WHERE id = 5000;
759 class id title
760 5000 5000 a5000
761 SELECT * FROM t1 WHERE title = 'a5000';
762 class id title
763 5000 5000 a5000
764 SELECT * FROM t1 WHERE id = 10000;
765 class id title
766 10000 10000 a10000
767 SELECT * FROM t1 WHERE title = 'a10000';
768 class id title
769 10000 10000 a10000
770 SELECT * FROM t1 WHERE id = 10010;
771 class id title
772 SELECT * FROM t1 WHERE title = 'a10010';
773 class id title
774 /*Insert/Update/Delete. */
775 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
776 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
777 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
778 SELECT * FROM t1 WHERE id = 3000;
779 class id title
780 3000 3000 b3000
781 SELECT * FROM t1 WHERE title = 'a3000';
782 class id title
783 SELECT * FROM t1 WHERE title = 'b3000';
784 class id title
785 3000 3000 b3000
786 SELECT * FROM t1 WHERE id = 4000;
787 class id title
788 4000 4000 b4000
789 SELECT * FROM t1 WHERE title = 'a4000';
790 class id title
791 SELECT * FROM t1 WHERE title = 'b4000';
792 class id title
793 4000 4000 b4000
794 SELECT * FROM t1 WHERE id = 4001;
795 class id title
796 SELECT * FROM t1 WHERE title = 'a4001';
797 class id title
798 SELECT COUNT(*) FROM t1;
799 COUNT(*)
800 9992
801 /* Add column. */
802 ALTER TABLE t1 ADD COLUMN content TEXT;
803 CHECK TABLE t1;
804 Table Op Msg_type Msg_text
805 test.t1 check status OK
806 SELECT * FROM t1 WHERE id = 10;
807 class id title content
808 10 10 a10 NULL
809 SELECT * FROM t1 WHERE title = 'a10';
810 class id title content
811 10 10 a10 NULL
812 SELECT * FROM t1 WHERE id = 5000;
813 class id title content
814 5000 5000 a5000 NULL
815 SELECT * FROM t1 WHERE title = 'a5000';
816 class id title content
817 5000 5000 a5000 NULL
818 SELECT * FROM t1 WHERE id = 10000;
819 class id title content
820 10000 10000 a10000 NULL
821 SELECT * FROM t1 WHERE title = 'a10000';
822 class id title content
823 10000 10000 a10000 NULL
824 SELECT * FROM t1 WHERE id = 10010;
825 class id title content
826 SELECT * FROM t1 WHERE title = 'a10010';
827 class id title content
828 /* Drop column. */
829 ALTER TABLE t1 DROP COLUMN content;
830 CHECK TABLE t1;
831 Table Op Msg_type Msg_text
832 test.t1 check status OK
833 SELECT * FROM t1 WHERE id = 10;
834 class id title
835 10 10 a10
836 SELECT * FROM t1 WHERE title = 'a10';
837 class id title
838 10 10 a10
839 SELECT * FROM t1 WHERE id = 5000;
840 class id title
841 5000 5000 a5000
842 SELECT * FROM t1 WHERE title = 'a5000';
843 class id title
844 5000 5000 a5000
845 SELECT * FROM t1 WHERE id = 10000;
846 class id title
847 10000 10000 a10000
848 SELECT * FROM t1 WHERE title = 'a10000';
849 class id title
850 10000 10000 a10000
851 SELECT * FROM t1 WHERE id = 10010;
852 class id title
853 SELECT * FROM t1 WHERE title = 'a10010';
854 class id title
855 DROP TABLE t1;
856 CREATE TABLE t1(
857 a INT PRIMARY KEY,
858 b TEXT,
859 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
860 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
861 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
862 CHAR_LENGTH(b)
863 10000
864 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
865 b=REPEAT(CONCAT('a',4975),2000)
866 1
867 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
868 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
869 b=REPEAT(CONCAT('a',4975),2000)
870 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
871 b=REPEAT(CONCAT('b',4975),2000)
872 1
873 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
874 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
875 b=REPEAT(CONCAT('b',4975),2000)
876 ALTER TABLE t1 DROP COLUMN c;
877 CHECK TABLE t1;
878 Table Op Msg_type Msg_text
879 test.t1 check status OK
880 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
881 CHAR_LENGTH(b)
882 DROP TABLE t1;
883 DROP PROCEDURE populate_t1;
884 SET GLOBAL innodb_fill_factor=50;
885 CREATE PROCEDURE populate_t1(load_even INT)
886 BEGIN
887 DECLARE i int DEFAULT 1;
888 START TRANSACTION;
889 WHILE (i <= 10000) DO
890 IF i%2 = 0 AND load_even = 1 THEN
891 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
892 END IF;
893 IF i%2 != 0 AND load_even != 1 THEN
894 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
895 END IF;
896 SET i = i + 1;
897 END WHILE;
898 COMMIT;
899 END|
900 SELECT @@innodb_fill_factor;
901 @@innodb_fill_factor
902 50
903 CREATE TABLE t1(
904 class INT,
905 id INT,
906 title VARCHAR(100)
907 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
908 SELECT COUNT(*) FROM t1;
909 COUNT(*)
910 5000
911 /* Create index. */
912 CREATE INDEX idx_id ON t1(id);
913 CREATE INDEX idx_title ON t1(title);
914 /* Check table. */
915 CHECK TABLE t1;
916 Table Op Msg_type Msg_text
917 test.t1 check status OK
918 /* Select by index. */
919 EXPLAIN SELECT * FROM t1 WHERE id = 10;
920 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
921 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
922 Warnings:
923 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`id` = 10)
924 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
925 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
926 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
927 Warnings:
928 Note 1003 /* select#1 */ select `test`.`t1`.`class` AS `class`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (`test`.`t1`.`title` = 'a10')
929 SELECT * FROM t1 WHERE id = 10;
930 class id title
931 10 10 a10
932 SELECT * FROM t1 WHERE title = 'a10';
933 class id title
934 10 10 a10
935 SELECT * FROM t1 WHERE id = 5000;
936 class id title
937 5000 5000 a5000
938 SELECT * FROM t1 WHERE title = 'a5000';
939 class id title
940 5000 5000 a5000
941 SELECT * FROM t1 WHERE id = 10000;
942 class id title
943 10000 10000 a10000
944 SELECT * FROM t1 WHERE title = 'a10000';
945 class id title
946 10000 10000 a10000
947 SELECT * FROM t1 WHERE id = 10010;
948 class id title
949 SELECT * FROM t1 WHERE title = 'a10010';
950 class id title
951 /*Insert/Update/Delete. */
952 DELETE FROM t1 WHERE id < 4010 AND id > 3990;
953 INSERT INTO t1 VALUES(4000, 4000, 'b4000');
954 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990;
955 SELECT * FROM t1 WHERE id = 3000;
956 class id title
957 3000 3000 b3000
958 SELECT * FROM t1 WHERE title = 'a3000';
959 class id title
960 SELECT * FROM t1 WHERE title = 'b3000';
961 class id title
962 3000 3000 b3000
963 SELECT * FROM t1 WHERE id = 4000;
964 class id title
965 4000 4000 b4000
966 SELECT * FROM t1 WHERE title = 'a4000';
967 class id title
968 SELECT * FROM t1 WHERE title = 'b4000';
969 class id title
970 4000 4000 b4000
971 SELECT * FROM t1 WHERE id = 4001;
972 class id title
973 SELECT * FROM t1 WHERE title = 'a4001';
974 class id title
975 SELECT COUNT(*) FROM t1;
976 COUNT(*)
977 9992
978 /* Add column. */
979 ALTER TABLE t1 ADD COLUMN content TEXT;
980 CHECK TABLE t1;
981 Table Op Msg_type Msg_text
982 test.t1 check status OK
983 SELECT * FROM t1 WHERE id = 10;
984 class id title content
985 10 10 a10 NULL
986 SELECT * FROM t1 WHERE title = 'a10';
987 class id title content
988 10 10 a10 NULL
989 SELECT * FROM t1 WHERE id = 5000;
990 class id title content
991 5000 5000 a5000 NULL
992 SELECT * FROM t1 WHERE title = 'a5000';
993 class id title content
994 5000 5000 a5000 NULL
995 SELECT * FROM t1 WHERE id = 10000;
996 class id title content
997 10000 10000 a10000 NULL
998 SELECT * FROM t1 WHERE title = 'a10000';
999 class id title content
1000 10000 10000 a10000 NULL
1001 SELECT * FROM t1 WHERE id = 10010;
1002 class id title content
1003 SELECT * FROM t1 WHERE title = 'a10010';
1004 class id title content
1005 /* Drop column. */
1006 ALTER TABLE t1 DROP COLUMN content;
1007 CHECK TABLE t1;
1008 Table Op Msg_type Msg_text
1009 test.t1 check status OK
1010 SELECT * FROM t1 WHERE id = 10;
1011 class id title
1012 10 10 a10
1013 SELECT * FROM t1 WHERE title = 'a10';
1014 class id title
1015 10 10 a10
1016 SELECT * FROM t1 WHERE id = 5000;
1017 class id title
1018 5000 5000 a5000
1019 SELECT * FROM t1 WHERE title = 'a5000';
1020 class id title
1021 5000 5000 a5000
1022 SELECT * FROM t1 WHERE id = 10000;
1023 class id title
1024 10000 10000 a10000
1025 SELECT * FROM t1 WHERE title = 'a10000';
1026 class id title
1027 10000 10000 a10000
1028 SELECT * FROM t1 WHERE id = 10010;
1029 class id title
1030 SELECT * FROM t1 WHERE title = 'a10010';
1031 class id title
1032 DROP TABLE t1;
1033 CREATE TABLE t1(
1034 a INT PRIMARY KEY,
1035 b TEXT,
1036 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
1037 ALTER TABLE t1 ADD INDEX `idx` (a,b(5));
1038 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
1039 CHAR_LENGTH(b)
1040 10000
1041 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
1042 b=REPEAT(CONCAT('a',4975),2000)
1043 1
1044 UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%';
1045 SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%';
1046 b=REPEAT(CONCAT('a',4975),2000)
1047 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
1048 b=REPEAT(CONCAT('b',4975),2000)
1049 1
1050 DELETE FROM t1 WHERE a=4975 AND b like 'b4975%';
1051 SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%';
1052 b=REPEAT(CONCAT('b',4975),2000)
1053 ALTER TABLE t1 DROP COLUMN c;
1054 CHECK TABLE t1;
1055 Table Op Msg_type Msg_text
1056 test.t1 check status OK
1057 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
1058 CHAR_LENGTH(b)
1059 DROP TABLE t1;
1060 DROP PROCEDURE populate_t1;
1061 SET GLOBAL innodb_fill_factor=default;