Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test

raw
1 #
2 # wl#7277: InnoDB: Bulk Load for Create Index
3 #
4
5 -- source include/innodb_page_size_small.inc
6
7 # Create Insert Procedure
8 DELIMITER |;
9 CREATE PROCEDURE populate_t1()
10 BEGIN
11 DECLARE i int DEFAULT 1;
12
13 START TRANSACTION;
14 WHILE (i <= 1000) DO
15 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
16 SET i = i + 1;
17 END WHILE;
18 COMMIT;
19 END|
20 DELIMITER ;|
21
22 SELECT @@innodb_fill_factor;
23
24 # Test Compact Table
25 CREATE TABLE t1(
26 class INT,
27 id INT,
28 title VARCHAR(100)
29 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
30
31 -- disable_query_log
32 CALL populate_t1();
33 -- enable_query_log
34
35 SELECT COUNT(*) FROM t1;
36
37 /* Create index. */
38 CREATE INDEX idx_id ON t1(id);
39
40 CREATE INDEX idx_title ON t1(title);
41
42 /* Check table. */
43 CHECK TABLE t1;
44
45 /* Select by index. */
46 EXPLAIN SELECT * FROM t1 WHERE id = 10;
47 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
48
49 SELECT * FROM t1 WHERE id = 10;
50 SELECT * FROM t1 WHERE title = 'a10';
51
52 SELECT * FROM t1 WHERE id = 500;
53 SELECT * FROM t1 WHERE title = 'a500';
54
55 SELECT * FROM t1 WHERE id = 1000;
56 SELECT * FROM t1 WHERE title = 'a1000';
57
58 SELECT * FROM t1 WHERE id = 1010;
59 SELECT * FROM t1 WHERE title = 'a1010';
60
61 DROP TABLE t1;
62
63 # Test Blob
64 CREATE TABLE t1(
65 a INT PRIMARY KEY,
66 b TEXT,
67 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
68
69 INSERT INTO t1 VALUES
70 (1, REPEAT('a',10000), 'a'),
71 (2, REPEAT('b',20000), 'b'),
72 (3, REPEAT('c',40000), 'c'),
73 (4, REPEAT('d',60000), 'd');
74
75 ALTER TABLE t1 DROP COLUMN c;
76
77 CHECK TABLE t1;
78
79 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
80
81 DROP TABLE t1;
82
83 CREATE TABLE t1(
84 class INT,
85 id INT,
86 title VARCHAR(100)
87 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
88
89 -- disable_query_log
90 CALL populate_t1();
91 -- enable_query_log
92
93 SELECT COUNT(*) FROM t1;
94
95 /* Create index. */
96 CREATE INDEX idx_id ON t1(id);
97
98 CREATE INDEX idx_title ON t1(title);
99
100 /* Check table. */
101 CHECK TABLE t1;
102
103 /* Select by index. */
104 EXPLAIN SELECT * FROM t1 WHERE id = 10;
105 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
106
107 SELECT * FROM t1 WHERE id = 10;
108 SELECT * FROM t1 WHERE title = 'a10';
109
110 SELECT * FROM t1 WHERE id = 500;
111 SELECT * FROM t1 WHERE title = 'a500';
112
113 SELECT * FROM t1 WHERE id = 1000;
114 SELECT * FROM t1 WHERE title = 'a1000';
115
116 SELECT * FROM t1 WHERE id = 1010;
117 SELECT * FROM t1 WHERE title = 'a1010';
118
119 DROP TABLE t1;
120
121 # Test Compression & Blob
122 CREATE TABLE t1(
123 a INT PRIMARY KEY,
124 b TEXT,
125 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
126
127 INSERT INTO t1 VALUES
128 (1, REPEAT('a',10000), 'a'),
129 (2, REPEAT('b',20000), 'b'),
130 (3, REPEAT('c',40000), 'c'),
131 (4, REPEAT('d',60000), 'd');
132
133 ALTER TABLE t1 DROP COLUMN c;
134
135 CHECK TABLE t1;
136
137 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
138
139 DROP TABLE t1;
140
141 DROP PROCEDURE populate_t1;