|
In the following test case the first query produces an empty result set, and the second query returns a row. 1 row is the correct result.
SET use_stat_tables = 'PREFERABLY';
|
|
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2);
|
ANALYZE TABLE t1;
|
|
CREATE TABLE t2 (name CHAR(3)) ENGINE=MyISAM;
|
ANALYZE TABLE t2;
|
INSERT INTO t2 VALUES ('USA'),('AUS');
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
|
ANALYZE TABLE t2;
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
Results:
INSERT INTO t2 VALUES ('USA'),('AUS');
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
id name
|
ANALYZE TABLE t2;
|
Table Op Msg_type Msg_text
|
test.t2 analyze status OK
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
id name
|
1 AUS
|
revision-id: sergii@pisem.net-20131028064617-ferc2dktspd2rylj
|
revno: 3907
|
branch-nick: 10.0-base
|
First EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
Warnings:
|
Note 1003 select 1 AS `id`,'USA' AS `name` from `test`.`t1` straight_join `test`.`t2` where (('USA' in ('AUS','YEM')))
|
Second EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where
|
Warnings:
|
Note 1003 select 1 AS `id`,`test`.`t2`.`name` AS `name` from `test`.`t1` straight_join `test`.`t2` where ((`test`.`t2`.`name` in ('AUS','YEM')))
|
|