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