MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
innodb_pk_extension.inc
1 --source include/have_innodb.inc
2 
3 CREATE TABLE t1
4 (
5  pk_1 INT,
6  pk_2 INT,
7  f1 DATETIME,
8  f2 INT,
9  PRIMARY KEY(pk_1, pk_2),
10  KEY k1(f1),
11  KEY k2(f2)
12 ) ENGINE = InnoDB;
13 
14 INSERT INTO t1 VALUES
15 (1, 1, '2000-01-01', 1), (1, 2, '2000-01-02', 2), (1, 3, '2000-01-03', 3), (1, 4, '2000-01-04', 4), (1, 5, '2000-01-05', 5),
16 (2, 1, '2000-01-01', 6), (2, 2, '2000-01-02', 7), (2, 3, '2000-01-03', 8), (2, 4, '2000-01-04', 9), (2, 5, '2000-01-05', 10),
17 (3, 1, '2000-01-01', 11), (3, 2, '2000-01-02', 12), (3, 3, '2000-01-03', 13), (3, 4, '2000-01-04', 14), (3, 5, '2000-01-05', 15),
18 (4, 1, '2000-01-01', 16), (4, 2, '2000-01-02', 17), (4, 3, '2000-01-03', 18), (4, 4, '2000-01-04', 19), (4, 5, '2000-01-05', 20),
19 (5, 1, '2000-01-01', 21), (5, 2, '2000-01-02', 22), (5, 3, '2000-01-03', 23), (5, 4, '2000-01-04', 24), (5, 5, '2000-01-05', 25),
20 (6, 1, '2000-01-06', 26), (6, 2, '2000-01-06', 27), (6, 3, '2000-01-03', 28), (6, 4, '2000-01-06', 29), (6, 5, '2000-01-06', 30),
21 (7, 1, '2000-01-06', 31), (7, 2, '2000-01-06', 32), (7, 3, '2000-01-03', 33), (7, 4, '2000-01-06', 34), (7, 5, '2000-01-06', 35),
22 (8, 1, '2000-01-06', 36), (8, 2, '2000-01-06', 37), (8, 3, '2000-01-03', 38), (8, 4, '2000-01-06', 39), (8, 5, '2000-01-06', 40),
23 (9, 1, '2000-01-06', 41), (9, 2, '2000-01-06', 42), (9, 3, '2000-01-03', 43), (9, 4, '2000-01-06', 44), (9, 5, '2000-01-06', 45);
24 
25 INSERT INTO t1 VALUES
26 (11, 1, '2000-01-01', 1), (11, 2, '2000-01-02', 2), (11, 3, '2000-01-03', 3), (11, 4, '2000-01-04', 4), (11, 5, '2000-01-05', 5),
27 (12, 1, '2000-01-01', 6), (12, 2, '2000-01-02', 7), (12, 3, '2000-01-03', 8), (12, 4, '2000-01-04', 9), (12, 5, '2000-01-05', 10),
28 (13, 1, '2000-01-01', 11), (13, 2, '2000-01-02', 12), (13, 3, '2000-01-03', 13), (13, 4, '2000-01-04', 14), (13, 5, '2000-01-05', 15),
29 (14, 1, '2000-01-01', 16), (14, 2, '2000-01-02', 17), (14, 3, '2000-01-03', 18), (14, 4, '2000-01-04', 19), (14, 5, '2000-01-05', 20),
30 (15, 1, '2000-01-01', 1), (15, 2, '2000-01-02', 2), (15, 3, '2000-01-03', 3), (15, 4, '2000-01-04', 4), (15, 5, '2000-01-05', 5),
31 (16, 1, '2000-01-06', 6), (16, 2, '2000-01-06', 7), (16, 3, '2000-01-03', 8), (16, 4, '2000-01-06', 9), (16, 5, '2000-01-06', 10),
32 (17, 1, '2000-01-06', 31), (17, 2, '2000-01-06', 32), (17, 3, '2000-01-03', 33), (17, 4, '2000-01-06', 34), (17, 5, '2000-01-06', 35),
33 (18, 1, '2000-01-06', 36), (18, 2, '2000-01-06', 37), (18, 3, '2000-01-03', 38), (18, 4, '2000-01-06', 39), (18, 5, '2000-01-06', 40),
34 (19, 1, '2000-01-06', 1), (19, 2, '2000-01-06', 2), (19, 3, '2000-01-03', 3), (19, 4, '2000-01-06', 4), (19, 5, '2000-01-06', 5);
35 
36 
37 INSERT INTO t1 VALUES
38 (21, 1, '2000-01-01', 1), (21, 2, '2000-01-02', 2), (31, 3, '2000-01-03', 3), (41, 4, '2000-01-04', 4), (51, 5, '2000-01-05', 5),
39 (22, 1, '2000-01-01', 6), (22, 2, '2000-01-02', 7), (32, 3, '2000-01-03', 8), (42, 4, '2000-01-04', 9), (52, 5, '2000-01-05', 10),
40 (23, 1, '2000-01-01', 11), (23, 2, '2000-01-02', 12), (33, 3, '2000-01-03', 13), (43, 4, '2000-01-04', 14), (53, 5, '2000-01-05', 15),
41 (24, 1, '2000-01-01', 16), (24, 2, '2000-01-02', 17), (34, 3, '2000-01-03', 18), (44, 4, '2000-01-04', 19), (54, 5, '2000-01-05', 20),
42 (25, 1, '2000-01-01', 1), (25, 2, '2000-01-02', 2), (35, 3, '2000-01-03', 3), (45, 4, '2000-01-04', 4), (55, 5, '2000-01-05', 5),
43 (26, 1, '2000-01-06', 6), (26, 2, '2000-01-06', 7), (36, 3, '2000-01-03', 8), (46, 4, '2000-01-06', 9), (56, 5, '2000-01-06', 10),
44 (27, 1, '2000-01-06', 31), (27, 2, '2000-01-06', 32), (37, 3, '2000-01-03', 33), (47, 4, '2000-01-06', 34), (57, 5, '2000-01-06', 35),
45 (28, 1, '2000-01-06', 36), (28, 2, '2000-01-06', 37), (38, 3, '2000-01-03', 38), (48, 4, '2000-01-06', 39), (58, 5, '2000-01-06', 40),
46 (29, 1, '2000-01-06', 1), (29, 2, '2000-01-06', 2), (39, 3, '2000-01-03', 3), (49, 4, '2000-01-06', 4), (59, 5, '2000-01-06', 5);
47 
48 INSERT INTO t1 SELECT pk_1 + 60, pk_2, f1, f2 FROM t1;
49 INSERT INTO t1 SELECT pk_1 + 120, pk_2, f1, f2 FROM t1;
50 INSERT INTO t1 SELECT pk_1 + 240, pk_2, f1, f2 FROM t1;
51 INSERT INTO t1 SELECT pk_1, pk_2 + 10, f1, f2 FROM t1;
52 
53 ANALYZE TABLE t1;
54 
55 --echo #
56 --echo # REF access optimization
57 --echo #
58 
59 --replace_column 9 #
60 EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
61 FLUSH STATUS;
62 SELECT count(*) FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
63 SHOW STATUS LIKE 'handler_read%';
64 
65 --replace_column 9 #
66 EXPLAIN SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
67 FLUSH STATUS;
68 SELECT pk_2 FROM t1 WHERE pk_1 = 3 and f1 = '2000-01-03';
69 SHOW STATUS LIKE 'handler_read%';
70 
71 --echo #
72 --echo # RANGE access optimization
73 --echo #
74 
75 --replace_column 9 #
76 EXPLAIN SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
77 FLUSH STATUS;
78 SELECT count(*) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
79 SHOW STATUS LIKE 'handler_read%';
80 
81 --replace_column 9 #
82 EXPLAIN SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
83 FLUSH STATUS;
84 SELECT pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
85 SHOW STATUS LIKE 'handler_read%';
86 
87 --echo #
88 --echo # MAX/MIN optimization
89 --echo #
90 
91 --replace_column 9 #
92 EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
93 FLUSH STATUS;
94 SELECT MIN(pk_1) FROM t1 WHERE f1 = '2000-01-03';
95 SHOW STATUS LIKE 'handler_read%';
96 
97 --replace_column 9 #
98 EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
99 FLUSH STATUS;
100 SELECT MIN(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
101 SHOW STATUS LIKE 'handler_read%';
102 
103 --replace_column 9 #
104 EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
105 FLUSH STATUS;
106 SELECT MAX(pk_1) FROM t1 WHERE f1 = '2000-01-03';
107 SHOW STATUS LIKE 'handler_read%';
108 
109 --replace_column 9 #
110 EXPLAIN SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
111 FLUSH STATUS;
112 SELECT MAX(pk_1) FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03';
113 SHOW STATUS LIKE 'handler_read%';
114 
115 --echo #
116 --echo # Loose index scan
117 --echo #
118 
119 --replace_column 9 #
120 EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
121 FLUSH STATUS;
122 SELECT MIN(pk_1) FROM t1 WHERE f2 BETWEEN 13 AND 14 GROUP BY f2;
123 SHOW STATUS LIKE 'handler_read%';
124 
125 --replace_column 9 #
126 EXPLAIN SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
127 FLUSH STATUS;
128 SELECT MIN(pk_1) FROM t1 WHERE f2 IN (1, 2) GROUP BY f2;
129 SHOW STATUS LIKE 'handler_read%';
130 
131 --echo #
132 --echo # JOIN optimization
133 --echo #
134 
135 --replace_column 9 #
136 EXPLAIN SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
137 ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
138 FLUSH STATUS;
139 SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2
140 ON t2.pk_1 = t1.pk_1 WHERE t1.f1 = '2000-01-03' AND t2.f1 = '2000-01-03';
141 SHOW STATUS LIKE 'handler_read%';
142 
143 --echo #
144 --echo # Optimization of sorting
145 --echo #
146 
147 --replace_column 9 #
148 EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
149 ORDER BY pk_2 DESC LIMIT 5;
150 FLUSH STATUS;
151 SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 = 3 AND f1 = '2000-01-03'
152 ORDER BY pk_2 DESC LIMIT 5;
153 SHOW STATUS LIKE 'handler_read%';
154 
155 --replace_column 9 #
156 EXPLAIN SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
157 ORDER BY pk_2 DESC LIMIT 5;
158 FLUSH STATUS;
159 SELECT f1, pk_1, pk_2 FROM t1 WHERE pk_1 BETWEEN 3 AND 5 AND f1 = '2000-01-03'
160 ORDER BY pk_2 DESC LIMIT 5;
161 SHOW STATUS LIKE 'handler_read%';
162 
163 DROP TABLE t1;
164 
165 --echo #
166 --echo # Max key part limitation
167 --echo #
168 
169 CREATE TABLE t1
170 (
171  f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT,
172  f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT,
173  PRIMARY KEY (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10),
174  KEY k1 (f11, f12, f13, f14, f15, f16, f17)
175 ) ENGINE = InnoDB;
176 
177 --replace_column 9 #
178 EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
179 f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
180 f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND f10 = 0 AND
181 f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
182 f15 = 0 AND f16 = 0 AND f17 = 0;
183 
184 --replace_column 9 #
185 EXPLAIN SELECT f17 FROM t1 FORCE INDEX (k1) WHERE
186 f1 = 0 AND f2 = 0 AND f3 = 0 AND f4 = 0 AND f5 = 0 AND
187 f6 = 0 AND f7 = 0 AND f8 = 0 AND f9 = 0 AND
188 f11 = 0 AND f12 = 0 AND f13 = 0 AND f14 = 0 AND
189 f15 = 0 AND f16 = 0 AND f17 = 0;
190 
191 DROP TABLE t1;
192 
193 --echo #
194 --echo # Max key length limitation
195 --echo #
196 
197 CREATE TABLE t1
198 (
199  f1 VARCHAR(500), f2 VARCHAR(500), f3 VARCHAR(500),
200  f4 VARCHAR(500), f5 VARCHAR(500), f6 VARCHAR(500),
201  f7 VARCHAR(500),
202  PRIMARY KEY (f1, f2, f3, f4),
203  KEY k1 (f5, f6, f7)
204 ) ENGINE = InnoDB;
205 
206 --replace_column 9 #
207 EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
208 f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND f4 = 'a' AND
209 f5 = 'a' AND f6 = 'a' AND f7 = 'a';
210 
211 --replace_column 9 #
212 EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
213 f1 = 'a' AND f2 = 'a' AND f3 = 'a' AND
214 f5 = 'a' AND f6 = 'a' AND f7 = 'a';
215 
216 --replace_column 9 #
217 EXPLAIN SELECT f5 FROM t1 FORCE INDEX (k1) WHERE
218 f1 = 'a' AND f2 = 'a' AND f4 = 'a' AND
219 f5 = 'a' AND f6 = 'a' AND f7 = 'a';
220 
221 DROP TABLE t1;
222 
223 --echo #
224 --echo # Unique extended key
225 --echo #
226 
227 CREATE TABLE t1
228 (
229  pk INT NOT NULL auto_increment,
230  f1 INT NOT NULL,
231  KEY (f1),
232  PRIMARY KEY (pk)
233 ) ENGINE = INNODB;
234 
235 CREATE TABLE t2
236 (
237  f1 INT,
238  f2 INT
239 ) ENGINE = INNODB;
240 
241 INSERT INTO t1(f1) VALUES (1),(2);
242 INSERT INTO t1(f1) SELECT f1 + 2 FROM t1;
243 INSERT INTO t1(f1) SELECT f1 + 4 FROM t1;
244 ANALYZE TABLE t1;
245 
246 INSERT INTO t2 VALUES (1,1), (2,2);
247 
248 EXPLAIN SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
249 FLUSH STATUS;
250 SELECT t2.f1 FROM t2 JOIN t1 IGNORE INDEX(primary) ON t2.f1 = t1.pk and t2.f2 = t1.f1;
251 SHOW STATUS LIKE 'Handler_read%';
252 
253 DROP TABLE t1, t2;