Debian Codegraph @

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

raw
1 CREATE PROCEDURE populate_t1()
2 BEGIN
3 DECLARE i int DEFAULT 1;
4 START TRANSACTION;
5 WHILE (i <= 1000) DO
6 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
7 SET i = i + 1;
8 END WHILE;
9 COMMIT;
10 END|
11 SELECT @@innodb_fill_factor;
12 @@innodb_fill_factor
13 100
14 CREATE TABLE t1(
15 class INT,
16 id INT,
17 title VARCHAR(100)
18 ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
19 SELECT COUNT(*) FROM t1;
20 COUNT(*)
21 1000
22 /* Create index. */
23 CREATE INDEX idx_id ON t1(id);
24 CREATE INDEX idx_title ON t1(title);
25 /* Check table. */
26 CHECK TABLE t1;
27 Table Op Msg_type Msg_text
28 test.t1 check status OK
29 /* Select by index. */
30 EXPLAIN SELECT * FROM t1 WHERE id = 10;
31 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
32 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
33 Warnings:
34 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)
35 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
36 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
37 1 SIMPLE t1 NULL ref idx_title idx_title 403 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`.`title` = 'a10')
40 SELECT * FROM t1 WHERE id = 10;
41 class id title
42 10 10 a10
43 SELECT * FROM t1 WHERE title = 'a10';
44 class id title
45 10 10 a10
46 SELECT * FROM t1 WHERE id = 500;
47 class id title
48 500 500 a500
49 SELECT * FROM t1 WHERE title = 'a500';
50 class id title
51 500 500 a500
52 SELECT * FROM t1 WHERE id = 1000;
53 class id title
54 1000 1000 a1000
55 SELECT * FROM t1 WHERE title = 'a1000';
56 class id title
57 1000 1000 a1000
58 SELECT * FROM t1 WHERE id = 1010;
59 class id title
60 SELECT * FROM t1 WHERE title = 'a1010';
61 class id title
62 DROP TABLE t1;
63 CREATE TABLE t1(
64 a INT PRIMARY KEY,
65 b TEXT,
66 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
67 INSERT INTO t1 VALUES
68 (1, REPEAT('a',10000), 'a'),
69 (2, REPEAT('b',20000), 'b'),
70 (3, REPEAT('c',40000), 'c'),
71 (4, REPEAT('d',60000), 'd');
72 ALTER TABLE t1 DROP COLUMN c;
73 CHECK TABLE t1;
74 Table Op Msg_type Msg_text
75 test.t1 check status OK
76 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
77 CHAR_LENGTH(b)
78 DROP TABLE t1;
79 SET GLOBAL innodb_file_per_table=default;
80 SET GLOBAL innodb_file_per_table=1;
81 CREATE TABLE t1(
82 class INT,
83 id INT,
84 title VARCHAR(100)
85 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
86 SELECT COUNT(*) FROM t1;
87 COUNT(*)
88 1000
89 /* Create index. */
90 CREATE INDEX idx_id ON t1(id);
91 CREATE INDEX idx_title ON t1(title);
92 /* Check table. */
93 CHECK TABLE t1;
94 Table Op Msg_type Msg_text
95 test.t1 check status OK
96 /* Select by index. */
97 EXPLAIN SELECT * FROM t1 WHERE id = 10;
98 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
99 1 SIMPLE t1 NULL ref idx_id idx_id 5 const 1 100.00 NULL
100 Warnings:
101 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)
102 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
103 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
104 1 SIMPLE t1 NULL ref idx_title idx_title 403 const 1 100.00 NULL
105 Warnings:
106 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')
107 SELECT * FROM t1 WHERE id = 10;
108 class id title
109 10 10 a10
110 SELECT * FROM t1 WHERE title = 'a10';
111 class id title
112 10 10 a10
113 SELECT * FROM t1 WHERE id = 500;
114 class id title
115 500 500 a500
116 SELECT * FROM t1 WHERE title = 'a500';
117 class id title
118 500 500 a500
119 SELECT * FROM t1 WHERE id = 1000;
120 class id title
121 1000 1000 a1000
122 SELECT * FROM t1 WHERE title = 'a1000';
123 class id title
124 1000 1000 a1000
125 SELECT * FROM t1 WHERE id = 1010;
126 class id title
127 SELECT * FROM t1 WHERE title = 'a1010';
128 class id title
129 DROP TABLE t1;
130 CREATE TABLE t1(
131 a INT PRIMARY KEY,
132 b TEXT,
133 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
134 INSERT INTO t1 VALUES
135 (1, REPEAT('a',10000), 'a'),
136 (2, REPEAT('b',20000), 'b'),
137 (3, REPEAT('c',40000), 'c'),
138 (4, REPEAT('d',60000), 'd');
139 ALTER TABLE t1 DROP COLUMN c;
140 CHECK TABLE t1;
141 Table Op Msg_type Msg_text
142 test.t1 check status OK
143 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
144 CHAR_LENGTH(b)
145 DROP TABLE t1;
146 SET GLOBAL innodb_file_per_table=default;
147 DROP PROCEDURE populate_t1;