1333
1333
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2) from t1;
1334
1334
id select_type table type possible_keys key key_len ref rows filtered Extra
1335
1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index
1336
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key
1335
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1336
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1338
1338
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
1339
1339
explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
1340
1340
id select_type table type possible_keys key key_len ref rows filtered Extra
1341
1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index
1342
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key
1341
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1342
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1344
1344
Note 1003 select `test`.`t1`.`s1` AS `s1`,<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))) AS `s1 = ANY (SELECT s1 FROM t2)` from `test`.`t1`
1345
1345
explain extended select s1, s1 <> ALL (SELECT s1 FROM t2) from t1;
1346
1346
id select_type table type possible_keys key key_len ref rows filtered Extra
1347
1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index
1348
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Full scan on NULL key
1347
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1348
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Full scan on NULL key
1350
1350
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
1351
1351
explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
1352
1352
id select_type table type possible_keys key key_len ref rows filtered Extra
1353
1 PRIMARY t1 index NULL s1 8 NULL 3 100.00 Using index
1354
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 8 func 2 100.00 Using index; Using where; Full scan on NULL key
1353
1 PRIMARY t1 index NULL s1 23 NULL 3 100.00 Using index
1354
2 DEPENDENT SUBQUERY t2 index_subquery s1 s1 23 func 2 100.00 Using index; Using where; Full scan on NULL key
1356
1356
Note 1003 select `test`.`t1`.`s1` AS `s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`) in t2 on s1 checking NULL where (`test`.`t2`.`s1` < 'a2') having trigcond(<is_not_null_test>(`test`.`t2`.`s1`))))))) AS `s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
1357
1357
drop table t1,t2;
3087
3087
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
3088
3088
id select_type table type possible_keys key key_len ref rows Extra
3089
3089
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
3090
2 SUBQUERY t1 index NULL a 8 NULL 9
3090
2 SUBQUERY t1 index NULL a 23 NULL 9
3092
3092
create table t1( f1 int,f2 int);
3093
3093
insert into t1 values (1,1),(2,2);
3705
3707
CREATE INDEX I2 ON t1 (b);
3706
3708
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3707
3709
id select_type table type possible_keys key key_len ref rows Extra
3708
1 PRIMARY t1 index I1 I1 4 NULL 2 Using index; LooseScan
3709
1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using index condition
3710
1 PRIMARY t1 index I1 I1 7 NULL 2 Using index; LooseScan
3711
1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using index condition
3710
3712
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
3712
3714
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
3715
3717
CREATE INDEX I2 ON t2 (b);
3716
3718
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3717
3719
id select_type table type possible_keys key key_len ref rows Extra
3718
1 PRIMARY t2 index I1 I1 4 NULL 2 Using index; LooseScan
3719
1 PRIMARY t2 ref I2 I2 13 test.t2.a 1 Using index condition
3720
1 PRIMARY t2 index I1 I1 7 NULL 2 Using index; LooseScan
3721
1 PRIMARY t2 ref I2 I2 43 test.t2.a 1 Using index condition
3720
3722
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
3723
3725
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3724
3726
id select_type table type possible_keys key key_len ref rows Extra
3725
1 PRIMARY t1 index I1 I1 4 NULL 2 Using where; Using index; LooseScan
3726
1 PRIMARY t1 ref I2 I2 13 test.t1.a 1 Using index condition
3727
1 PRIMARY t1 index I1 I1 7 NULL 2 Using where; Using index; LooseScan
3728
1 PRIMARY t1 ref I2 I2 43 test.t1.a 1 Using index condition
3727
3729
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
3729
3731
DROP TABLE t1,t2;