218
218
explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
219
219
id select_type table type possible_keys key key_len ref rows Extra
220
220
1 SIMPLE t1 ref y y 5 const 1
221
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer
221
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
222
222
explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
223
223
id select_type table type possible_keys key key_len ref rows Extra
224
224
1 SIMPLE t1 ref y y 5 const 1
225
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer
225
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
226
226
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
227
227
id select_type table type possible_keys key key_len ref rows Extra
228
228
1 SIMPLE t1 ref y y 5 const 1
229
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using MRR; Using join buffer
229
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer
230
230
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
231
231
id select_type table type possible_keys key key_len ref rows Extra
232
232
1 SIMPLE t1 ref y y 5 const 1
233
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using MRR; Using join buffer
233
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer
234
234
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
235
235
id select_type table type possible_keys key key_len ref rows Extra
236
236
1 SIMPLE t1 ref y y 5 const 1
237
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer
237
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
238
238
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
239
239
id select_type table type possible_keys key key_len ref rows Extra
240
240
1 SIMPLE t1 ref y y 5 const 1
241
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using MRR; Using join buffer
241
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
242
242
explain select count(*) from t1 where x in (1);
243
243
id select_type table type possible_keys key key_len ref rows Extra
244
244
1 SIMPLE t1 ref x x 5 const 1 Using index
273
273
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
274
274
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
275
275
id select_type table type possible_keys key key_len ref rows Extra
276
1 SIMPLE t1 range a,b a 5 NULL 2 Using where; Using MRR
276
1 SIMPLE t1 range a,b a 5 NULL 2 Using where
277
277
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
418
418
test.t2 analyze status OK
419
419
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
420
420
id select_type table type possible_keys key key_len ref rows Extra
421
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where; Using MRR
421
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where
422
422
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid #
423
423
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0;
424
424
id select_type table type possible_keys key key_len ref rows Extra
425
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where; Using MRR
425
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where
426
426
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid #
427
427
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
428
428
id select_type table type possible_keys key key_len ref rows Extra
429
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where; Using MRR
429
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where
430
430
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid #
431
431
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0;
432
432
id select_type table type possible_keys key key_len ref rows Extra
433
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where; Using MRR
433
1 SIMPLE t1 range uid_index uid_index 4 NULL # Using where
434
434
1 SIMPLE t2 ref uid_index uid_index 4 test.t1.uid #
435
435
select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
436
436
id name uid id name uid
611
611
1 SIMPLE t1 ref a a 43 const 2 Using where
612
612
explain select * from t1 where a=binary 'aaa';
613
613
id select_type table type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 range a a 43 NULL 2 Using where; Using MRR
614
1 SIMPLE t1 range a a 43 NULL 2 Using where
615
615
explain select * from t1 where a='aaa' collate utf8_bin;
616
616
id select_type table type possible_keys key key_len ref rows Extra
617
1 SIMPLE t1 range a a 43 NULL 2 Using where; Using MRR
617
1 SIMPLE t1 range a a 43 NULL 2 Using where
619
619
CREATE TABLE t1 (
620
620
`CLIENT` char(3) collate utf8_bin NOT NULL default '000',
999
999
EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
1000
1000
id select_type table type possible_keys key key_len ref rows Extra
1001
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where; Using MRR
1001
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where
1002
1002
EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
1003
1003
id select_type table type possible_keys key key_len ref rows Extra
1004
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where; Using MRR
1004
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where
1005
1005
SELECT * FROM t1 WHERE 0 < b OR 0 > c;
1030
1030
('A2','2005-12-01 08:00:00',1000);
1031
1031
EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 23:59:59';
1032
1032
id select_type table type possible_keys key key_len ref rows Extra
1033
1 SIMPLE t1 range PRIMARY PRIMARY 90 NULL # Using where; Using MRR
1033
1 SIMPLE t1 range PRIMARY PRIMARY 90 NULL # Using where
1034
1034
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 23:59:59';
1035
1035
item started price
1036
1036
A1 2005-11-01 08:00:00 1000.000
added
removed
tests/r/range.result
