CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a));
CREATE TABLE t2 (a INTEGER NOT NULL, KEY (a));
CREATE TABLE t3 (a INTEGER NOT NULL, b INT, KEY (a));
INSERT INTO t1 VALUES (1,10), (2,20), (3,30),  (4,40);
INSERT INTO t2 VALUES (2), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (20,4), (30,5);
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
test.t3 analyze status Engine-independent statistics collected
test.t3 analyze status OK
# Parser tests
# Correct hints (no warnings):
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`select#1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ QB_NAME(`qb1`) SEMIJOIN(@`qb1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) NO_SEMIJOIN(@qb1 firstmatch) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ QB_NAME(`qb1`) NO_SEMIJOIN(@`qb1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(  @qb1 firstmatch,  dupsweedout ) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ QB_NAME(`qb1`) SEMIJOIN(@`qb1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(  FIRSTMATCH, LOOSESCAN,materialization, DUPSWEEDOUT  ) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`select#1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb2) NO_SEMIJOIN(@qb2 FIRSTMATCH,LOOSESCAN, materialization, DUPSWEEDOUT) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ QB_NAME(`qb2`) NO_SEMIJOIN(@`qb2` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
set optimizer_switch='derived_merge=off';
# Correct 'cause hints refer to different query blocks:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@qb1) SEMIJOIN(loosescan)*/ a
FROM (SELECT /*+ QB_NAME(qb1)*/ * FROM t2) AS tt;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 
2 DERIVED t2 index NULL a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`qb1`) SEMIJOIN(@`select#1` LOOSESCAN) */ `tt`.`a` AS `a` from (/* select#2 */ select /*+ QB_NAME(`qb1`) */ `test`.`t2`.`a` AS `a` from `test`.`t2`) `tt`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN()*/ a
FROM (SELECT /*+ SEMIJOIN(loosescan)*/ * FROM t2) AS tt;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 
2 DERIVED t2 index NULL a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`select#2` LOOSESCAN) SEMIJOIN(@`select#1`) */ `tt`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`) `tt`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SUBQUERY(@`select#1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY( INTOEXISTS ) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SUBQUERY(@`select#1` INTOEXISTS) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME (qb1) SUBQUERY(@qb1   materialization) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ QB_NAME(`qb1`) SUBQUERY(@`qb1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
# Incorrect hints (warnings)
SELECT /*+ SEMIJOIN(loosescan @qb1) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near '@qb1) */ a FROM t1' at line 1
SELECT /*+ SEMIJOIN(@qb1 @qb2) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near '@qb2) */ a FROM t1' at line 1
SELECT /*+ SEMIJOIN(@qb1 LOOSESCAN,materialization, unknown_strategy) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near 'unknown_strategy) */ a FROM t1' at line 1
SELECT /*+ NO_SEMIJOIN(@qb1, @qb2) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near ', @qb2) */ a FROM t1' at line 1
SELECT /*+ NO_SEMIJOIN(FIRSTMATCH, ,LOOSESCAN, materialization) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near ',LOOSESCAN, materialization) */ a FROM t1' at line 1
SELECT /*+ NO_SEMIJOIN(FIRSTMATCH, @qb2,LOOSESCAN) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near '@qb2,LOOSESCAN) */ a FROM t1' at line 1
SELECT /*+ SUBQUERY(wrong_strat) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near 'wrong_strat) */ a FROM t1' at line 1
SELECT /*+ SUBQUERY(materialization, intoexists) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near ', intoexists) */ a FROM t1' at line 1
SELECT /*+ SUBQUERY(@qb1   materialization) */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 4220 Query block name `qb1` is not found for SUBQUERY hint
SELECT /*+ SUBQUERY() */ a FROM t1;
a
1
2
3
4
Warnings:
Warning 1064 Optimizer hint syntax error near ') */ a FROM t1' at line 1
# Mix of correct and incorrect hints:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(firstmatch ) SEMIJOIN(loosescan @qb1) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 1064 Optimizer hint syntax error near '@qb1) */ a FROM t1' at line 2
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@qb1, @qb2) SEMIJOIN()*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 1064 Optimizer hint syntax error near ', @qb2) SEMIJOIN()*/ a FROM t1' at line 2
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN() NO_SEMIJOIN(FIRSTMATCH, @qb2,LOOSESCAN) */ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 1064 Optimizer hint syntax error near '@qb2,LOOSESCAN) */ a FROM t1' at line 2
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1`
# Conflicting hints:
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() SEMIJOIN(dupsweedout) NO_SEMIJOIN(firstmatch)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(DUPSWEEDOUT) is ignored as conflicting/duplicated
Warning 4219 Hint NO_SEMIJOIN(FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`select#1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(loosescan,materialization) SEMIJOIN(dupsweedout)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(DUPSWEEDOUT) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`select#1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(firstmatch,loosescan,materialization) SEMIJOIN() NO_SEMIJOIN()*/ a
FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN() is ignored as conflicting/duplicated
Warning 4219 Hint NO_SEMIJOIN() is ignored as conflicting/duplicated
Note 1003 select /*+ NO_SEMIJOIN(@`select#1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) SEMIJOIN(loosescan) NO_SEMIJOIN(@qb1 dupsweedout)*/ a
FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(LOOSESCAN) is ignored as conflicting/duplicated
Warning 4219 Hint NO_SEMIJOIN(@`qb1` DUPSWEEDOUT) is ignored as conflicting/duplicated
Note 1003 select /*+ QB_NAME(`qb1`) SEMIJOIN(@`qb1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED SELECT /*+ SEMIJOIN(firstmatch) NO_SEMIJOIN()*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint NO_SEMIJOIN() is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`select#1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ SUBQUERY(@`select#1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN() SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(MATERIALIZATION) is ignored as conflicting/duplicated
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`select#1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ SUBQUERY(@`select#1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SUBQUERY(intoexists) SUBQUERY(materialization)*/ a
FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Warning 4219 Hint SUBQUERY(MATERIALIZATION) is ignored as conflicting/duplicated
Note 1003 select /*+ SUBQUERY(@`select#1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(materialization) SEMIJOIN(firstmatch) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(FIRSTMATCH) is ignored as conflicting/duplicated
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ SUBQUERY(@`select#1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SEMIJOIN(@qb1) SUBQUERY(@qb1 materialization) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(@`qb1` MATERIALIZATION) is ignored as conflicting/duplicated
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ QB_NAME(`qb1`) SEMIJOIN(@`qb1`) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ QB_NAME(qb1) SUBQUERY(@qb1 materialization) SEMIJOIN(@qb1 firstmatch) SUBQUERY(intoexists)*/ a FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(@`qb1` FIRSTMATCH) is ignored as conflicting/duplicated
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 select /*+ QB_NAME(`qb1`) SUBQUERY(@`qb1` MATERIALIZATION) */ `test`.`t1`.`a` AS `a` from `test`.`t1`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@qb1) SEMIJOIN(loosescan) NO_SEMIJOIN(@qb1 dupsweedout)*/ a
FROM (SELECT /*+ QB_NAME(qb1)*/ * FROM t2) AS tt;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 
2 DERIVED t2 index NULL a 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint NO_SEMIJOIN(@`qb1` DUPSWEEDOUT) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`qb1`) SEMIJOIN(@`select#1` LOOSESCAN) */ `tt`.`a` AS `a` from (/* select#2 */ select /*+ QB_NAME(`qb1`) */ `test`.`t2`.`a` AS `a` from `test`.`t2`) `tt`
DROP TABLE t1, t2 ,t3;
set optimizer_switch=default;
# End of parser tests
CREATE TABLE t1 (a INTEGER NOT NULL, b INT, PRIMARY KEY (a));
CREATE TABLE t2 (a INTEGER NOT NULL, KEY (a));
CREATE TABLE t3 (a INTEGER NOT NULL, b INT, KEY (a));
INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
INSERT INTO t2 VALUES (2), (2), (3), (3), (4), (5);
INSERT INTO t3 VALUES (10,3), (15,3), (20,4), (30,5);
ANALYZE TABLE t1, t2, t3;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
test.t3 analyze status Engine-independent statistics collected
test.t3 analyze status OK
# This query will normally use Table Pull-out
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
# Check that we can disable SEMIJOIN transformation
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Same with hint in outer query
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Query with two sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and `test`.`ty`.`a` = `test`.`t3`.`b`
# No SEMIJOIN transformation for first subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where `test`.`ty`.`a` = `test`.`t3`.`b` and <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))))
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(`subq1`) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where `test`.`ty`.`a` = `test`.`t3`.`b` and <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))))
# No SEMIJOIN transformation for latter subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#3`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY))))
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@`subq2`) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY))))
# No SEMIJOIN transformation for any subquery
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ NO_SEMIJOIN() */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) NO_SEMIJOIN(@`select#3`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY))))
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 Using index
# Query with nested sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`ty`.`a` = `test`.`tx`.`b` and `test`.`tx`.`a` = `test`.`t3`.`a`
# No SEMIJOIN transformation for outer subquery
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY tx eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
2 DEPENDENT SUBQUERY ty eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`tx`.`a` from `test`.`t1` `ty` join `test`.`t1` `tx` where `test`.`ty`.`a` = `test`.`tx`.`b` and <cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`)))
# No SEMIJOIN transformation for inner-most subquery
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and <expr_cache><`test`.`tx`.`b`>(<in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))))
# No SEMIJOIN transformation at all
EXPLAIN EXTENDED
SELECT /*+  NO_SEMIJOIN(@subq1) NO_SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq2`) NO_SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where <in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))) and <cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))
# This query does not support SEMIJOIN.  SEMIJOIN hint is ignored
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 4 100.00 
1 PRIMARY t2 ref a a 4 <subquery2>.min(a) 1 100.00 Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from  <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t2` where `test`.`t2`.`a` = `<subquery2>`.`min(a)`
# This query will get LooseScan by default
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Let's turn off LooseScan, FirstMatch is then SELECTed
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# Let's also turn off FirstMatch, DupsWeedout is then used.
# (StartTemporary, EndTemporary) in the output indicate DupsWeedout usage
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Materialization is used here
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where 1
# Turn off all strategies, DuplicateWeedout should be used as a fallback
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION,
DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Turn off non-used strategies, nothing should change. Still Loosescan
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Test same query with SEMIJOIN hint
# Forcing LooseScan, should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Force FirstMatch
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# Force Materialization
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where 1
# Force DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# If LooseScan is among candidates, it will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION,
DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Drop LooseScan from list of strategies, FirstMatch will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
For this query LooseScan and Materialization is not applicable
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.b 1 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` = `test`.`t1`.`b`
# Turn off all applicable strategies. DuplicateWeedout should be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
# Similar with SEMIJOIN hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
# Test multiple subqueries.
# Default for this query is Loosecan for first and FirstMatch for latter
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Forcing the default strategy should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Forcing a strategy for one, may change the other due to cost changes
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t3)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Forcing same strategy for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t3)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Loosescan for both is not possible,  ends up with DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Swap strategies compared to default
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Different subsets of strategies for different subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) SEMIJOIN(@`subq2` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Vice versa
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT)
SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Another combination
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH)
SEMIJOIN(@subq2 LOOSESCAN, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Turn off default
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN)
NO_SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Also turn off 2nd choice. Gives DuplicateWeedout over both
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Also turn off DuplicateWeedout.  Materialization is only one left.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT)
NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where 1
# Force materialization with SEMIJOIN hints instead
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where 1
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`b`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch((sj-nest))
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` LOOSESCAN, FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`b`
# A query with nested subqueries which are joined together
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using where
1 PRIMARY t2 ref a a 4 test.t3.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t3`.`a` = `test`.`t1`.`a`
# Let's turn off FirtMatch, DuplicateWeedout is then chosen
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 
1 PRIMARY t2 ref a a 4 test.t3.b 1 16.67 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t1`.`a` = `test`.`t3`.`a`
# If we turn off all strategies, DuplicateWeedout should still be used
EXPLAIN
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN, MATERIALIZATION,
DUPSWEEDOUT) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 
1 PRIMARY t2 ref a a 4 test.t3.b 1 Using index; End temporary
# Test the same query with SEMIJOIN hint
# Force FirstMatch, should not change anything
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using where
1 PRIMARY t2 ref a a 4 test.t3.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t3`.`a` = `test`.`t1`.`a`
# Force LooseScan, will fall back to DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 
1 PRIMARY t2 ref a a 4 test.t3.b 1 16.67 Using index; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t1`.`a` = `test`.`t3`.`a`
# Force DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 
1 PRIMARY t2 ref a a 4 test.t3.b 1 16.67 Using index; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t1`.`a` = `test`.`t3`.`a`
# If FirstMatch is among candidates, it will be used
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using where
1 PRIMARY t2 ref a a 4 test.t3.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t3`.`a` = `test`.`t1`.`a`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, FIRSTMATCH, LOOSESCAN,
DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3
WHERE t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using where
1 PRIMARY t2 ref a a 4 test.t3.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION, FIRSTMATCH, LOOSESCAN, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`b` and `test`.`t3`.`a` = `test`.`t1`.`a`
# Test hints with prepared statements
PREPARE stmt1 FROM "EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
           NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
  AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
DEALLOCATE PREPARE stmt1;
SET optimizer_switch = default;
# Tests with non-default optimizer_switch settings
SET optimizer_switch = 'semijoin=off';
# No table pull-out for this query
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# This should not change anything
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Force semijoin, table pull-out is performed
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
# Setting strategy should still force semijoin
# Strategy is ignored since table pull-out is done
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
# Query with two sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT a FROM t1 tx)
AND t3.b IN (SELECT a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY))))
# SEMIJOIN transformation for first subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`b`) in t1 on PRIMARY))))
# SEMIJOIN transformation for latter subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t3` where `test`.`ty`.`a` = `test`.`t3`.`b` and <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY))))
# SEMIJOIN transformation for both subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1`) SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and `test`.`ty`.`a` = `test`.`t3`.`b`
# Query with nested sub-queries
EXPLAIN EXTENDED
SELECT * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where <in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))) and <cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))
# SEMIJOIN transformation for outer subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and <expr_cache><`test`.`tx`.`b`>(<in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY))))
# SEMIJOIN transformation for inner-most subquery
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY tx eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where
2 DEPENDENT SUBQUERY ty eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SEMIJOIN(@`subq2`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`tx`.`a` from `test`.`t1` `ty` join `test`.`t1` `tx` where `test`.`ty`.`a` = `test`.`tx`.`b` and <cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`)))
# SEMIJOIN transformation for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1) SEMIJOIN(@subq2) */ * FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using where
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.tx.b 1 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq2`) SEMIJOIN(@`subq1`) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`ty`.`a` = `test`.`tx`.`b` and `test`.`tx`.`a` = `test`.`t3`.`a`
Test strategies when some are disabled by optimizer_switch
SET optimizer_switch='semijoin=on';
SET optimizer_switch='loosescan=off';
# This query will get LooseScan by default. FirstMatch now
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# Let's turn off LooseScan also by hint, FirstMatch is then selected
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# Let's also turn off FirstMatch, DupsWeedout is then used.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Turn off DupsWeedout, Materialization is used here
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where 1
# Let's force LooseScan back on
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Forcing another strategy
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where 1
# If LooseScan is among candidates, it is used even if originally disabled
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION,
DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
Disable another strategy
SET optimizer_switch='firstmatch=off';
# Turn on FirstMatch, but not LooseScan on with hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# Drop all remaining strategies with hint, should use DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# For this query LooseScan and Materialization is not applicable
# Should use DuplicateWeedout since FirstMatch is disabled
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
Turn off all applicable strategies. DuplicateWeedout should still be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, DUPSWEEDOUT) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
Reverse which strategies are allowed with hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.b 1 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` = `test`.`t1`.`b`
# Default for this query is Loosecan for first and FirstMatch for latter
# Since both strategies are disabled, will now use DuplicateWeedout
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Allowing LooseScan and FirstMatch and optimizer_switch is ignored
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH)
SEMIJOIN(@subq2 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN, FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Forcing a disabled strategy for one
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Forcing same strategy for both
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t3)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Swap strategies compared to default
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) SEMIJOIN(@subq2 LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Different subsets of strategies for different subqueries
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
SEMIJOIN(@subq2 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) SEMIJOIN(@`subq2` MATERIALIZATION, DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Turn off DuplicateWeedout for both.  Materialization is left
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT)
NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` DUPSWEEDOUT) NO_SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where 1
# Forcing materialization should have same effect
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION)
SEMIJOIN(@subq2 MATERIALIZATION) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) SEMIJOIN(@`subq2` MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where 1
# Turn off DuplicateWeedout for first.  MatLookup is used for both
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`b`
# Turn off DuplicateWeedout for second.  Same effect.
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq2 DUPSWEEDOUT) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 End temporary
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq2` DUPSWEEDOUT) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a`
Enable all strategies except DuplicateWeedout
SET optimizer_switch='firstmatch=on,loosescan=on,materialization=on,duplicateweedout=off';
# If we turn off all other strategies, DuplicateWeedout will be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# LooseScan and Materialization is not applicable, FirstMatch is used
EXPLAIN EXTENDED
SELECT * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.b 1 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` = `test`.`t1`.`b`
# Turn off all applicable strategies. DuplicateWeedout should be used
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
# Similar with SEMIJOIN hint
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
# Disable all strategies
SET optimizer_switch='firstmatch=off,loosescan=off,materialization=off,duplicateweedout=off';
# DuplicateWeedout is then used
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Turning off extra strategies should not change anything
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN, DUPSWEEDOUT) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t2 ref a a 4 test.t3.a 1 25.00 Using index; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN, DUPSWEEDOUT) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t2`.`a` = `test`.`t3`.`a`
# Turning on some strategies should give one of those
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH, MATERIALIZATION) */ *
FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index a a 4 NULL 6 100.00 Using index
1 PRIMARY t3 ref a a 4 test.t2.a 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select /*+ SEMIJOIN(@`subq1` FIRSTMATCH, MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t3`) where `test`.`t3`.`a` = `test`.`t2`.`a`
# For this query that cannot use LooseScan or Materialization,
# turning those on will still give DuplicateWeedout
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, MATERIALIZATION) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.b 1 25.00 Using where; End temporary
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, MATERIALIZATION) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` and `test`.`t1`.`b` = `test`.`t3`.`a`
# Turning on FirstMatch should give FirstMatch
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN, FIRSTMATCH) */ * FROM t1
WHERE t1.b IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3 WHERE t3.b = t1.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.b 1 100.00 Using where; FirstMatch(t1)
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN, FIRSTMATCH) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` = `test`.`t1`.`b`
SET optimizer_switch = default;
Test that setting optimizer_switch after prepare will change strategy
PREPARE stmt1 FROM "EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN)
           NO_SEMIJOIN(@subq2 FIRSTMATCH, LOOSESCAN) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
  AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
SET optimizer_switch = 'duplicateweedout=off';
Will now use materialization
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 
2 MATERIALIZED t3 index a a 4 NULL 4 100.00 Using index
3 MATERIALIZED t2 index a a 4 NULL 6 100.00 Using index
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where 1
SET optimizer_switch = 'duplicateweedout=on';
Turn DuplicateWeedout back on
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where; End temporary
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` FIRSTMATCH, LOOSESCAN) NO_SEMIJOIN(@`subq2` FIRSTMATCH, LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
DEALLOCATE PREPARE stmt1;
SET optimizer_switch = default;
# Specifying two SEMIJOIN/NO_SEMIJOIN for same query block gives warning
# First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN() is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SEMIJOIN() NO_SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Warning 4219 Hint NO_SEMIJOIN() is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
# Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Warning 4219 Hint NO_SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
# Specify at different levels, opposite order
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Duplicate hints also gives warning, but hint has effect
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint NO_SEMIJOIN(@`subq` ) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Multiple subqueries with conflicting hints
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN() */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
2 DEPENDENT SUBQUERY t3 index_subquery a a 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated
Warning 4219 Hint SEMIJOIN(@`subq2` FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq1`) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`b` and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t3 on a))))
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq2 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) NO_SEMIJOIN(LOOSESCAN) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) SEMIJOIN(LOOSESCAN) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; Start temporary; End temporary
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated
Warning 4219 Hint SEMIJOIN(@`subq2` FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) SEMIJOIN(@`subq2` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Conflicting hints in same hint comment
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) SEMIJOIN(@subq1 FIRSTMATCH) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq1` FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 LOOSESCAN) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Warning 4219 Hint NO_SEMIJOIN(@`subq1` LOOSESCAN) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
EXPLAIN EXTENDED
SELECT /*+ NO_SEMIJOIN(@subq1 LOOSESCAN) NO_SEMIJOIN(@subq1 FIRSTMATCH) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 100.00 Using where
1 PRIMARY t3 ref a a 4 test.t1.a 1 100.00 Using index; FirstMatch(t1)
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t3)
Warnings:
Warning 4219 Hint NO_SEMIJOIN(@`subq1` FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 select /*+ NO_SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t3`.`a` = `test`.`t1`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# Non-supported strategies should give warnings
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq1 INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ *
FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 index a a 4 NULL 4 100.00 Using index; LooseScan
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 25.00 Using where
1 PRIMARY t2 ref a a 4 test.t1.b 1 66.67 Using index; FirstMatch(t1)
Warnings:
Warning 1064 Optimizer hint syntax error near 'INTOEXISTS) NO_SEMIJOIN(@subq2 INTOEXISTS) */ *
FROM t1
WHERE t1.a IN (SELECT...' at line 2
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) semi join (`test`.`t2`) where `test`.`t1`.`a` = `test`.`t3`.`a` and `test`.`t2`.`a` = `test`.`t1`.`b`
# SUBQUERY tests
# SUBQUERY should disable SEMIJOIN and use specified subquery strategy
EXPLAIN EXTENDED
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
EXPLAIN
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL a 4 NULL 6 Using where; Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 Using index
# Query with two subqueries
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 MATERIALIZED ty index NULL PRIMARY 4 NULL 4 100.00 Using index
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq1` INTOEXISTS) SUBQUERY(@`subq2` MATERIALIZATION) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY)))) and <expr_cache><`test`.`t3`.`b`>(<in_optimizer>(`test`.`t3`.`b`,`test`.`t3`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` ), <primary_index_lookup>(`test`.`t3`.`b` in <temporary table> on distinct_key where `test`.`t3`.`b` = `<subquery3>`.`a`))))
# Query with nested sub-queries
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 INTOEXISTS) SUBQUERY(@subq2 MATERIALIZATION) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 DEPENDENT SUBQUERY tx unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where
3 MATERIALIZED ty index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq2` MATERIALIZATION) SUBQUERY(@`subq1` INTOEXISTS) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t3`.`a`) in t1 on PRIMARY where <in_optimizer>(`test`.`tx`.`b`,`test`.`tx`.`b` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`ty`.`a` from `test`.`t1` `ty` ), <primary_index_lookup>(`test`.`tx`.`b` in <temporary table> on distinct_key where `test`.`tx`.`b` = `<subquery3>`.`a`))) and <cache>(`test`.`t3`.`a`) = `test`.`tx`.`a`))))
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION) SUBQUERY(@subq2 INTOEXISTS) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx
WHERE tx.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
2 MATERIALIZED tx ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY ty unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq2` INTOEXISTS) SUBQUERY(@`subq1` MATERIALIZATION) */ `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where <expr_cache><`test`.`t3`.`a`>(<in_optimizer>(`test`.`t3`.`a`,`test`.`t3`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`tx`.`a` from `test`.`t1` `tx` where <expr_cache><`test`.`tx`.`b`>(<in_optimizer>(`test`.`tx`.`b`,<exists>(<primary_index_lookup>(<cache>(`test`.`tx`.`b`) in t1 on PRIMARY)))) ), <primary_index_lookup>(`test`.`t3`.`a` in <temporary table> on distinct_key where `test`.`t3`.`a` = `<subquery2>`.`a`))))
# This query does not support SEMIJOIN.  Materialization is default
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 4 100.00 
1 PRIMARY t2 ref a a 4 <subquery2>.min(a) 1 100.00 Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from  <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t2` where `test`.`t2`.`a` = `<subquery2>`.`min(a)`
# Use In-to-exists instead
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having <cache>(`test`.`t2`.`a`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))
# For this query In-to-exists is default
EXPLAIN EXTENDED
SELECT a, a IN (SELECT a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))) AS `a IN (SELECT a FROM t1)` from `test`.`t2`
# Force Subquery Materialization
EXPLAIN EXTENDED
SELECT a, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` MATERIALIZATION) */ `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) AS `a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1)` from `test`.`t2`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a,
a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) AS `a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1)` from `test`.`t2`
# This query does not support Subquery Materialization due to type mismatch
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 1 100.00 
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ sum(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having <cache>(`test`.`t2`.`a`) = <ref_null_helper>(sum(`test`.`t1`.`b`)))))
# Trying to force Subquery Materialization will not change anything
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ sum(b) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 1 100.00 
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ sum(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a` having <cache>(`test`.`t2`.`a`) = <ref_null_helper>(sum(`test`.`t1`.`b`)))))
# Test hints with prepared statements
PREPARE stmt1 FROM "EXPLAIN
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION)
           SUBQUERY(@subq2 INTOEXISTS) */ * FROM t1
