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