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/have_innodb_max_16k.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 SET GLOBAL innodb_file_per_table=default;
84
85 # Test Compressed Table
86 SET GLOBAL innodb_file_per_table=1;
87
88 CREATE TABLE t1(
89 class INT,
90 id INT,
91 title VARCHAR(100)
92 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
93
94 -- disable_query_log
95 CALL populate_t1();
96 -- enable_query_log
97
98 SELECT COUNT(*) FROM t1;
99
100 /* Create index. */
101 CREATE INDEX idx_id ON t1(id);
102
103 CREATE INDEX idx_title ON t1(title);
104
105 /* Check table. */
106 CHECK TABLE t1;
107
108 /* Select by index. */
109 EXPLAIN SELECT * FROM t1 WHERE id = 10;
110 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
111
112 SELECT * FROM t1 WHERE id = 10;
113 SELECT * FROM t1 WHERE title = 'a10';
114
115 SELECT * FROM t1 WHERE id = 500;
116 SELECT * FROM t1 WHERE title = 'a500';
117
118 SELECT * FROM t1 WHERE id = 1000;
119 SELECT * FROM t1 WHERE title = 'a1000';
120
121 SELECT * FROM t1 WHERE id = 1010;
122 SELECT * FROM t1 WHERE title = 'a1010';
123
124 DROP TABLE t1;
125
126 # Test Compression & Blob
127 CREATE TABLE t1(
128 a INT PRIMARY KEY,
129 b TEXT,
130 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
131
132 INSERT INTO t1 VALUES
133 (1, REPEAT('a',10000), 'a'),
134 (2, REPEAT('b',20000), 'b'),
135 (3, REPEAT('c',40000), 'c'),
136 (4, REPEAT('d',60000), 'd');
137
138 ALTER TABLE t1 DROP COLUMN c;
139
140 CHECK TABLE t1;
141
142 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
143
144 DROP TABLE t1;
145
146 SET GLOBAL innodb_file_per_table=default;
147
148 DROP PROCEDURE populate_t1;