mirror of
https://github.com/MariaDB/server.git
synced 2025-09-30 21:09:17 +02:00
aabe0c5218
Query blocks have implicit names, such as `select#1`, formulated
by appending their select number to the string `select#`. This patch
allows hints to scope their applicability by implicit query block
name. For example,
SELECT /*+ JOIN_ORDER(@`select#2` t1, t2) */ ...
@`select#2` is an implicit query block name. Users can control hint
applicability per query block without first naming the blocks with
QB_NAME().
Hints may now be specified within VIEWs during their creation and
they are applied locally within that VIEW's scope. For example,
CREATE VIEW v1 AS
SELECT /*+ IGNORE_INDEX(t1 idx1) */ FROM t1 ... GROUP BY ... HAVING ...
In many cases and for some parts of the VIEW, the query plan
doesn't really depend on how the VIEW is used, so it makes sense
to control a part of the query plan from the VIEW definition.
Implicit names are not yet supported in VIEWs. Attempting to create
a VIEW with an implicit name reference will cause the server to create
the VIEW, but it will emit a warning and exclude that hint from the query.
1473 lines
87 KiB
Text
1473 lines
87 KiB
Text
set default_storage_engine=innodb;
|
|
CREATE TABLE t1 (f1 INT NOT NULL);
|
|
INSERT INTO t1 VALUES (9),(0), (7);
|
|
CREATE TABLE t2 (f1 INT NOT NULL);
|
|
INSERT INTO t2 VALUES
|
|
(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
|
|
CREATE TABLE t3 (f1 INT NOT NULL);
|
|
INSERT INTO t3 VALUES (9),(0), (7), (4), (5);
|
|
CREATE TABLE t4 (f1 INT NOT NULL);
|
|
INSERT INTO t4 VALUES (0), (7);
|
|
CREATE TABLE t5 (f1 INT NOT NULL, PRIMARY KEY(f1));
|
|
INSERT INTO t5 VALUES (7);
|
|
CREATE TABLE t6(f1 INT NOT NULL, PRIMARY KEY(f1));
|
|
INSERT INTO t6 VALUES (7);
|
|
ANALYZE TABLE t1, t2, t3, t4, t5, t6;
|
|
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
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status OK
|
|
test.t5 analyze status Engine-independent statistics collected
|
|
test.t5 analyze status OK
|
|
test.t6 analyze status Engine-independent statistics collected
|
|
test.t6 analyze status OK
|
|
set join_cache_level = 8;
|
|
# Warning expected: hint must be ignored as malformed (no table names)
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX()*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4224 Hint JOIN_PREFIX() is ignored as malformed
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4224 Hint JOIN_PREFIX(@`q1` ) is ignored as malformed
|
|
Note 1003 select /*+ QB_NAME(`q1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Hint must be ignored as malformed (table names are not allowed for the fixed order)
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER(t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4224 Hint JOIN_FIXED_ORDER(`t2`) is ignored as malformed
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Hint is applied: QB name is allowed for the fixed order
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qb1)*/ count(*) FROM t2, t1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`qb1`) JOIN_FIXED_ORDER(@`qb1`) */ straight_join count(0) AS `count(*)` from `test`.`t2` join `test`.`t1`
|
|
# Invalid QB name for the fixed order
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qb3)*/ count(*) FROM t2, t1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4220 Query block name `qb3` is not found for JOIN_FIXED_ORDER hint
|
|
Note 1003 select /*+ QB_NAME(`qb1`) */ count(0) AS `count(*)` from `test`.`t2` join `test`.`t1`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER()*/ STRAIGHT_JOIN count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_FIXED_ORDER(@`select#1`) */ straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# JOIN_FIXED_ORDER() cannot be combined with other join order hints
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t2) JOIN_FIXED_ORDER()*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_FIXED_ORDER() is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_FIXED_ORDER() JOIN_PREFIX(t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_FIXED_ORDER(@`select#1`) */ straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Multiple join order hints of same type are not allowed (except JOIN_ORDER())
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_SUFFIX(t1) JOIN_SUFFIX(t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Multiple JOIN_ORDER() hints are applied
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2,t1) JOIN_ORDER(@q1 t3,t2)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 5 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_ORDER(@`q1` `t2`,`t1`) JOIN_ORDER(@`q1` `t3`,`t2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3`
|
|
# Variation of last syntax
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2@q1, t1) JOIN_ORDER(t3, t2@q1)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 5 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_ORDER(@`q1` `t2`@`q1`,`t1`) JOIN_ORDER(@`q1` `t3`,`t2`@`q1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3`
|
|
# Invalid mix of notations
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1 t2, t1@q1)*/ count(*)
|
|
FROM t1, t2, t3;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Warning 1064 Optimizer hint syntax error near '@q1)*/ count(*)
|
|
FROM t1, t2, t3' at line 2
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3`
|
|
# Hint is correct
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(t1@qb1, t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`qb1`) JOIN_SUFFIX(@`qb1` `t1`@`qb1`,`t2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_PREFIX(@qb1, t2)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 1064 Optimizer hint syntax error near ', t2)*/ count(*) FROM t1, t2' at line 2
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Invalid table name
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_SUFFIX(t3)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `t3` for JOIN_SUFFIX hint
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
# Invalid query block name
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(@qbXXX t1)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4220 Query block name `qbXXX` is not found for JOIN_SUFFIX hint
|
|
Note 1003 select /*+ QB_NAME(`qb1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_SUFFIX(t1, t2@qb3)*/ count(*) FROM t1, t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `t2`@`qb3` for JOIN_SUFFIX hint
|
|
Note 1003 select /*+ QB_NAME(`qb1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2`
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ QB_NAME(qb1) JOIN_FIXED_ORDER(@qbXXX)*/ count(*) FROM t2, t1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4220 Query block name `qbXXX` is not found for JOIN_FIXED_ORDER hint
|
|
Note 1003 select /*+ QB_NAME(`qb1`) */ count(0) AS `count(*)` from `test`.`t2` join `test`.`t1`
|
|
# Warning expected as the hint must not confuse `t2` in the subquery
|
|
# with `t2` in the join
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_PREFIX(t2@subq)*/ (SELECT /*+ QB_NAME(subq)*/ max(f1) FROM t2) as SQ, f1
|
|
FROM t2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
2 SUBQUERY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `t2`@`subq` for JOIN_PREFIX hint
|
|
Note 1003 /* select#1 */ select (/* select#2 */ select /*+ QB_NAME(`subq`) */ max(`test`.`t2`.`f1`) from `test`.`t2`) AS `SQ`,`test`.`t2`.`f1` AS `f1` from `test`.`t2`
|
|
# No more than 64 join order hints are allowed. It is hard to construct
|
|
# a test case where 64 hints will be applicable and only one, exceeding
|
|
# the maximum, will produce a warning. Usually so many hints will conflict
|
|
# with each other and generate warnings.
|
|
# But in the case below we can observe that the first warning
|
|
# is about JOIN_ORDER(t1,t2,t3,t4,t5,t6) which is actually
|
|
# the last hint in the list, and it is the 65th in order.
|
|
# This happens because the check for exceeding the maximum is performed
|
|
# during the preparation phase, while other checks are performed during
|
|
# hints application. So, if JOIN_ORDER(t1,t2,t3,t4,t5,t6)
|
|
# is mentioned first in the list of warnings, the test case passes.
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+
|
|
JOIN_PREFIX(t2,t1) JOIN_SUFFIX(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2) JOIN_ORDER(t2,t1) JOIN_ORDER(t3,t2)
|
|
JOIN_ORDER(t1,t2,t3,t4,t5,t6) */
|
|
count(*) FROM t1, t2, t3;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t1`,`t2`,`t3`,`t4`,`t5`,`t6`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_SUFFIX(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_ORDER(`t3`,`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t1`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3`
|
|
# Original query with no hints
|
|
SELECT count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# Check name resolving
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(t3, t2, t2@subq2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t2) AND
|
|
t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t2);
|
|
count(*)
|
|
300
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_PREFIX(t3, t2, t2@subq2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t2) AND
|
|
t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t2.f1 20 5.00 Using where; FirstMatch(t2); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 20 100.00
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_PREFIX(@`q1` `t3`,`t2`,`t2`@`subq2`) */ count(0) AS `count(*)` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2`) join `test`.`t2` join `test`.`t3` where `test`.`t2`.`f1` = `test`.`t2`.`f1`
|
|
# Check conflicting hints
|
|
# Second JOIN_PREFIX is conflicting
|
|
SELECT /*+ JOIN_PREFIX(t3, t2, t1) JOIN_PREFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_PREFIX(t3, t2, t1) JOIN_PREFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t3`,`t2`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Second JOIN_SUFFIX is conflicting
|
|
SELECT /*+ JOIN_SUFFIX(t3, t2) JOIN_SUFFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_SUFFIX(t3, t2) JOIN_SUFFIX(t2, t1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t3`,`t2`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# Both JOIN_ORDERs applicable
|
|
SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t1, t2, t5@subq2) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t1, t2, t5@subq2) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (flat, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t3`,`t2`) JOIN_ORDER(@`select#1` `t1`,`t2`,`t5`@`subq2`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Unresolved table name t7 in JOIN_ORDER hint, hint ignored
|
|
SELECT /*+ JOIN_ORDER(t1, t7, t5) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `t7` for JOIN_ORDER hint
|
|
explain extended SELECT /*+ JOIN_ORDER(t1, t7, t5) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `t7` for JOIN_ORDER hint
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# All hints are applicable
|
|
SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
|
|
JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
|
|
JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`) JOIN_ORDER(@`select#1` `t4`@`subq1`,`t3`) JOIN_SUFFIX(@`select#1` `t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Second JOIN_ORDER is ignored
|
|
SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t2, t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t3`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_ORDER(t3, t2) JOIN_ORDER(t2, t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t3`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t3`,`t2`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# JOIN_SUFFIX is ignored
|
|
SELECT /*+ JOIN_ORDER(t3, t2) JOIN_SUFFIX(t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t3`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_ORDER(t3, t2) JOIN_SUFFIX(t3) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t3`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t3`,`t2`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# JOIN_PREFIX is ignored
|
|
SELECT /*+ JOIN_ORDER(t3, t2) JOIN_PREFIX(t2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_ORDER(t3, t2) JOIN_PREFIX(t2) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t3`,`t2`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# All hints are applicable
|
|
SELECT /*+ JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) JOIN_PREFIX(t2, t5@subq2, t4@subq1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_ORDER(t4@subq1, t3) JOIN_SUFFIX(t1) JOIN_PREFIX(t2, t5@subq2, t4@subq1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t4`@`subq1`,`t3`) JOIN_SUFFIX(@`select#1` `t1`) JOIN_PREFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# JOIN_PREFIX with all tables.
|
|
SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# JOIN_SUFFIX with all tables.
|
|
SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# JOIN_ORDER with all tables.
|
|
SELECT /*+ JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# JOIN_PREFIX, JOIN_ORDER, JOIN_SUFFIX with all tables.
|
|
SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_SUFFIX(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_ORDER(t2, t5@subq2, t4@subq1, t3, t1)
|
|
JOIN_PREFIX(t2, t5@subq2, t4@subq1, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) JOIN_ORDER(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) JOIN_PREFIX(@`select#1` `t2`,`t5`@`subq2`,`t4`@`subq1`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# JOIN_ORDER is ignored due to STRAIGHT_JOIN.
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2, t1) */ STRAIGHT_JOIN count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_ORDER(t2, t1) */ STRAIGHT_JOIN count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
3 MATERIALIZED t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 /* select#1 */ select /*+ QB_NAME(`q1`) */ straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3` where <expr_cache><`test`.`t1`.`f1`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`f1` from `test`.`t4` where <cache>(`test`.`t1`.`f1`) = `test`.`t4`.`f1`))) and <expr_cache><`test`.`t2`.`f1`>(<in_optimizer>(`test`.`t2`.`f1`,`test`.`t2`.`f1` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`t5`.`f1` from `test`.`t5` ), <primary_index_lookup>(`test`.`t2`.`f1` in <temporary table> on distinct_key where `test`.`t2`.`f1` = `<subquery3>`.`f1`))))
|
|
# Test JOIN_FIXED_ORDER.
|
|
SELECT /*+ QB_NAME(q1) JOIN_FIXED_ORDER(@q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_FIXED_ORDER(@q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
3 MATERIALIZED t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select /*+ QB_NAME(`q1`) JOIN_FIXED_ORDER(@`q1`) */ straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3` where <expr_cache><`test`.`t1`.`f1`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`f1` from `test`.`t4` where <cache>(`test`.`t1`.`f1`) = `test`.`t4`.`f1`))) and <expr_cache><`test`.`t2`.`f1`>(<in_optimizer>(`test`.`t2`.`f1`,`test`.`t2`.`f1` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`t5`.`f1` from `test`.`t5` ), <primary_index_lookup>(`test`.`t2`.`f1` in <temporary table> on distinct_key where `test`.`t2`.`f1` = `<subquery3>`.`f1`))))
|
|
SELECT /*+ JOIN_FIXED_ORDER() */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_FIXED_ORDER() */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
3 MATERIALIZED t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select /*+ JOIN_FIXED_ORDER(@`select#1`) */ straight_join count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` join `test`.`t3` where <expr_cache><`test`.`t1`.`f1`>(<in_optimizer>(`test`.`t1`.`f1`,<exists>(/* select#2 */ select /*+ QB_NAME(`subq1`) */ `test`.`t4`.`f1` from `test`.`t4` where <cache>(`test`.`t1`.`f1`) = `test`.`t4`.`f1`))) and <expr_cache><`test`.`t2`.`f1`>(<in_optimizer>(`test`.`t2`.`f1`,`test`.`t2`.`f1` in ( <materialize> (/* select#3 */ select /*+ QB_NAME(`subq2`) */ `test`.`t5`.`f1` from `test`.`t5` ), <primary_index_lookup>(`test`.`t2`.`f1` in <temporary table> on distinct_key where `test`.`t2`.`f1` = `<subquery3>`.`f1`))))
|
|
SELECT /*+ QB_NAME(q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ QB_NAME(q1) */ count(*) FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# Testing STRAIGHT_JOIN
|
|
SELECT count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` straight_join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# t3 can not be first
|
|
SELECT /*+ JOIN_PREFIX(t3, t1) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
count(*)
|
|
10
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t3`,`t1`) is ignored as conflicting/duplicated
|
|
explain extended SELECT /*+ JOIN_PREFIX(t3, t1) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t3`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` straight_join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# Hint is applicable
|
|
SELECT /*+ JOIN_PREFIX(t1, t2, t3) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_PREFIX(t1, t2, t3) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t1`,`t2`,`t3`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` straight_join `test`.`t3` where `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Hint is applicable
|
|
SELECT /*+ JOIN_SUFFIX(t4, t5) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ JOIN_SUFFIX(t4, t5) */ count(*) FROM t1 JOIN t2 STRAIGHT_JOIN t3
|
|
WHERE t1.f1 IN (SELECT f1 FROM t4) AND t2.f1 IN (SELECT f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t3); Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t4`,`t5`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` straight_join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Alternative syntax
|
|
SELECT /*+ QB_NAME(q1) JOIN_ORDER(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_ORDER(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (flat, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 4 test.t1.f1 2 50.00 Using where; FirstMatch(t1); Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_ORDER(@`q1` `t2`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t4`.`f1` = `test`.`t1`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
# Alternative syntax
|
|
SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_PREFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 20 100.00
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 100.00
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_PREFIX(@`q1` `t2`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t5`.`f1` = `test`.`t2`.`f1`
|
|
# Alternative syntax
|
|
SELECT /*+ QB_NAME(q1) JOIN_SUFFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
count(*)
|
|
10
|
|
explain extended SELECT /*+ QB_NAME(q1) JOIN_SUFFIX(@q1 t2, t3, t1) */ count(*)
|
|
FROM t1 JOIN t2 JOIN t3
|
|
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
|
|
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t5 ALL PRIMARY NULL NULL NULL 1 100.00
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Start temporary; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t2 hash_ALL NULL #hash#$hj 4 test.t5.f1 20 11.11 Using where; Using join buffer (incremental, BNLH join)
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (incremental, BNL join)
|
|
1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t4.f1 3 16.67 Using where; End temporary; Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select /*+ QB_NAME(`q1`) JOIN_SUFFIX(@`q1` `t2`,`t3`,`t1`) */ count(0) AS `count(*)` from `test`.`t5` semi join (`test`.`t4`) join `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`f1` = `test`.`t4`.`f1` and `test`.`t2`.`f1` = `test`.`t5`.`f1`
|
|
DROP TABLE t1, t2, t3, t4 ,t5, t6;
|
|
# Testing embedded join
|
|
CREATE TABLE t1 (f1 INT);
|
|
CREATE TABLE t2 (f1 INT);
|
|
CREATE TABLE t3 (f1 INT);
|
|
CREATE TABLE t4 (f1 INT);
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t4, t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t4`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 select 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t1, t4) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t2`,`t1`,`t4`) */ 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t4, t1, t2) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t4`,`t1`,`t2`) */ 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t3, t4) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t3`,`t4`) */ 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
EXPLAIN SELECT /*+ JOIN_ORDER(t4, t3) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join)
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_SUFFIX(t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t1`) */ 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_SUFFIX(t2, t1) */ 1 FROM t1
|
|
JOIN t2 ON 1
|
|
RIGHT JOIN t3 ON 1
|
|
JOIN t4 ON 1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join)
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_SUFFIX(@`select#1` `t2`,`t1`) */ 1 AS `1` from `test`.`t3` left join (`test`.`t1` join `test`.`t2`) on(1 and 1) join `test`.`t4` where 1
|
|
DROP TABLE t1, t2, t3, t4;
|
|
CREATE TABLE t1
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE TABLE t2
|
|
(
|
|
f1 INT(11) DEFAULT NULL
|
|
);
|
|
INSERT INTO t2 VALUES (0),(1),(2);
|
|
CREATE TABLE t3
|
|
(
|
|
f1 INT(11) DEFAULT NULL
|
|
);
|
|
INSERT INTO t3 VALUES (0),(1),(2);
|
|
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
|
|
# Original query
|
|
EXPLAIN EXTENDED DELETE
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 33.33 Using where; Start temporary; End temporary
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00 Using where
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
# Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00
|
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 delete /*+ JOIN_PREFIX(@`select#1` `t2`,`t3`,`ta2`) */ from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
# Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta1, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00 Using where
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00
|
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 delete /*+ JOIN_PREFIX(@`select#1` `t2`,`t3`,`ta1`,`ta2`) */ from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
# Applicable hint
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(t2, t3, ta2, ta1) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00
|
|
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 delete /*+ JOIN_PREFIX(@`select#1` `t2`,`t3`,`ta2`,`ta1`) */ from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
# Hint should be ignored
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_SUFFIX(ta2, t3, ta1) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 33.33 Using where; Start temporary; End temporary
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00 Using where
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`ta2`,`t3`,`ta1`) is ignored as conflicting/duplicated
|
|
Note 1003 delete from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
# Both hints are ignored
|
|
EXPLAIN EXTENDED DELETE /*+ JOIN_PREFIX(ta1, t2, t3) JOIN_SUFFIX(t3, ta2) */
|
|
FROM ta1.* USING t1 AS ta1 JOIN t1 AS ta2 ON 1
|
|
RIGHT OUTER JOIN t2 ON (ta1.f1 = t2.f1)
|
|
WHERE (9) IN (SELECT f1 FROM t3);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 33.33 Using where; Start temporary; End temporary
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
|
|
1 PRIMARY ta1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 100.00 Using where
|
|
1 PRIMARY ta2 ALL NULL NULL NULL NULL 3 100.00
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`ta1`,`t2`,`t3`) is ignored as conflicting/duplicated
|
|
Warning 4219 Hint JOIN_SUFFIX(`t3`,`ta2`) is ignored as conflicting/duplicated
|
|
Note 1003 delete from `test`.`t1` `ta1` using `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta1`.`f1` = `test`.`t2`.`f1` and 1 and `test`.`t2`.`f1` is not null) where `test`.`t3`.`f1` = 9
|
|
DROP TABLE t1, t2, t3;
|
|
# Const table behavior, table order is not changed, hint is applicable.
|
|
# Note: Const tables are excluded from the process of dependency setting
|
|
# since they are always first in the table order. Note that it
|
|
# does not prevent the hint from being applied to the non-const
|
|
# tables of the hint.
|
|
CREATE TABLE t1(f1 INT) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TABLE t2(f1 INT) ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1);
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t1, t2) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t1`,`t2`) */ 1 AS `1` from `test`.`t2` where `test`.`t2`.`f1` = 1
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t2, t1) */ 1 FROM t1 JOIN t2 ON t1.f1 = t2.f1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`,`t1`) */ 1 AS `1` from `test`.`t2` where `test`.`t2`.`f1` = 1
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#23144274 WL9158:ASSERTION `JOIN->BEST_READ < DOUBLE(1.79769313486231570815E+308L)' FAILED
|
|
#
|
|
CREATE TABLE t1 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (f1));
|
|
CREATE TABLE t2 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 varchar(255) CHARACTER SET utf8mb3 DEFAULT NULL,
|
|
f3 varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f3(f3));
|
|
ANALYZE TABLE t1, t2;
|
|
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
|
|
EXPLAIN SELECT /*+ JOIN_SUFFIX(t1, t2) */ t2.f3 FROM t2 LEFT JOIN t1
|
|
ON t2.f1 = t1.f1 WHERE t1.f2 NOT LIKE ('FMGAU') OR t2.f2 > 't';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 1
|
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.f1 1 Using where
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_SUFFIX(`t1`,`t2`) is ignored as conflicting/duplicated
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1
|
|
(
|
|
f1 int(11) DEFAULT NULL,
|
|
KEY f1 (f1)
|
|
);
|
|
CREATE TABLE t2
|
|
(
|
|
f1 int(11) DEFAULT NULL,
|
|
f2 varchar(255) CHARACTER SET utf8mb3 DEFAULT NULL,
|
|
KEY f2 (f2),
|
|
KEY f1 (f1)
|
|
);
|
|
CREATE TABLE t3 (
|
|
f1 int(11) DEFAULT NULL,
|
|
f2 varchar(255) CHARACTER SET cp932 DEFAULT NULL,
|
|
KEY f1 (f1),
|
|
KEY f2 (f2)
|
|
);
|
|
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
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t2, t3) JOIN_ORDER(t1, t2) */ t3.f1
|
|
FROM ( t2 INNER JOIN t3 ON t3.f2 = t2.f2 LEFT JOIN t1 ON t1.f1 = t3.f1 )
|
|
WHERE NOT (t2.f1 >= 7);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 range f2,f1 f1 5 NULL 1 100.00 Using index condition
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE t1 ref f1 f1 5 test.t3.f1 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t1`,`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t2`,`t3`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t2` join `test`.`t3` left join `test`.`t1` on(`test`.`t1`.`f1` = `test`.`t3`.`f1` and `test`.`t3`.`f1` is not null) where `test`.`t2`.`f1` < 7 and convert(`test`.`t3`.`f2` using utf8mb3) = `test`.`t2`.`f2`
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */ t3.f1
|
|
FROM ( t2 INNER JOIN t3 ON t3.f2 = t2.f2 LEFT JOIN t1 ON t1.f1 = t3.f1 )
|
|
WHERE NOT (t2.f1 >= 7);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 100.00
|
|
1 SIMPLE t1 ref f1 f1 5 test.t3.f1 1 100.00 Using where; Using index
|
|
1 SIMPLE t2 ref f2,f1 f2 768 func 1 100.00 Using index condition; Using where
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t2`,`t3`) is ignored as conflicting/duplicated
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `t1`,`t2`) */ `test`.`t3`.`f1` AS `f1` from `test`.`t2` join `test`.`t3` left join `test`.`t1` on(`test`.`t1`.`f1` = `test`.`t3`.`f1` and `test`.`t3`.`f1` is not null) where `test`.`t2`.`f1` < 7 and convert(`test`.`t3`.`f2` using utf8mb3) = `test`.`t2`.`f2`
|
|
DROP TABLE t1, t2, t3;
|
|
CREATE TABLE t1 (
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
f2 INT(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
CREATE TABLE t2 (
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
);
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t1, t1) */ t2.f1 FROM t1 JOIN t2 ON t1.f2 = t2.f1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.f2 1 100.00
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_PREFIX(`t1`,`t1`) is ignored as conflicting/duplicated
|
|
Note 1003 select `test`.`t2`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`f1` = `test`.`t1`.`f2`
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1
|
|
(
|
|
f1 DATETIME,
|
|
f2 DATE,
|
|
f3 VARCHAR(1),
|
|
KEY (f1)
|
|
) ENGINE=myisam;
|
|
CREATE TABLE t2
|
|
(
|
|
f1 VARCHAR(1),
|
|
f2 INT,
|
|
f3 VARCHAR(1),
|
|
KEY (f1)
|
|
) ENGINE=innodb;
|
|
CREATE TABLE t3
|
|
(
|
|
f1 VARCHAR(1),
|
|
f2 DATE,
|
|
f3 DATETIME,
|
|
f4 INT
|
|
) ENGINE=myisam;
|
|
ANALYZE TABLE t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
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 Table is already up to date
|
|
EXPLAIN EXTENDED
|
|
UPDATE /*+ JOIN_ORDER(t2, als1, als3) JOIN_FIXED_ORDER() */ t3 AS als1
|
|
JOIN t1 AS als2 ON (als1.f3 = als2 .f1)
|
|
JOIN t1 AS als3 ON (als1.f1 = als3.f3)
|
|
RIGHT OUTER JOIN t3 AS als4 ON (als1.f3 = als4.f2)
|
|
SET als1.f4 = 'eogqjvbhzodzimqahyzlktkbexkhdwxwgifikhcgblhgswxyutepc'
|
|
WHERE ('i','b') IN (SELECT f3, f1 FROM t2 WHERE f2 <> f2 AND als2.f2 IS NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_FIXED_ORDER() is ignored as conflicting/duplicated
|
|
Note 1276 Field or reference 'test.als2.f2' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 update /*+ JOIN_ORDER(@`select#1` `t2`,`als1`,`als3`) */ (`test`.`t2`) join `test`.`t3` `als4` left join (`test`.`t3` `als1` join `test`.`t1` `als2` join `test`.`t1` `als3`) on(`test`.`als1`.`f3` = `test`.`als4`.`f2` and multiple equal(`test`.`als1`.`f1`, `test`.`als3`.`f3`) and multiple equal(`test`.`als1`.`f3`, `test`.`als2`.`f1`)) set `test`.`als1`.`f4` = 'eogqjvbhzodzimqahyzlktkbexkhdwxwgifikhcgblhgswxyutepc' where 0
|
|
DROP TABLE t1, t2, t3;
|
|
CREATE TABLE t1(
|
|
f1 VARCHAR(1)) ENGINE=myisam;
|
|
CREATE TABLE t2(
|
|
f1 VARCHAR(1),
|
|
f2 VARCHAR(1),
|
|
f3 DATETIME,
|
|
KEY(f2)) ENGINE=innodb;
|
|
CREATE TABLE t3(
|
|
f1 INT,
|
|
f2 DATE,
|
|
f3 VARCHAR(1),
|
|
KEY(f3)) ENGINE=myisam;
|
|
CREATE TABLE t4(
|
|
f1 VARCHAR(1),
|
|
KEY(f1)) ENGINE=innodb;
|
|
ALTER TABLE t4 DISABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Storage engine InnoDB of the table `test`.`t4` doesn't have this option
|
|
INSERT INTO t4 VALUES ('x'), (NULL), ('d'), ('x'), ('u');
|
|
ALTER TABLE t4 ENABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Storage engine InnoDB of the table `test`.`t4` doesn't have this option
|
|
CREATE TABLE t5(
|
|
f1 VARCHAR(1),
|
|
KEY(f1) ) ENGINE=myisam;
|
|
INSERT INTO t5 VALUES (NULL), ('s'), ('c'), ('x'), ('z');
|
|
ANALYZE TABLE t1, t2, t3, t4, t5;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
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 Table is already up to date
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status OK
|
|
test.t5 analyze status Engine-independent statistics collected
|
|
test.t5 analyze status OK
|
|
EXPLAIN EXTENDED UPDATE /*+ JOIN_ORDER(t4, alias1, alias3) */ t3 AS alias1
|
|
JOIN t5 ON (alias1.f3 = t5.f1)
|
|
JOIN t3 AS alias3 ON (alias1.f2 = alias3.f2 )
|
|
RIGHT OUTER JOIN t1 ON (alias1.f3 = t1.f1)
|
|
SET alias1.f1 = -1
|
|
WHERE ( 'v', 'o' ) IN
|
|
(SELECT DISTINCT t2.f1, t2.f2 FROM t4 RIGHT OUTER JOIN t2 ON (t4.f1 = t2.f1)
|
|
WHERE t2.f3 BETWEEN '2001-10-04' AND '2003-05-15');
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
Warnings:
|
|
Note 1003 update /*+ JOIN_ORDER(@`select#1` `t4`,`alias1`,`alias3`) */ `test`.`t1` semi join (`test`.`t2` left join `test`.`t4` on(`test`.`t4`.`f1` = `test`.`t2`.`f1` and `test`.`t2`.`f1` is not null)) left join (`test`.`t3` `alias1` join `test`.`t5` join `test`.`t3` `alias3`) on(`test`.`alias1`.`f3` = NULL and `test`.`t5`.`f1` = NULL and `test`.`alias3`.`f2` = `test`.`alias1`.`f2` and NULL is not null and `test`.`alias1`.`f2` is not null and NULL is not null) set `test`.`alias1`.`f1` = -1 where 0
|
|
DROP TABLE t1, t2, t3, t4, t5;
|
|
CREATE TABLE t1 (
|
|
f1 INT(11) DEFAULT NULL,
|
|
f3 VARCHAR(1) DEFAULT NULL,
|
|
f2 INT(11) DEFAULT NULL,
|
|
KEY (f1)
|
|
) ENGINE=MyISAM;
|
|
CREATE TABLE t2(
|
|
f1 INT(11) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
CREATE TABLE t3 (
|
|
f1 VARCHAR(1) DEFAULT NULL,
|
|
f2 VARCHAR(1) DEFAULT NULL,
|
|
KEY (f2)
|
|
) ENGINE=InnoDB;
|
|
ANALYZE TABLE t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
EXPLAIN EXTENDED UPDATE /*+ JOIN_SUFFIX(ta1, t2) */
|
|
t1 AS ta1 JOIN t1 AS ta2 ON ta1.f1 = ta2.f1 RIGHT JOIN t2 ON (ta1.f1 = t2.f1)
|
|
SET ta1.f2 = '', ta2.f3 = ''
|
|
WHERE ('n', 'r') IN (SELECT f2, f1 FROM t3 WHERE f1 <> f2 XOR ta2.f3 IS NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.ta2.f3' of SELECT #2 was resolved in SELECT #1
|
|
Note 1003 update /*+ JOIN_SUFFIX(@`select#1` `ta1`,`t2`) */ `test`.`t2` semi join (`test`.`t3`) left join (`test`.`t1` `ta1` join `test`.`t1` `ta2`) on(`test`.`ta2`.`f1` = NULL and `test`.`ta1`.`f1` = NULL and NULL is not null and NULL is not null) set `test`.`ta1`.`f2` = '',`test`.`ta2`.`f3` = '' where 0
|
|
DROP TABLE t1, t2, t3;
|
|
CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) charset latin1 ENGINE=MyISAM;
|
|
CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1))
|
|
charset latin1 ENGINE=MyISAM;
|
|
CREATE TABLE t5(f1 INT(11) NOT NULL AUTO_INCREMENT, f2 INT(11) DEFAULT NULL, PRIMARY KEY (f1))
|
|
charset latin1 ENGINE=InnoDB;
|
|
CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1))
|
|
charset latin1 ENGINE=InnoDB;
|
|
CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL)
|
|
charset latin1 ENGINE=InnoDB;
|
|
CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),KEY (f2),KEY (f3)) charset latin1 ENGINE=MyISAM;
|
|
CREATE TABLE t11(f1 INT(11) DEFAULT NULL,f2 VARCHAR(10) DEFAULT NULL,
|
|
KEY (f1),KEY (f2)) charset latin1 ENGINE=InnoDB;
|
|
ANALYZE TABLE t2, t4, t5, t6, t7, t10, t11;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status Table is already up to date
|
|
test.t5 analyze status Engine-independent statistics collected
|
|
test.t5 analyze status OK
|
|
test.t6 analyze status Engine-independent statistics collected
|
|
test.t6 analyze status OK
|
|
test.t7 analyze status Engine-independent statistics collected
|
|
test.t7 analyze status OK
|
|
test.t10 analyze status Engine-independent statistics collected
|
|
test.t10 analyze status Table is already up to date
|
|
test.t11 analyze status Engine-independent statistics collected
|
|
test.t11 analyze status OK
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_ORDER(alias11, alias8) */ 1
|
|
FROM t4 AS alias4
|
|
LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
|
|
LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
|
|
ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
|
|
JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
|
|
ON alias4.f2 = alias11.f2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
Warnings:
|
|
Note 1105 Cannot use key `f2` part[0] for lookup: `test`.`t11`.`f2` of type `varchar` = "`alias4`.`f2`" of type `int`
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `alias11`,`alias8`) */ 1 AS `1` from `test`.`t4` `alias4` left join (`test`.`t5` `alias5` join `test`.`t6` `alias6` left join (`test`.`t7` `alias7` join `test`.`t2` `alias8`) on(`test`.`alias8`.`f2` = `test`.`alias5`.`f1` and `test`.`alias8`.`f1` = `test`.`alias7`.`f1` and `test`.`alias7`.`f1` is not null)) on(`test`.`alias5`.`f2` = NULL and `test`.`alias6`.`f1` = NULL and NULL is not null and `test`.`alias5`.`f2` is not null) join `test`.`t10` `alias10` join `test`.`t11` `alias11` where 0
|
|
EXPLAIN EXTENDED
|
|
SELECT /*+ JOIN_ORDER(alias11, alias10, alias8, alias7) */ 1
|
|
FROM t4 AS alias4
|
|
LEFT JOIN t5 AS alias5 JOIN t6 AS alias6 ON alias5.f2 = alias6.f1
|
|
LEFT JOIN t7 AS alias7 JOIN t2 AS alias8 ON alias7.f1 = alias8.f1
|
|
ON alias5.f1 = alias8.f2 ON alias4.f2 = alias6.f1
|
|
JOIN t10 AS alias10 JOIN t11 AS alias11 ON alias10.f1 = alias11.f1
|
|
ON alias4.f2 = alias11.f2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
Warnings:
|
|
Note 1105 Cannot use key `f2` part[0] for lookup: `test`.`t11`.`f2` of type `varchar` = "`alias4`.`f2`" of type `int`
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `alias11`,`alias10`,`alias8`,`alias7`) */ 1 AS `1` from `test`.`t4` `alias4` left join (`test`.`t5` `alias5` join `test`.`t6` `alias6` left join (`test`.`t7` `alias7` join `test`.`t2` `alias8`) on(`test`.`alias8`.`f2` = `test`.`alias5`.`f1` and `test`.`alias7`.`f1` = `test`.`alias8`.`f1` and `test`.`alias5`.`f1` is not null and `test`.`alias8`.`f1` is not null)) on(`test`.`alias5`.`f2` = NULL and `test`.`alias6`.`f1` = NULL and NULL is not null and `test`.`alias5`.`f2` is not null) join `test`.`t10` `alias10` join `test`.`t11` `alias11` where 0
|
|
DROP TABLES t2, t4, t5, t6, t7, t10, t11;
|
|
CREATE TABLE t1 (f1 VARCHAR(255) DEFAULT NULL, f2 VARCHAR(255) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 VARCHAR(255) DEFAULT NULL, f2 INT(11) DEFAULT NULL,
|
|
KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
CREATE TABLE t3(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT, f2 VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (f1), KEY (f2)) ENGINE=InnoDB;
|
|
CREATE TABLE t4(f1 INT(11) DEFAULT NULL, f2 INT(11) DEFAULT NULL, KEY (f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t6(f1 INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t7 (f1 VARCHAR(255) DEFAULT NULL) ENGINE=InnoDB;
|
|
CREATE TABLE t10(f1 INT(11) NOT NULL AUTO_INCREMENT,f2 INT(11) DEFAULT NULL,f3 VARCHAR(10) DEFAULT NULL,
|
|
PRIMARY KEY (f1),KEY (f2),KEY (f3)) ENGINE=InnoDB;
|
|
ANALYZE TABLE t1, t2, t3, t4, t5, t6, t7, t10;
|
|
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
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status OK
|
|
test.t5 analyze Error Table 'test.t5' doesn't exist
|
|
test.t5 analyze status Operation failed
|
|
test.t6 analyze status Engine-independent statistics collected
|
|
test.t6 analyze status OK
|
|
test.t7 analyze status Engine-independent statistics collected
|
|
test.t7 analyze status OK
|
|
test.t10 analyze status Engine-independent statistics collected
|
|
test.t10 analyze status OK
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_ORDER(alias8, alias6) */ 1
|
|
FROM t1 AS alias1
|
|
LEFT JOIN t7 alias7
|
|
JOIN t2 AS alias2
|
|
LEFT JOIN t3 AS alias3 JOIN t4 AS alias4 ON alias4.f2 = alias3.f1
|
|
ON alias4.f1 = alias2.f1
|
|
ON alias2.f2 = alias7.f1
|
|
JOIN t10 AS alias5
|
|
LEFT JOIN t6 AS alias6 JOIN t2 AS alias8 ON alias6.f1 = alias8.f2
|
|
ON alias6.f1 = alias5.f1
|
|
ON alias5.f3 = alias7.f1
|
|
ON alias1.f2 = alias7.f1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE alias1 index NULL f2 1023 NULL 1 100.00 Using index
|
|
1 SIMPLE alias7 ALL NULL NULL NULL NULL 1 0.00 Using where
|
|
1 SIMPLE alias2 ref f2 f2 5 test.alias7.f1 1 100.00 Using where
|
|
1 SIMPLE alias4 ref f1 f1 5 test.alias2.f1 1 100.00 Using where
|
|
1 SIMPLE alias3 eq_ref PRIMARY PRIMARY 4 test.alias4.f2 1 100.00
|
|
1 SIMPLE alias5 ref f3 f3 43 test.alias7.f1 1 100.00 Using where; Using index
|
|
1 SIMPLE alias8 ref f2 f2 5 test.alias5.f1 1 100.00 Using where; Using index
|
|
1 SIMPLE alias6 eq_ref PRIMARY PRIMARY 4 test.alias8.f2 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_ORDER(@`select#1` `alias8`,`alias6`) */ 1 AS `1` from `test`.`t1` `alias1` left join (`test`.`t7` `alias7` join `test`.`t2` `alias2` left join (`test`.`t3` `alias3` join `test`.`t4` `alias4`) on(`test`.`alias3`.`f1` = `test`.`alias4`.`f2` and `test`.`alias4`.`f1` = `test`.`alias2`.`f1` and `test`.`alias2`.`f1` is not null and `test`.`alias4`.`f2` is not null) join `test`.`t10` `alias5` left join (`test`.`t6` `alias6` join `test`.`t2` `alias8`) on(`test`.`alias8`.`f2` = `test`.`alias5`.`f1` and `test`.`alias6`.`f1` = `test`.`alias5`.`f1` and `test`.`alias5`.`f1` is not null and `test`.`alias8`.`f2` is not null)) on(`test`.`alias7`.`f1` = `test`.`alias1`.`f2` and `test`.`alias5`.`f3` = `test`.`alias1`.`f2` and `test`.`alias2`.`f2` = `test`.`alias7`.`f1` and `test`.`alias1`.`f2` is not null and `test`.`alias7`.`f1` is not null and `test`.`alias7`.`f1` is not null) where 1
|
|
DROP TABLES t1, t2, t3, t4, t6, t7, t10;
|
|
#
|
|
# Bug#23144230 WL#9158 : OPT_HINTS_QB::APPLY_JOIN_ORDER_HINTS - MYSQLD GOT SIGNAL 11
|
|
#
|
|
CREATE TABLE t1 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) DEFAULT NULL,
|
|
f3 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f2 (f2))
|
|
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
|
|
CREATE TABLE t2 (
|
|
f1 int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) DEFAULT NULL,
|
|
f3 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY f2 (f2))
|
|
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
|
|
ANALYZE TABLE t1, t2;
|
|
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
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(alias1) */ alias2.f3 AS field1
|
|
FROM ( SELECT * FROM t1 ) AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2
|
|
ON alias1.f2 = alias2.f3
|
|
WHERE alias2.f2 IS NULL HAVING (field1 != 3 AND field1 >= 8);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ref f2 f2 5 const 1 100.00 Using index condition; Using where
|
|
1 SIMPLE t1 ref f2 f2 5 test.t2.f3 1 100.00 Using index
|
|
Warnings:
|
|
Warning 4221 Unresolved table name `alias1` for JOIN_PREFIX hint
|
|
Note 1003 select `test`.`t2`.`f3` AS `field1` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`f2` = `test`.`t2`.`f3` and `test`.`t2`.`f2` is null having `f3` <> 3 and `f3` >= 8
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#23651098 WL#9158 : ASSERTION `!(SJ_NEST->SJ_INNER_TABLES & JOIN->CONST_TABLE_MAP)' FAILED
|
|
#
|
|
CREATE TABLE t1
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (f1)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE t2
|
|
(
|
|
f1 VARCHAR(1) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
CREATE TABLE t3
|
|
(
|
|
f1 VARCHAR(1) DEFAULT NULL
|
|
) ENGINE=MyISAM;
|
|
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 Table is already up to date
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status Table is already up to date
|
|
EXPLAIN EXTENDED SELECT /*+ JOIN_PREFIX(t2, t1) */ t1.f1 FROM t1, t2
|
|
WHERE t2.f1 IN (SELECT t3.f1 FROM t3) AND t1.f1 = 183;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`,`t1`) */ NULL AS `f1` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` where 0
|
|
DROP TABLE t1, t2, t3;
|
|
#
|
|
# Bug23715779 SELECT QUERY WITH JOIN_PREFIX() HINT RETURNS INCORRECT RESULT
|
|
#
|
|
CREATE TABLE t1(f1 INT(11) NOT NULL);
|
|
INSERT INTO t1 VALUES (10);
|
|
CREATE TABLE t2
|
|
(
|
|
f1 INT(11) NOT NULL AUTO_INCREMENT,
|
|
f2 INT(11) DEFAULT NULL,
|
|
PRIMARY KEY (f1),
|
|
KEY (f2)
|
|
);
|
|
INSERT INTO t2 VALUES (1, 7), (2, 1), (4, 7);
|
|
CREATE TABLE t4(f1 INT DEFAULT NULL);
|
|
INSERT INTO t4 VALUES (2);
|
|
ANALYZE TABLE t1, t2, t4;
|
|
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.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status OK
|
|
SELECT
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
COUNT(*)
|
|
3
|
|
explain extended SELECT
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00
|
|
1 PRIMARY t2 index f2 f2 5 NULL 3 100.00 Using where; Using index; LooseScan
|
|
1 PRIMARY ta3 ref f2 f2 5 test.t2.f2 1 33.33 Using index
|
|
1 PRIMARY ta4 index PRIMARY f2 5 NULL 3 100.00 Using index; Using join buffer (flat, BNL join)
|
|
1 PRIMARY t4 hash_ALL NULL #hash#$hj 5 test.ta4.f1 1 100.00 Using where; FirstMatch(ta4); Using join buffer (incremental, BNLH join)
|
|
Warnings:
|
|
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` semi join (`test`.`t4`) semi join (`test`.`t2`) join `test`.`t2` `ta3` join `test`.`t2` `ta4` where `test`.`t4`.`f1` = `test`.`ta4`.`f1` and `test`.`ta3`.`f2` = `test`.`t2`.`f2`
|
|
SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
COUNT(*)
|
|
3
|
|
explain extended SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
|
|
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
|
|
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
|
|
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 index f2 f2 5 NULL 3 100.00 Using where; Using index; Start temporary
|
|
1 PRIMARY t4 ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 PRIMARY ta3 ref f2 f2 5 test.t2.f2 1 100.00 Using index
|
|
1 PRIMARY ta4 eq_ref PRIMARY PRIMARY 4 test.t4.f1 1 33.33 End temporary
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Note 1003 select /*+ JOIN_PREFIX(@`select#1` `t2`@`qb2`,`t4`@`qb1`,`ta3`,`ta4`) */ count(0) AS `COUNT(*)` from `test`.`t1` semi join (`test`.`t4`) semi join (`test`.`t2`) join `test`.`t2` `ta3` join `test`.`t2` `ta4` where `test`.`ta4`.`f1` = `test`.`t4`.`f1` and `test`.`ta3`.`f2` = `test`.`t2`.`f2`
|
|
DROP TABLE t1, t2, t4;
|
|
|
|
MDEV-36638 Some optimizer hint warnings are returned as errors
|
|
|
|
SET @save_sql_mode = @@sql_mode;
|
|
SET SQL_MODE = 'STRICT_TRANS_TABLES';
|
|
CREATE TABLE t1 (id int(11) NOT NULL auto_increment, f1 INT NOT NULL, PRIMARY KEY (id));
|
|
INSERT INTO t1 (id, f1) VALUES (1,9),(2,0), (3,7);
|
|
CREATE TABLE t2 (id int(11), f2 INT NOT NULL);
|
|
INSERT INTO t2 (id, f2) VALUES
|
|
(4,5),(3,3),(1,0),(1,3),(6,1),(2,0),(4,1),(2,7),(2,1),(1,0),(3,0),(5,8),(5,4),
|
|
(3,9),(2,0),(7,2),(2,0),(1,8),(6,5),(4,1);
|
|
CREATE TABLE tn (fn_1 INT, fn_2 INT);
|
|
ANALYZE TABLE t1, t2, tn;
|
|
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.tn analyze status Engine-independent statistics collected
|
|
test.tn analyze status OK
|
|
EXPLAIN EXTENDED
|
|
INSERT INTO tn (fn_1, fn_2)
|
|
SELECT /*+ JOIN_ORDER(t2,t1) JOIN_FIXED_ORDER() */ f1,f2
|
|
FROM t2 JOIN t1 ON t1.id=t2.id ORDER BY f1, f2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using temporary; Using filesort
|
|
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3 33.33 Using where; Using join buffer (flat, BNL join)
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_FIXED_ORDER() is ignored as conflicting/duplicated
|
|
Note 1003 insert into `test`.`tn`(fn_1,fn_2) select /*+ JOIN_ORDER(@`select#1` `t2`,`t1`) */ `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`id` = `test`.`t2`.`id` order by `test`.`t1`.`f1`,`test`.`t2`.`f2`
|
|
EXPLAIN EXTENDED
|
|
INSERT INTO tn (fn_1, fn_2)
|
|
SELECT /*+ JOIN_ORDER(t1,t2) */ f1,f2 FROM t2 LEFT JOIN t1 ON t1.id=t2.id
|
|
ORDER BY f1, f2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 20 100.00 Using temporary; Using filesort
|
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 100.00 Using where
|
|
Warnings:
|
|
Warning 4219 Hint JOIN_ORDER(`t1`,`t2`) is ignored as conflicting/duplicated
|
|
Note 1003 insert into `test`.`tn`(fn_1,fn_2) select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`id` = `test`.`t2`.`id` and `test`.`t2`.`id` is not null) where 1 order by `test`.`t1`.`f1`,`test`.`t2`.`f2`
|
|
DROP TABLE tn;
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (a int, b varchar(50), c varchar(50));
|
|
INSERT t1 (a,b) VALUES (1,'1'), (2,'2'), (3,'3');
|
|
CREATE TRIGGER tr BEFORE UPDATE ON t1 FOR EACH ROW
|
|
SET new.c = (SELECT /*+ NO_RANGE_OPTIMIZATION(t1) */ 1);
|
|
UPDATE t1 SET t1.b = 10;
|
|
DROP TABLE t1;
|
|
SET SQL_MODE = @save_sql_mode;
|