MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
filesort_pq.inc
1 --source include/have_optimizer_trace.inc
2 --source include/have_64bit.inc
3 
4 if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
5  + $VIEW_PROTOCOL > 0`)
6 {
7  --skip Need normal protocol
8 }
9 
10 SET optimizer_trace_max_mem_size=1048576; # 1MB
11 SET end_markers_in_json=off;
12 SET optimizer_trace="enabled=on,one_line=off";
13 
14 --echo #
15 --echo # WL#1393 - Optimizing filesort with small limit
16 --echo # WL#5834 - Add optimizer traces for sorting
17 --echo #
18 
19 
20 CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200));
21 INSERT INTO t1(f1, f2) VALUES
22 (0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
23 (6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
24 (11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
25 (16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
26 (21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
27 (26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
28 (31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
29 (36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
30 (41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
31 (46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
32 (51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
33 (56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
34 (61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
35 (66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
36 (71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
37 (76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
38 (81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
39 (86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
40 (91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
41 (96,"96"),(97,"97"),(98,"98"),(99,"99");
42 
43 ################
44 ## Test sort when source data fits in memory
45 
46 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100;
47 SELECT * FROM information_schema.OPTIMIZER_TRACE;
48 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
49 SELECT * FROM information_schema.OPTIMIZER_TRACE;
50 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
51 SELECT * FROM information_schema.OPTIMIZER_TRACE;
52 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
53 SELECT * FROM information_schema.OPTIMIZER_TRACE;
54 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
55 SELECT * FROM information_schema.OPTIMIZER_TRACE;
56 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
57 SELECT * FROM information_schema.OPTIMIZER_TRACE;
58 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
59 SELECT * FROM information_schema.OPTIMIZER_TRACE;
60 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
61 SELECT * FROM information_schema.OPTIMIZER_TRACE;
62 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
63 SELECT * FROM information_schema.OPTIMIZER_TRACE;
64 
65 SELECT CONCAT("hello ", f2) AS foo FROM t1 ORDER BY foo LIMIT 2;
66 SELECT * FROM information_schema.OPTIMIZER_TRACE;
67 
68 --disable_result_log
69 SELECT * from t1 ORDER BY rand(2) LIMIT 2;
70 --enable_result_log
71 SELECT * FROM information_schema.OPTIMIZER_TRACE;
72 
73 ################
74 ## Test sort when source data does not fit in memory
75 set sort_buffer_size= 32768;
76 
77 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30;
78 SELECT * FROM information_schema.OPTIMIZER_TRACE;
79 SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0;
80 SELECT * FROM information_schema.OPTIMIZER_TRACE;
81 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30;
82 SELECT * FROM information_schema.OPTIMIZER_TRACE;
83 SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0;
84 SELECT * FROM information_schema.OPTIMIZER_TRACE;
85 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20;
86 SELECT * FROM information_schema.OPTIMIZER_TRACE;
87 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0;
88 SELECT * FROM information_schema.OPTIMIZER_TRACE;
89 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
90 SELECT * FROM information_schema.OPTIMIZER_TRACE;
91 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
92 SELECT * FROM information_schema.OPTIMIZER_TRACE;
93 
94 ################
95 ## Test with SQL_CALC_FOUND_ROWS
96 set sort_buffer_size= 32768;
97 set optimizer_trace_limit=1;
98 set optimizer_trace_offset=-2;
99 
100 SELECT SQL_CALC_FOUND_ROWS * FROM t1
101 ORDER BY f1, f0 LIMIT 30;
102 SELECT FOUND_ROWS();
103 SELECT * FROM information_schema.OPTIMIZER_TRACE;
104 
105 SELECT SQL_CALC_FOUND_ROWS * FROM t1
106 ORDER BY f1, f0 LIMIT 0;
107 SELECT FOUND_ROWS();
108 SELECT * FROM information_schema.OPTIMIZER_TRACE;
109 
110 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
111 ORDER BY f2, f0 LIMIT 20;
112 SELECT FOUND_ROWS();
113 SELECT * FROM information_schema.OPTIMIZER_TRACE;
114 
115 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
116 ORDER BY f2, f0 LIMIT 0;
117 SELECT FOUND_ROWS();
118 SELECT * FROM information_schema.OPTIMIZER_TRACE;
119 
120 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
121 ORDER BY f2, f0 LIMIT 10 OFFSET 10;
122 SELECT FOUND_ROWS();
123 SELECT * FROM information_schema.OPTIMIZER_TRACE;
124 
125 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
126 ORDER BY f2, f0 LIMIT 0 OFFSET 10;
127 SELECT FOUND_ROWS();
128 SELECT * FROM information_schema.OPTIMIZER_TRACE;
129 
130 ################
131 ## Test sorting with join
132 ## These are re-written to use PQ during execution.
133 set sort_buffer_size= 327680;
134 set optimizer_trace_limit=1;
135 set optimizer_trace_offset=-1;
136 
137 CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20));
138 INSERT INTO tmp SELECT f1, f2 FROM t1;
139 INSERT INTO t1(f1, f2) SELECT * FROM tmp;
140 INSERT INTO tmp SELECT f1, f2 FROM t1;
141 INSERT INTO t1(f1, f2) SELECT * FROM tmp;
142 
143 SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
144 ORDER BY tmp.f1, f0 LIMIT 30;
145 SELECT * FROM information_schema.OPTIMIZER_TRACE;
146 
147 SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2
148 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
149 SELECT * FROM information_schema.OPTIMIZER_TRACE;
150 
151 set optimizer_trace_limit=2;
152 set optimizer_trace_offset=-2;
153 
154 SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
155 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
156 SELECT FOUND_ROWS();
157 SELECT * FROM information_schema.OPTIMIZER_TRACE;
158 
159 SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2
160 WHERE t1.f2>20
161 ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30;
162 SELECT FOUND_ROWS();
163 SELECT * FROM information_schema.OPTIMIZER_TRACE;
164 
165 DROP TABLE tmp;
166 
167 ################
168 ## Test views
169 set optimizer_trace_limit=1;
170 set optimizer_trace_offset=-1;
171 
172 CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30;
173 SELECT * FROM v1;
174 SELECT * FROM information_schema.OPTIMIZER_TRACE;
175 drop view v1;
176 
177 CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100;
178 SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
179 SELECT * FROM information_schema.OPTIMIZER_TRACE;
180 
181 CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100;
182 SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0
183 LIMIT 30;
184 SELECT * FROM information_schema.OPTIMIZER_TRACE;
185 
186 ################
187 ## Test group & having
188 SELECT floor(f1/10) f3, count(f2) FROM t1
189 GROUP BY 1 ORDER BY 2,1 LIMIT 5;
190 SELECT * FROM information_schema.OPTIMIZER_TRACE;
191 
192 SELECT floor(f1/10) f3, count(f2) FROM t1
193 GROUP BY 1 ORDER BY 2,1 LIMIT 0;
194 SELECT * FROM information_schema.OPTIMIZER_TRACE;
195 
196 ################
197 ## Test SP
198 delimiter |;
199 CREATE PROCEDURE wl1393_sp_test()
200 BEGIN
201 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30;
202 SELECT * FROM information_schema.OPTIMIZER_TRACE;
203 SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15;
204 SELECT * FROM information_schema.OPTIMIZER_TRACE;
205 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10
206 ORDER BY f2, f0 LIMIT 15 OFFSET 15;
207 SELECT * FROM information_schema.OPTIMIZER_TRACE;
208 SELECT FOUND_ROWS();
209 SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30;
210 SELECT * FROM information_schema.OPTIMIZER_TRACE;
211 END|
212 CALL wl1393_sp_test()|
213 DROP PROCEDURE wl1393_sp_test|
214 delimiter ;|
215 
216 ################
217 ## Test with subqueries
218 SELECT d1.f1, d1.f2 FROM t1
219 LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1
220 ORDER BY d1.f2 DESC LIMIT 30;
221 SELECT * FROM information_schema.OPTIMIZER_TRACE;
222 
223 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1);
224 SELECT * FROM information_schema.OPTIMIZER_TRACE;
225 
226 --error ER_SUBQUERY_NO_1_ROW
227 SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2);
228 SELECT * FROM information_schema.OPTIMIZER_TRACE;
229 
230 ################
231 ## Test stripping additional fields
232 CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20));
233 INSERT INTO tmp SELECT f1,f2 FROM t1;
234 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
235 INSERT INTO tmp SELECT f1,f2 FROM t1;
236 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
237 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
238 INSERT INTO tmp SELECT f1,f2 FROM t1;
239 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
240 INSERT INTO tmp SELECT f1,f2 FROM t1;
241 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
242 INSERT INTO tmp SELECT f1,f2 FROM t1;
243 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
244 INSERT INTO tmp SELECT f1,f2 FROM t1;
245 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
246 INSERT INTO tmp SELECT f1,f2 FROM t1;
247 INSERT INTO t1(f1,f2) SELECT * FROM tmp;
248 
249 # Test when only sortkeys fits to memory
250 set sort_buffer_size= 32768;
251 SELECT * FROM t1 ORDER BY f2 LIMIT 100;
252 SELECT * FROM information_schema.OPTIMIZER_TRACE;
253 
254 DROP TABLE t1, tmp;
255 DROP VIEW v1, v2;
256 
257 --echo # end of WL#1393 - Optimizing filesort with small limit
258 --echo # end of WL#5834 - Add optimizer traces for sorting