WHERE t1.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3)
  AND t1.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t2)";
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
3 DEPENDENT SUBQUERY t2 index_subquery a a 4 func 1 Using index
2 MATERIALIZED t3 index NULL a 4 NULL 4 Using index
EXECUTE stmt1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
3 DEPENDENT SUBQUERY t2 index_subquery a a 4 func 1 Using index
2 MATERIALIZED t3 index NULL a 4 NULL 4 Using index
DEALLOCATE PREPARE stmt1;
# Test optimizer_switch settings with SUBQUERY hint
SET optimizer_switch='materialization=off';
This query will now use In-to-exist
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having <cache>(`test`.`t2`.`a`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))
# Force it to use Materialization
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) */ min(a) FROM t1 group by a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`min(a)`))))
SET optimizer_switch='materialization=on';
# This query will now use In-to_exists
EXPLAIN EXTENDED
SELECT a, a IN (SELECT a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))) AS `a IN (SELECT a FROM t1)` from `test`.`t2`
Force Materialization
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ a,
a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1) FROM t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a`,<expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`)))) AS `a IN (SELECT /*+ QB_NAME(subq) */ a FROM t1)` from `test`.`t2`
# Specifying both strategies should give a warning
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 MATERIALIZATION, INTOEXISTS)
SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
Warnings:
Warning 1064 Optimizer hint syntax error near ', INTOEXISTS)
SUBQUERY(@subq2 MATERIALIZATION, INTOEXISTS) */ *
FROM t3
WHERE...' at line 2
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and `test`.`ty`.`a` = `test`.`t3`.`b`
# Non-supported strategies should give warnings
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq1 FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ *
FROM t3
WHERE t3.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t1 tx)
AND t3.b IN (SELECT /*+ QB_NAME(subq2) */ a FROM t1 ty);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL a NULL NULL NULL 4 100.00 Using where
1 PRIMARY tx eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 Using index
1 PRIMARY ty eq_ref PRIMARY PRIMARY 4 test.t3.b 1 100.00 Using index
Warnings:
Warning 1064 Optimizer hint syntax error near 'FIRSTMATCH) SUBQUERY(@subq2 LOOSESCAN) */ *
FROM t3
WHERE t3.a IN (SELECT /*+...' at line 2
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` `ty` join `test`.`t1` `tx` join `test`.`t3` where `test`.`tx`.`a` = `test`.`t3`.`a` and `test`.`ty`.`a` = `test`.`t3`.`b`
SET optimizer_switch= default;
# Specifying two SUBQUERY for same query block gives warning
# First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) SUBQUERY(INTOEXISTS) */ a
FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`))))
# Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SUBQUERY(MATERIALIZATION) */ a
FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(MATERIALIZATION) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(INTOEXISTS) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(@`subq` MATERIALIZATION) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Specify at different levels, opposite order
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(MATERIALIZATION) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 MATERIALIZED t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(@`subq` INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` MATERIALIZATION) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (/* select#2 */ select /*+ QB_NAME(`subq`) */ `test`.`t1`.`a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`a`))))
# Specifying combinations of SUBQUERY and SEMIJOIN/NO_SEMIJOIN
# for same query block gives warning
# First has effect, second is ignored
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN() is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`select#2` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Try opposite order
EXPLAIN EXTENDED
SELECT * FROM t2
WHERE t2.a IN (SELECT /*+ NO_SEMIJOIN() SUBQUERY(MATERIALIZATION) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(MATERIALIZATION) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`select#2`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
# Specify at different levels, hint inside block has effect
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq MATERIALIZATION) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
1 PRIMARY t2 ref a a 4 test.t1.a 1 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(@`subq` MATERIALIZATION) is ignored as conflicting/duplicated
Note 1003 select /*+ SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a`
EXPLAIN EXTENDED
SELECT /*+ SUBQUERY(@subq INTOEXISTS) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) NO_SEMIJOIN() */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SUBQUERY(@`subq` INTOEXISTS) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ NO_SEMIJOIN(@`subq`) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
EXPLAIN EXTENDED
SELECT /*+ SEMIJOIN(@subq FIRSTMATCH) */ * FROM t2
WHERE t2.a IN (SELECT /*+ QB_NAME(subq) SUBQUERY(@subq INTOEXISTS) */ a FROM t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL a 4 NULL 6 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index
Warnings:
Warning 4219 Hint SEMIJOIN(@`subq` FIRSTMATCH) is ignored as conflicting/duplicated
Note 1003 /* select#1 */ select /*+ SUBQUERY(@`subq` INTOEXISTS) */ `test`.`t2`.`a` AS `a` from `test`.`t2` where <expr_cache><`test`.`t2`.`a`>(<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY))))
DROP TABLE t1,t2,t3;