353
select some_id from t1 where some_id not in('-1', '0');
358
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
359
INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
360
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
361
INSERT INTO t2 VALUES (3,2),(4,2);
362
CREATE TABLE t3 (a int PRIMARY KEY);
363
INSERT INTO t3 VALUES (1),(2),(3),(4);
364
CREATE TABLE t4 (a int PRIMARY KEY);
365
INSERT INTO t4 VALUES (1),(2);
366
EXPLAIN SELECT STRAIGHT_JOIN * FROM t3
369
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
370
id select_type table type possible_keys key key_len ref rows Extra
371
1 SIMPLE t3 index PRIMARY PRIMARY 4 NULL 4 Using index
372
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
373
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
374
1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1)
375
SELECT STRAIGHT_JOIN * FROM t3
378
JOIN t4 WHERE t4.a IN (t1.b, t2.b);
384
EXPLAIN SELECT STRAIGHT_JOIN
385
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
387
WHERE t3.a=t1.a AND t3.a=t2.a;
388
id select_type table type possible_keys key key_len ref rows Extra
389
1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 4 Using index
390
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
391
1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1
392
2 DEPENDENT SUBQUERY t4 index NULL PRIMARY 4 NULL 2 Using where; Using index
394
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
396
WHERE t3.a=t1.a AND t3.a=t2.a;
397
(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
400
DROP TABLE t1,t2,t3,t4;