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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
--- range_vs_index_merge_innodb.result 2017-01-17 15:00:18.039148421 -0800
+++ range_vs_index_merge_innodb,innodb_plugin.result 2017-01-17 14:58:45.129148312 -0800
@@ -50,14 +50,14 @@
WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR
(Population < 100000 OR Name Like 'T%') AND Country='ARG';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country,Name Country 3 NULL 106 Using index condition; Using where
+1 SIMPLE City range Population,Country,Name Country 3 NULL 106 Using where
EXPLAIN
SELECT * FROM City
WHERE Population < 200000 AND Name LIKE 'P%' AND
(Population > 300000 OR Name LIKE 'T%') AND
(Population < 100000 OR Name LIKE 'Pa%');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Name Name 35 NULL 235 Using index condition; Using where
+1 SIMPLE City range Population,Name Name 35 NULL 235 Using where
EXPLAIN
SELECT * FROM City
WHERE Population > 100000 AND Name LIKE 'Aba%' OR
@@ -70,12 +70,12 @@
SELECT * FROM City
WHERE (Population > 101000 AND Population < 115000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 458 Using index condition
+1 SIMPLE City range Population Population 4 NULL 458 Using where
EXPLAIN
SELECT * FROM City
WHERE (Population > 101000 AND Population < 102000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 38 Using index condition
+1 SIMPLE City range Population Population 4 NULL 38 Using where
EXPLAIN
SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'));
@@ -92,7 +92,7 @@
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 102000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country,Name Population 4 NULL 38 Using index condition; Using where
+1 SIMPLE City range Population,Country,Name Population 4 NULL 38 Using where
SELECT * FROM City USE INDEX ()
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'F'))
AND (Population > 101000 AND Population < 115000);
@@ -172,37 +172,37 @@
EXPLAIN
SELECT * FROM City WHERE (Name < 'Ac');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 23 Using index condition
+1 SIMPLE City range Name Name 35 NULL 23 Using where
EXPLAIN
SELECT * FROM City WHERE (Name < 'Bb');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 373 Using index condition
+1 SIMPLE City range Name Name 35 NULL 373 Using where
EXPLAIN
SELECT * FROM City WHERE (Country > 'A' AND Country < 'B');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country Country 3 NULL 106 Using index condition
+1 SIMPLE City range Country Country 3 NULL 106 Using where
EXPLAIN
SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'Pb');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 71 Using index condition
+1 SIMPLE City range Name Name 35 NULL 71 Using where
EXPLAIN
SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 384 Using index condition
+1 SIMPLE City range Name Name 35 NULL 384 Using where
EXPLAIN
SELECT * FROM City WHERE (Population > 101000 AND Population < 110000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 327 Using index condition
+1 SIMPLE City range Population Population 4 NULL 327 Using where
EXPLAIN
SELECT * FROM City WHERE (Population > 103000 AND Population < 104000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 36 Using index condition
+1 SIMPLE City range Population Population 4 NULL 36 Using where
EXPLAIN
SELECT * FROM City
WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
(Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population,Country,Name Name 35 NULL 94 Using index condition; Using where
+1 SIMPLE City range Population,Country,Name Name 35 NULL 94 Using where
EXPLAIN
SELECT * FROM City
WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR
@@ -340,15 +340,15 @@
EXPLAIN
SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country Country 3 NULL 19 Using index condition
+1 SIMPLE City range Country Country 3 NULL 19 Using where
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 394 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 394 Using where
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where
+1 SIMPLE City range Name Name 35 NULL 133 Using where
EXPLAIN
SELECT * FROM City
WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG')))
@@ -577,27 +577,27 @@
EXPLAIN
SELECT * FROM City WHERE Population > 101000 AND Population < 102000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 38 Using index condition
+1 SIMPLE City range Population Population 4 NULL 38 Using where
EXPLAIN
SELECT * FROM City WHERE Population > 101000 AND Population < 110000;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 327 Using index condition
+1 SIMPLE City range Population Population 4 NULL 327 Using where
EXPLAIN
SELECT * FROM City WHERE Country < 'C';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country Country 3 NULL 446 Using index condition
+1 SIMPLE City range Country Country 3 NULL 446 Using where
EXPLAIN
SELECT * FROM City WHERE Country < 'AGO';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country Country 3 NULL 5 Using index condition
+1 SIMPLE City range Country Country 3 NULL 5 Using where
EXPLAIN
SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 384 Using index condition
+1 SIMPLE City range Name Name 35 NULL 384 Using where
EXPLAIN
SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 71 Using index condition
+1 SIMPLE City range Name Name 35 NULL 71 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
@@ -609,7 +609,7 @@
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'P%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 235 Using index condition
+1 SIMPLE City range Name Name 35 NULL 235 Using where
EXPLAIN
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 102000) AND
@@ -680,23 +680,23 @@
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Pas%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 8 Using index condition
+1 SIMPLE City range Name Name 35 NULL 8 Using where
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'P%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 235 Using index condition
+1 SIMPLE City range Name Name 35 NULL 235 Using where
EXPLAIN
SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 80 Using index condition
+1 SIMPLE City range Population Population 4 NULL 80 Using where
EXPLAIN
SELECT * FROM City WHERE Country='USA';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Country,CountryPopulation Country 3 const 274 Using index condition
+1 SIMPLE City ref Country,CountryPopulation Country 3 const 274 Using where
EXPLAIN
SELECT * FROM City WHERE Country='FIN';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using index condition
+1 SIMPLE City ref Country,CountryPopulation Country 3 const 7 Using where
EXPLAIN
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
@@ -708,7 +708,7 @@
WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%')
AND Country='FIN';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where
+1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using where
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%')
AND Country='USA';
@@ -753,15 +753,15 @@
EXPLAIN
SELECT * FROM City WHERE Country='USA';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 274 Using index condition
+1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 274 Using where
EXPLAIN
SELECT * FROM City WHERE Country='FIN';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using index condition
+1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 7 Using where
EXPLAIN
SELECT * FROM City WHERE Country='BRA';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 250 Using index condition
+1 SIMPLE City ref Country,CountryPopulation,CountryName Country 3 const 250 Using where
EXPLAIN
SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800;
id select_type table type possible_keys key key_len ref rows Extra
@@ -789,15 +789,15 @@
EXPLAIN
SELECT * FROM City WHERE (Population > 101000 AND Population < 102000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 38 Using index condition
+1 SIMPLE City range Population Population 4 NULL 38 Using where
EXPLAIN
SELECT * FROM City WHERE (Population > 101000 AND Population < 103000);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Population Population 4 NULL 80 Using index condition
+1 SIMPLE City range Population Population 4 NULL 80 Using where
EXPLAIN
SELECT * FROM City WHERE Name LIKE 'Pa%';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Name Name 35 NULL 71 Using index condition
+1 SIMPLE City range Name Name 35 NULL 71 Using where
EXPLAIN
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 102000) OR
@@ -818,7 +818,7 @@
ID BETWEEN 3500 AND 3800) AND Country='FIN'
AND (Name BETWEEN 'P' AND 'T' OR ID BETWEEN 4000 AND 4300);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using index condition; Using where
+1 SIMPLE City ref PRIMARY,Population,Country,Name,CountryPopulation,CountryName Country 3 const 7 Using where
SELECT * FROM City USE INDEX ()
WHERE ((Population > 101000 AND Population < 102000) OR
ID BETWEEN 3790 AND 3800) AND Country='USA'
@@ -950,14 +950,14 @@
ID BETWEEN 3500 AND 3800) AND Country='USA'
AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition; Using where
+1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using where
EXPLAIN
SELECT * FROM City
WHERE ((Population > 101000 AND Population < 11000) OR
ID BETWEEN 3500 AND 3800) AND Country='USA'
AND (Name LIKE 'Pho%' OR ID BETWEEN 4000 AND 4300);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName Name 35 NULL 1 Using index condition; Using where
+1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName Name 35 NULL 1 Using where
SELECT * FROM City USE INDEX ()
WHERE ((Population > 101000 AND Population < 11000) OR
ID BETWEEN 3500 AND 3800) AND Country='USA'
@@ -1077,7 +1077,7 @@
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using where
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1164,7 +1164,7 @@
(Name='Samara' AND Country='RUS') OR
(Name='Seattle' AND Country='USA');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition; Using where
+1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using where
SELECT Name, Country, Population FROM City WHERE
(Name='Manila' AND Country='PHL') OR
(Name='Addis Abeba' AND Country='ETH') OR
@@ -1346,7 +1346,7 @@
AND (Population >= 100000 AND Population < 120000)
ORDER BY Population LIMIT 5;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where
+1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where
FLUSH STATUS;
SELECT * FROM City
WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
@@ -1790,7 +1790,7 @@
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range state,capital state 71 NULL 10 Using index condition; Using where
+1 SIMPLE t1 range state,capital state 71 NULL 10 Using where
SELECT * FROM t1 FORCE KEY (state,capital)
WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9
OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas';
|
