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 type possible_keys key key_len ref rows Extra
32 1 SIMPLE t1 ref idx_id idx_id 5 const 1
33 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
34 id select_type table type possible_keys key key_len ref rows Extra
35 1 SIMPLE t1 ref idx_title idx_title 403 const 1 Using index condition
36 SELECT * FROM t1 WHERE id = 10;
37 class id title
38 10 10 a10
39 SELECT * FROM t1 WHERE title = 'a10';
40 class id title
41 10 10 a10
42 SELECT * FROM t1 WHERE id = 500;
43 class id title
44 500 500 a500
45 SELECT * FROM t1 WHERE title = 'a500';
46 class id title
47 500 500 a500
48 SELECT * FROM t1 WHERE id = 1000;
49 class id title
50 1000 1000 a1000
51 SELECT * FROM t1 WHERE title = 'a1000';
52 class id title
53 1000 1000 a1000
54 SELECT * FROM t1 WHERE id = 1010;
55 class id title
56 SELECT * FROM t1 WHERE title = 'a1010';
57 class id title
58 DROP TABLE t1;
59 CREATE TABLE t1(
60 a INT PRIMARY KEY,
61 b TEXT,
62 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
63 INSERT INTO t1 VALUES
64 (1, REPEAT('a',10000), 'a'),
65 (2, REPEAT('b',20000), 'b'),
66 (3, REPEAT('c',40000), 'c'),
67 (4, REPEAT('d',60000), 'd');
68 ALTER TABLE t1 DROP COLUMN c;
69 CHECK TABLE t1;
70 Table Op Msg_type Msg_text
71 test.t1 check status OK
72 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
73 CHAR_LENGTH(b)
74 DROP TABLE t1;
75 CREATE TABLE t1(
76 class INT,
77 id INT,
78 title VARCHAR(100)
79 ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
80 SELECT COUNT(*) FROM t1;
81 COUNT(*)
82 1000
83 /* Create index. */
84 CREATE INDEX idx_id ON t1(id);
85 CREATE INDEX idx_title ON t1(title);
86 /* Check table. */
87 CHECK TABLE t1;
88 Table Op Msg_type Msg_text
89 test.t1 check status OK
90 /* Select by index. */
91 EXPLAIN SELECT * FROM t1 WHERE id = 10;
92 id select_type table type possible_keys key key_len ref rows Extra
93 1 SIMPLE t1 ref idx_id idx_id 5 const 1
94 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
95 id select_type table type possible_keys key key_len ref rows Extra
96 1 SIMPLE t1 ref idx_title idx_title 403 const 1 Using index condition
97 SELECT * FROM t1 WHERE id = 10;
98 class id title
99 10 10 a10
100 SELECT * FROM t1 WHERE title = 'a10';
101 class id title
102 10 10 a10
103 SELECT * FROM t1 WHERE id = 500;
104 class id title
105 500 500 a500
106 SELECT * FROM t1 WHERE title = 'a500';
107 class id title
108 500 500 a500
109 SELECT * FROM t1 WHERE id = 1000;
110 class id title
111 1000 1000 a1000
112 SELECT * FROM t1 WHERE title = 'a1000';
113 class id title
114 1000 1000 a1000
115 SELECT * FROM t1 WHERE id = 1010;
116 class id title
117 SELECT * FROM t1 WHERE title = 'a1010';
118 class id title
119 DROP TABLE t1;
120 CREATE TABLE t1(
121 a INT PRIMARY KEY,
122 b TEXT,
123 c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
124 INSERT INTO t1 VALUES
125 (1, REPEAT('a',10000), 'a'),
126 (2, REPEAT('b',20000), 'b'),
127 (3, REPEAT('c',40000), 'c'),
128 (4, REPEAT('d',60000), 'd');
129 ALTER TABLE t1 DROP COLUMN c;
130 CHECK TABLE t1;
131 Table Op Msg_type Msg_text
132 test.t1 check status OK
133 SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975;
134 CHAR_LENGTH(b)
135 DROP TABLE t1;
136 DROP PROCEDURE populate_t1;