Quelle opt_hints_impl_qb_name.result
Sprache: Lisp
set optimizer_switch = 'derived_merge=on';
create table t1 (a int, b int, c char(20), key idx_a(a), key idx_ab(a, b));
insert into t1 select seq, seq, 'filler' from seq_1_to_100;
create table t2 as select * from t1;
analyze table t1,t2 persistent for all;
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
# Table-level hint
explain extended select /*+ no_bnl(t2@dt)*/ * from
(select t1.* from t1, t2 where t1.a > t2.a) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003 select /*+ NO_BNL(`t2`@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
# More than one reference to a single QB
explain extended select /*+ no_bnl(t2@dt) no_index(t1@dt)*/ * from
(select t1.* from t1, t2 where t1.a > t2.a) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 100100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003 select /*+ NO_BNL(`t2`@`dt`) NO_INDEX(`t1`@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
# QB-level hint
explain extended select /*+ no_bnl(@dt)*/ * from
(select t1.* from t1, t2 where t1.a > t2.a) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003 select /*+ NO_BNL(@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
# Index-level hints
# Without the hint 'range' index access would be chosen
explain extended select /*+ no_index(t1@`T`)*/ * from
(select * from t1 where a < 3) t;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1002.00 Using where
Warnings:
Note 1003 select /*+ NO_INDEX(`t1`@`T`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3
# Without the hint 'range' index access would be chosen
explain extended select /*+ no_range_optimization(t1@t1)*/ * from
(select * from t1 where a > 100and a < 120) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 1001.00 Using where
Warnings:
Note 1003 select /*+ NO_RANGE_OPTIMIZATION(`t1`@`t1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` > 100 and `test`.`t1`.`a` < 120
# Regular and derived tables share same name but the hint is applied correctly
explain extended select /*+ index(t1@t1 idx_ab)*/ * from
(select * from t1 where a < 3) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_ab idx_ab 5 NULL 2100.00 Using index condition
Warnings:
Note 1003 select /*+ INDEX(`t1`@`t1` `idx_ab`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3
explain extended select /*+ no_index(t1@t2 idx_a) index(t1@t1 idx_ab)*/ * from
(select * from t1 where a < 3) as t1, (select * from t1 where a < 5) as t2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_ab idx_ab 5 NULL 2100.00 Using index condition 1 SIMPLE t1 range idx_ab idx_ab 5 NULL 3100.00 Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select /*+ NO_INDEX(`t1`@`t2` `idx_a`) INDEX(`t1`@`t1` `idx_ab`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5 and `test`.`t1`.`a` < 3
explain extended select /*+ no_index(t1@t1 idx_a, idx_ab)*/ * from
(select * from t1 where a < 3) as t1, (select * from t1 where a < 5) as t2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1002.00 Using where 1 SIMPLE t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition; Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select /*+ NO_INDEX(`t1`@`t1` `idx_a`,`idx_ab`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5 and `test`.`t1`.`a` < 3
# Nested derived tables
explain extended select /*+ no_bnl(t1@dt2)*/ * from
(select count(*) from t1, (select * from t1 where a < 5) dt1) as dt2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index
Warnings:
Note 1003/* select#1 */ select /*+ NO_BNL(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`dt2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `dt2`
explain extended select /*+ no_index(t1@DT2)*/ * from
(select count(*) from t1, (select * from t1 where a < 5) dt1) as dt2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 Using join buffer (flat, BNL join)
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`DT2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`DT2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `dt2`
# Explicit QB name overrides the implicit one
explain extended select /*+ no_index(t1@dt2)*/ * from
(select count(*) from t1, (select /*+ qb_name(dt2)*/ * from t1 where a < 5) dt1) as dt2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 400100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 1004.00 Using where 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join)
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `dt2`
# Both hints are applied
explain extended select /*+ no_index(t1@dt1) no_bnl(t1@dt2)*/ * from
(select count(*) from t1, (select * from t1 where a < 5) dt1) as dt2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 400100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 1004.00 Using where 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`dt1`) NO_BNL(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`dt2`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `dt2`
# Nested derived tables with ambiguous names, hint is ignored
explain extended select /*+ no_index(t1@t1)*/* from
(select count(*) from t1, (select * from t1 where a < 5) t1) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join)
Warnings:
Warning 4259 Query block name `t1` is ambiguous for NO_INDEX hint
Note 1003/* select#1 */ select `t1`.`count(*)` AS `count(*)` from (/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `t1`
# The hint cannot be applied to a derived table with UNION
explain extended select /*+ no_index(t2@t1)*/* from
(select * from t1 where a < 3 union select * from t2 where a < 9) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 1100.00 Using index condition 3 UNION t2 ALL NULL NULL NULL NULL 1008.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Warning 4260 Implicit query block name `t1` is not supported for derived tables and views with UNION/EXCEPT/INTERSECT and is ignored for NO_INDEX hint
Note 1003/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3 union /* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`a` < 9) `t1`
explain extended select /*+ no_index(t1@t1)*/* from
(select * from t1 where a < 3 union select * from t2 where a < 9) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 1100.00 Using index condition 3 UNION t2 ALL NULL NULL NULL NULL 1008.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Warning 4260 Implicit query block name `t1` is not supported for derived tables and views with UNION/EXCEPT/INTERSECT and is ignored for NO_INDEX hint
Note 1003/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3 union /* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`a` < 9) `t1`
# Test INSERT..SELECT
explain extended insert into t2 select /*+ no_bnl(t2@dt)*/ * from
(select t1.* from t1, t2 where t1.a > t2.a) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 Using temporary 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003 insert into `test`.`t2` select /*+ NO_BNL(`t2`@`dt`) */ sql_buffer_result `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
# Test MERGE and NO_MERGE hints
explain extended select /*+ merge(@dt)*/ * from
(select * from (select t1.* from t1, t2 where t1.a > t2.a) as dt1) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select /*+ MERGE(@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
explain extended select /*+ no_merge(@dt)*/ * from
(select * from (select t1.* from t1, t2 where t1.a > t2.a) as dt1) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 10000100.00 3 DERIVED t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 100100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003/* select#1 */ select /*+ NO_MERGE(@`dt`) */ `dt1`.`a` AS `a`,`dt1`.`b` AS `b`,`dt1`.`c` AS `c` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt1`
# Multiple levels of nested derived tables, all hints are applied
explain extended
select /*+ no_merge(dv) no_bnl(t2@dt) */ * from (
select /*+ no_merge(du) */ * from (
select /*+ no_merge(dt) */ * from (
select t1.* from t1, t2 where t1.a > t2.a
) dt
) du
) dv;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000100.00 2 DERIVED <derived3> ALL NULL NULL NULL NULL 10000100.00 3 DERIVED <derived4> ALL NULL NULL NULL NULL 10000100.00 4 DERIVED t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003/* select#1 */ select /*+ NO_MERGE(`dt`@`select#3`) NO_MERGE(`du`@`select#2`) NO_MERGE(`dv`@`select#1`) NO_BNL(`t2`@`dt`) */ `dv`.`a` AS `a`,`dv`.`b` AS `b`,`dv`.`c` AS `c` from (/* select#2 */ select `du`.`a` AS `a`,`du`.`b` AS `b`,`du`.`c` AS `c` from (/* select#3 */ select `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#4 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`) `du`) `dv`
# ======================================
# Test CTEs
# By default BNL and index access to t1 are used.
explain extended
with cte as (select count(*) from t1, (select * from t1 where a < 5) dt1)
select * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join)
Warnings:
Note 1003 with cte as (/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5)/* select#1 */ select `cte`.`count(*)` AS `count(*)` from `cte`
explain extended
with cte as (select count(*) from t1, (select * from t1 where a < 5) dt1)
select /*+ no_bnl(t1@cte)*/ * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index
java.lang.StringIndexOutOfBoundsException: Index 9 out of bounds for length 9
as(* select#2 */ select /*+ QB_NAME(`cte`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5)/* select#1 */ select /*+ NO_BNL(`t1`@`cte`) */ `cte`.`count(*)` AS `count(*)` from `cte`analyzestatus Table is alreadyup to date
explain
cteas select count(* , (elect*fromt1where <5 dt1
select/
id select_type table 1 SIMPLE t1 ALL idx_a,idx_ab NULLNULL . 1PRIMARYderived2 ALL NULL NULL NULL 200100.0 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index
Warnings: 1003with as (*select/ / explainextended withcteas(selectcount(*)fromt1,(select*fromt1wherea<5)dt1)
select /*+ no_index(t1@cte)*/ *fromcte
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <erived2>ALLNULL NULL NULL 200100.00java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56
idx_a,idx_ab idx_a 5NULL.00 Using where; Using index 1SIMPLE idx_a,dx_ab NULL 10010000
arningsjava.lang.StringIndexOutOfBoundsException: Index 9 out of bounds for length 9
Note1003select /*+ NO_BNL(@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`
explain extended
# Index hints
select /*+ no_index(t1@cte) no_index(t1@dt1)*/ * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <id select_type table key rows
t1 NULL . 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 Using join buffer (flat, BNL join)
Warnings
ote1003 with cte (/* select#2 */ select /*+ QB_NAME(`cte`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5)/* select#1 */ select /*+ NO_INDEX(`t1`@`cte`) NO_INDEX(`t1`@`dt1`) */ `cte`.`count(*)` AS `count(*)` from `cte`
explain extended
with cte as (select count(*) from t1, (select * # Regular and derived tables share same name but the
selects * t1 a 3;
typepossible_keys key_len rows filteredExtra
<>ALL NULL NULL 30010000java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56 2 DERIVED t1 range idx_ab idx_ab 5 NULL 3100.00 Using where; Using index 2 DERIVED indexNULL 5 NULL 100100.00Usingindex
Warnings:
Note 1003 with cte as select_typetable possible_keys key key_len rows filtered Extra
#Ambiguity:multiple occurencies of `cte`, the hint is ignored
explain extended
with cte as (select count(*) as cnt from t1, (select * from t1 where a < 5) dt1)
select /*+ no_bnl(@cte)*/ * from cte where cnt > 10
union
select * from ctewhere cnt <100;
idselect_type type keykey_len ref rows filtered Extra 1 PRIMARY <derived2>ALL NULLNULL 200100. Using where 2 DERIVED t1 range idx_a,select from t1 wherea<3) as,( *from t1 <5)as ;
DERIVEDt1 NULL 5 NULL10010000 Using ; join buffer (flat, BNL join) 4 UNION <derived5> ALL NULL NULL NULL NULL 200100.00 Using where 5 DERIVED t1range idx_a,idx_ab 5 NULL 10000 Using where; Using index 5 DERIVED1SIMPLEt1range , 5 NULL 100. index ; Using where; Using join buffer (flat, BNL join)
NULL UNIONNote1003select/
Warnings:
Warning 4259 Query block name `cte` is ambiguous for hint
Note 1003 with cte as (/* select#2 */ select count(0) AS `cnt` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5 having `cnt` > 10)/* select#1 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` > 10 union /* select#4 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` < 100
#, ifCTE occurencies have different aliases, the hint can be applied
extended
( cnt * <))
select /*+ no_index(t1@cte1)*/ * from cte as cte1 where cnt > 10
union
select * from cte where cnt < 100;
id tabletype key refrows filtered Extra 1 DERIVEDNULL idx_a 5 NULL 10010000 index 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 Using join buffer (flat, BNL join)
Using where 5 DERIVED(elect count()from t1,( *from where a < 5)dt1 dt2; 5 DERIVED t1 id select_type tablejava.lang.StringIndexOutOfBoundsException: Range [15, 14) out of bounds for length 75
NULL UNION RESULT <union1,4 t1range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index
Warnings:
Note 1003 with cte as (/* select#2 */ select /*+ QB_NAME(`cte1`) */ count(0) AS `cnt` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5 having `cnt` > 10)/* select#1 */ select /*+ NO_INDEX(`t1`@`cte1`) */ `cte1`.`cnt` AS `cnt` from `cte` `cte1` where `cte1`.`cnt` > 10 union /* select#4 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` < 100
# ======================================
# Test views
create view v1 as select * from t1 where a < 100;
# Default execution plan
explain extended select *
from v1, v1 as v2 where v1.a = v2.a and v1.a < 3;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_a,idx_ab idx_a 5 NULL 1100.00 Using index condition 1 SIMPLE t1 ref idx_a,idx_ab idx_a 5 test.t1.a 1100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3and `test`.`t1`.`a` < 100and `test`.`t1`.`a` < 100
explain extended
select /*+ index(t1@v1 idx_ab) no_index(t1@`v2`)*/ *
from v1, v1 as v2 where v1.a = v2.a and v1.a < 3;
id# Explicit QB overrides implicitone
idx_ab 5 NULL 2100..00Using index condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 10099.00 Using where; Using join buffer (flat, BNL join)
:
Note 1003 select /*+ INDEX(`t1`@`v1` `idx_ab`) NO_INDEX(`t1`@`v2`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 and `test`.`t1`.`a` < 100 and `test`.`t1`.`a` < 100 ref filtered Extra 2DERIVEDt1ALL NULL NULL 400Using where
create view *fromv1where <300;
# Warnings:
explainNote1003/* select#1 */ select /*+ NO_INDEX(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `dt2`
id select_type table type possible_keys key key_len ref rows filtered Extra 1SIMPLE idx_aidx_abNULL .
Warnings: 1003 select test``t1``a`AS``,test`.t`.`AS``,test`..cAS t`. `.t1``<300 `est`.a`<100
# Addressing an objectselect count( (elect * fromt1 where <5 ) as dt2
explain select/*+ index(t1@`v1` idx_ab)*/ * from v2;
id select_type table type 1PRIMARY <erived2 ALLNULLNULLNULLNULL 40010000java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56 1 java.lang.StringIndexOutOfBoundsException: Range [25, 24) out of bounds for length 70
Warnings: /*+ INDEX(`t1`@`v1` `idx_ab`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 300 and `test`.`t1`.`a` < 100
create view v3 as select * from t1 union select * from t2;
# Unable to apply the hint to a view with UNION
explain extended select /*+ no_index(t1@v3) */ * from v3;
id select_type table type possible_keys extended select /*+ no_index(t1@t1)*/* from 1 PRIMARY<> NULL NULL NULL 200 . 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 3 UNION t2 ALL NULL NULL NULL NULL 100100.00
NULL UNION RESULTidselect_typetable type possible_keys key key_len ref rows filtered Extra
Warnings:
Warning Implicit name ``isnotsupported forderived tables andviews UNION//INTERSECTand isignored forNO_INDEXhint 2DERIVED range ,dx_ab 5 NULL 2100. where; Using index
#Ambiguity:view`v1 appears two times - shouldwarn and ignore java.lang.StringIndexOutOfBoundsException: Index 70 out of bounds for length 70
explain extended select /*+ index(t2@v1) */ * from v1,
(elect a from v1 where b ;
id Note 1003 /* select1/ t1.count*`AS`count*` from(/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5) `t1`
t1range , java.lang.StringIndexOutOfBoundsException: Range [38, 37) out of bounds for length 78 1 SIMPLE t1 ALL idx_a,idx_ab NULL NULL NULL 10094.00 Using where; Using join buffer (flat, BNL join)
:
Warning 4259 id select_type table possible_keyskey key_lenrefrows
test.t1.a`AS``,`test.``.b AS``test````c AS``,test`.t1.a`AS`a` from`test`t1 .t`where `est````b`>5and`test`.`t1`.`a` < 100and `test`.`t1`.`a` < 100
# Implicit QB names2 DERIVEDt1 idx_a,idx_ab idx_a 5NULL 1100. index java.lang.StringIndexOutOfBoundsException: Range [75, 76) out of bounds for length 75
create view v4 as selectNULL RESULTu,3 ALLNULL NULL NULL java.lang.StringIndexOutOfBoundsException: Index 63 out of bounds for length 63
( t1* t1 t2a;
Warnings:
Warning 4242 Implicit query block names are ignored for hints specified within Note 1003 /* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from
showcreate v4;
View Create View character_set_client collation_connection
v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (select `t1`.`a` AS extendedjava.lang.StringIndexOutOfBoundsException: Range [24, 23) out of bounds for length 51
# However, a derived table inside a view can be addressed from outer query
from
t1, (select t1.* from t1, t2 where t1.a > t2.a) as dt 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 1 100.00 Usingcondition
# Addressing a single table
java.lang.StringIndexOutOfBoundsException: Range [17, 16) out of bounds for length 54
idselect_typetable possible_keyskey key_len refrows filtered
t1 java.lang.StringIndexOutOfBoundsException: Index 79 out of bounds for length 79 1 SIMPLE t1 ref idx_a,idx_ab java.lang.StringIndexOutOfBoundsException: Index 32 out of bounds for length 21 1 SIMPLE t2 ALL NULL NULL NULL NULL 100100.00 Using java.lang.StringIndexOutOfBoundsException: Index 57 out of bounds for length 50
Warnings:
Note 1003 select /*+ NO_BNL(`t2`@`dt`) */ `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` > `test`.`t2`.`a` key rows Extra
thejava.lang.StringIndexOutOfBoundsException: Range [23, 22) out of bounds for length 36
explain extended select /*+ no_bnl(@dt) */* from v5;
elect_type key ref filtered
index idx_a, idx_a 5NULL100100.00 Using where; Using index 1 SIMPLE t1 ref idx_a,idx_ab idx_a 5 test.t1.aexplain extended select /*+ merge(@dt)*/ * from
ULLNULLNULL 100100. Using where
Warnings: 1003select/*+NO_BNL(`t) * ``.`t1``a`AS `` from``.t1 `test.t1 jointest``t2` where `test`.`t1`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` > `test`.`t2`.`a`
# Derived tables inside views can be addressed by their aliases
explain extendedselect/java.lang.StringIndexOutOfBoundsException: Index 55 out of bounds for length 55
id typepossible_keys key key_len ref rows filtered Extra 1 explain select/*+ no_merge(@dt)*/ * from
SIMPLEt2 NULL NULL 100100.00 Usingwhere
Warnings:
te /*+ NO_BNL(`t2`@`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`NULLNULL .
drop view v1, v2, v3, v4, v5;
=====================
# Not supported for DML, check presence of warnings
Note 1003java.lang.StringIndexOutOfBoundsException: Index 283 out of bounds for length 283
update /*+ no_range_optimization(t1@dt)*/ t2,
(select a from t1 where a > 10) dt set b=1 where t2.a = dt.a;
select_typetabletype possible_keyskey ref filteredExtra 1 PRIMARY t2 ALLexplainextended 1 PRIMARY <derived2> ref key0 /*+ no_merge(du) */ * from 2 t1. t1,t2 where t1.a > t2.a
Warnings:
Warning 4220 Query block name `dt` dt
Note idselect_type possible_keyskey ref Extra
explain extended /*+ no_index(t1@dt)*/ from t2
where 2 derived3 ALL NULL NULL NULL NULL 10000 .
(elect from (select a from t1 where a > 10) dt where dt.a > 20);
select_type typepossible_keys key_len ref rows filtered Extra 1 PRIMARY t2 ALLNULL NULL NULL90.Using where 1 PRIMARY t1 ref idx_a,idx_ab idx_a 5 test.t2.aWarnings:
Warnings:Note /* select#1 */ select /*+ NO_MERGE(`dt`@`select#3`) NO_MERGE(`du`@`select#2`) NO_MERGE(`dv`@`select#1`) NO_BNL(`t2`@`dt`) */ `dv`.`a` AS `a`,`dv`.`b` AS `b`,`dv`.`c` AS `c` from (/* select#2 */ select `du`.`a` AS `a`,`du`.`b` AS `b`,`du`.`c` AS `c` from (/* select#3 */ select `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#4 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`) `du`) `dv` are.
Warning 4220 Query block name `dt` is not found for NO_INDEX hint; Using index 1003 delete from`test`.`t2`using (`test`.`t1`) where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` > 20and `test`.`t2`.`a` > 10
# ======================================
# Objects in triggers and stored functions must not be visible for hints
create table t3 (a int, b int);
# Trigger with derived table inside
create trigger tr1 before insert on t3
for each row
begin set new.b = (select max(a) fromid table type possible_keys key_len ref rows filtered Extra
(select a from t1 where a < new.a) dt);
java.lang.StringIndexOutOfBoundsException: Index 4 out of bounds for length 4
# Warning expected
explain java.lang.StringIndexOutOfBoundsException: Index 9 out of bounds for length 9
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULLNULL NULL NULLNULL NULL Impossible WHERE java.lang.StringIndexOutOfBoundsException: Range [74, 73) out of bounds for length 100
Warnings:
Warning 4220 Query block name `dt` is not found for NO_INDEX hint
Note 1003 select max(NULL) AS `max(a)` from `test`.`t3` where 0
derived
create get_max int return idselect_type java.lang.StringIndexOutOfBoundsException: Range [52, 51) out of bounds for length 75
(select a from t1 where a < p_a) dt);
# Warning expected
extended/*+ (@dt / a,get_max()from ;
id java.lang.StringIndexOutOfBoundsException: Index 10 out of bounds for length 9 1 SIMPLEt1 5NULL 100. index
Warnings:
Warning 4220 Query block name `dt` is not found for NO_INDEX hint
Note 1003 select `test`.`t1`.`a` AS `a`,`get_max`(`test`.`t1`.`a`) AS `get_max(a)` from `test`.`t1`
drop function with as(select ount(*from t1, select * t1where 5) dt1)
drop table t1, t2, t3;
derived_merge='
create table t1 (a int, b int, c char(20), key idx_a(a), key idx_ab(a, b));
into seq,seq filler' from seq_1_to_100;
create table t2 as select * from t1;
tablet2 all;
Table Op Msg_type Msg_text
test.Note 1003withcte (*select2 /select /*+ QB_NAME(`cte`) */ count(0) AS `count(*)` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` < 5)/* select#1 */ select /*+ NO_INDEX(`t1`@`cte`) */ `cte`.`count(*)` AS `count(*)` from `cte`
test.t1
test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
# Table- hint
explain extended select /*+ no_bnl(t2@dt)*/ * from
(elect t1..* from t1, t2 where t1.a > t2.a) as dt;
id select_type table type possible_keys key key_len ref rows filtered Extra
PRIMARY <erived2>ALL NULL NULLNULL 1000010000 2 t1ALL idx_a,idx_ab NULL NULL NULL 100100.00 2 DERIVED t2Note Warnings:
Note 1003 /* select#1 */ select /*+ NO_BNL(`t2`@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`
# with as (elect ()from (elect *from t1where a< dt1)
explain extended select /*+ no_bnl(t2@dt) no_index(t1@dt)*/ * from
(select t1.* from select /*+ no_index(t1@dt1 idx_a) no_bnl(@cte)*/ * from cte;
idselect_type table type possible_keys key key_len ref rows filtered Extra
<erived2> NULLNULL NULL NULL1000010000java.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
Note 1003/* select#1 */ select /*+ NO_BNL(`t2`@`dt`) NO_INDEX(`t1`@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`
# QB-level hint
extended /*+ no_bnl(@dt)*/ * from
(select t1wcte( java.lang.StringIndexOutOfBoundsException: Range [26, 25) out of bounds for length 80
id table possible_keys key key_len refrowsfiltered
rived2 ALL NULLNULL 1000010000
L ,dx_ab .00 2 <java.lang.StringIndexOutOfBoundsException: Range [20, 19) out of bounds for length 67
Warnings:
/java.lang.StringIndexOutOfBoundsException: Index 298 out of bounds for length 298
# Index-level hints
# Without the hint 'range' index access would be chosen
explain extended select /*+ no_index(t1@`T`)*/ * from
(select * from t1 where a < 3) t;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <2 DERIVED t1 indexNULL 5 NULL100.0 ; buffer(lat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 1002.00 Using where
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`T`) */ `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`T`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3) `t`100.00 Using where; Using index
hint' would chosen
explain extended Warnings
s *t1 java.lang.StringIndexOutOfBoundsException: Index 51 out of bounds for length 51
idselect_typetype key_len rows Extra
< ALLNULLNULLNULL 210000java.lang.StringIndexOutOfBoundsException: Index 54 out of bounds for length 54
with cte (selectcount() cnt from t1,(select *fromt1 a <5))
Warnings:
Note 1003/* select#1 */ select /*+ NO_RANGE_OPTIMIZATION(`t1`@`t1`) */ `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`t1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` > 100 and `test`.`t1`.`a` < 120) `t1`Extra
# 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using where; Using index
explainextended select /*+ index(t1@t1 idx_ab)*/ * from
(select * from t1 where a < 3) as t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
<erived2 NULL 210000 5 indexNULL 5NULL . index; Using join buffer (flat, BNL join)
java.lang.StringIndexOutOfBoundsException: Index 9 out of bounds for length 9
Note 1003/* select#1 */ select /*+ INDEX(`t1`@`t1` `idx_ab`) */ `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`t1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3) `t1`
explain extended select /*+ no_index(t1@t2 idx_a) index(t1@t1 idx_ab)*/ * from
(select * from t1 where a < 3) as t1, (select * from t1 where a < 5) as t2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <# Test views 1 PRIMARY derived3 NULLNULL NULL 3 . Using joinbuffer flat,BNLjoinjava.lang.StringIndexOutOfBoundsException: Index 88 out of bounds for length 88 3extended java.lang.StringIndexOutOfBoundsException: Index 25 out of bounds for length 25 2 DERIVED t1 range idx_ab idx_ab 5 NULL 2100.00 Using index condition
Warnings SIMPLE rangeidx_ab 5NULL 10000 index
Note1003/
explain extended select /*+ no_index(t1@t1 idx_a, idx_ab)*/ * from
(select * from t1 where a < 3) as t1, (select * from t1 where a < 5) as t2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2100.00 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2100.00 Using join buffer (flat, BNL join) 3 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition 2 DERIVED t1 ALL NULL NULL NULL NULL 1002.00 Using where
Warnings: 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`t1` `idx_a`,`idx_ab`) */ `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`t2`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`t1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3) `t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `t2`select_type typekeykey_lenrefrowsfilteredExtra
# Nested derived tables
explain extended select /*+ no_bnl(t1@dt2)*/ * from
(select count(*) from t1, (select * from t1 where a < 5) dt1) as dt2;
idselect_type tabletype possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2100.00 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index 3 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition
Warnings:
Note1003/* select#1 */ select /*+ NO_BNL(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`dt2`) */ count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`) `dt2`
extended /*+ no_index(t1@DT2)*/ * from
(selectWarnings:
id select_type table typet1`.a`AS``test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.# Addressing an object inside a nested
< ALL NULL NULL20000java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56 2DERIVED <> ALL NULLNULL NULL 2100. 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.: 3 DERIVED rangeidx_a idx_a 5NULL2100. condition
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`DT2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`DT2`) */ count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`) `dt2`create view v3 as select * from t1 union select * from t2;
des theimplicit
explain 1 PRIMARY <derived2PRIMARY <erived2> ALLNULL NULL NULL NULL 200100.00
(select count(*) from 2t1ALL NULL10010000
id select_type table type possible_keys key key_len ref rows filtered Extra
PRIMARY<> NULL NULL 400100. 2 DERIVED <derived3> ALL NULL NULL NULL NULL 4100.00
t1 index NULL idx_a5NULL 100 .00Usingindex; Using join buffer (flat, BNL join) 3 DERIVED t1 ALL NULL NULL NULL NULL 1004.00 Using where
Warnings:
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select /*+ QB_NAME(`dt2`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`) `dt2`
# Both hints are applied
explainextended /*+ no_index(t1@dt1) no_bnl(t1@dt2)*/ * from
lect*fromt1 < 5) dt1 asdt2;
id select_type idselect_type tabletype possible_keyskeykey_lenrefrows filtered 1 <>ALLNULLNULL NULL 40010000 2 DERIVED <derived3> ALL NULL NULL NULL NULL 4100.00 2 DERIVEDt1 indexNULL 5 NULL 100100.00Using java.lang.StringIndexOutOfBoundsException: Index 59 out of bounds for length 59
DERIVED t1 ALL NULLNULL 100 . Using where
Warnings: 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`dt1`) NO_BNL(`t1`@`dt2`) */ `dt2`.`count(*)` AS `count(*)` from (/* select#2 */ select /*+ QB_NAME(`dt2`) */ count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select /*+ QB_NAME(`dt1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`) `dt2`
# Nested derived tables with ambiguous names,Warnings:
java.lang.StringIndexOutOfBoundsException: Range [23, 7) out of bounds for length 51
(select count(*) View Create View character_set_client collation_connection
idselect_typetabletype possible_keys ref rows filtered Extra 1PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2100.00
t1index idx_a 5 NULL . Usingindex Using buffer (lat BNLjoin) 3 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition
Warnings:
name `` ambiguous forNO_INDEX hint 1003java.lang.StringIndexOutOfBoundsException: Index 281 out of bounds for length 281
# The hint cannot be applied aderived table with UNION
explain extended select /*+ no_index(t2@t1)*/* from
(select * from t1 where a < 3 union select * from t2 where a < 9) as t1;
id select_type table1SIMPLE ref idx_a,idx_abidx_a5t1. 1. 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9100.00 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 1100.00 Using index condition 3 UNION t2 ALL NULL NULL NULL NULL 1008.00 Usingwhere
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Warning Implicit query block name `t1` is not supported for derived tables and views with UNION/EXCEPT/INTERSECT and is ignored for NO_INDEX hint
Note 1003/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3 union /* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`a` < 9) `t1`
explain extended select /*+ no_index(t1@t1)*/* from
(select * from t1 where a < 3 union select * from t2 where a < 9) as t1;
id select_type table type possible_keys key Note 1003 select /*+ NO_BNL(@`dt`) */ `test`.`t1.`a` AS `` fromfrom`test`.``join test``t1`join `test.``where `test`.```a` `est.`t1.a andt``1`.a >`est.`2.ajava.lang.StringIndexOutOfBoundsException: Index 189 out of bounds for length 189 1 id selecttabletype possible_keys key key_len ref rows filtered Extra 1SIMPLE t1 ALLidx_a,dx_ab NULL NULL NULL 100100.00
NULL NULL NULLNULL 1008.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Warning 4260 Implicit query block name `` notsupported tablesand withUNION/EXCEPT/ and ignored NO_INDEXhint
Note 1003/* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3 union /* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where `test`.`t2`.`a` < 9) `t1`,;
# Test INSERT.(electa fromt1 a >10)dt
explain extended insert into t2 select /*+ no_bnl(t2@dt)*/ * from
(elect *from t1,t2 where t1a >t2.a)as dt;
id select_type table type 1 PRIMARY ALL NULL NULL NULL 100100.00 Using where 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000100.00 2 t1 ALL ,idx_ab NULL NULL 100.00 2 DERIVED t2Warnings:
Warnings:
Note insert into `test`.`t2` /* select#1 */ select /*+ NO_BNL(`t2`@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`1003/* select#1 */ update `test`.`t2` join (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 10) `dt` set `test`.`t2`.`b` = 1 where `dt`.`a` = `test`.`t2`.`a`
# Test MERGE and NO_MERGE
explainextended /*+ merge(@dt)*/ * from
:
possible_keyskeykey_len rowsfiltered
Note1003 delete `est.t2 using(`est.`1) where `est```.a`= `est````andtest``2`.a >20and ``.`2.`a`>10 2 DERIVED t1 ALL idx_a,idx_ab # Objects in triggers and stored f notbe visible for hints 2 DERIVED t2 ALLcreate table t3 (a int, b int);
Warnings:
Note1003/* select#1 */ select /*+ MERGE(@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`
explain extended select /*+ no_merge(@dt)*/ * from
t1.* from ,t2where t1.a t2.a as dt1) java.lang.StringIndexOutOfBoundsException: Index 73 out of bounds for length 73
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000100.00 2 DERIVED <derived3> ALL NULL NULL NULL explain extendedselect/ 3 DERIVED t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 3 buffer (flat, BNL join)
Warnings: 1003/* select#1 */ select /*+ NO_MERGE(@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `dt1`.`a` AS `a`,`dt1`.`b` AS `b`,`dt1`.`c` AS `c` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt1`) `dt`
#Note select (NULL AS `maxa)` from `test`.`t3` where 0
explain extended
(t2@)/*from java.lang.StringIndexOutOfBoundsException: Index 49 out of bounds for length 49
select/*+ no_merge(du) */ * from (
select(elect t1 a< dt;
select t1.* from t1, t2where t1.a t2.a
)xplain extended select /*+ no_index(t1@dt) */ a, get_max(a) from t1;
) du
)dv
select_type tabletype possible_keys key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000100.Warnings:
DERIVED<derived3> ALL NULL NULL NULL 10000100.00java.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 3 DERIVED <erived4 ALL NULLNULL NULL NULL 10000100.00java.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 4 DERIVED t1 ALL idx_a,idx_ab NULL NULL NULL 100100drop t1, t2 t3; 4 DERIVED set optimizer_switch ='erived_mergeoff';
Warnings:
ote1003/java.lang.StringIndexOutOfBoundsException: Index 544 out of bounds for length 544
# ======================================
# Test CTEs
# By default BNL and index access to t1 are used.
extended
with cte as (select count(*) from t1, (select * from t1 where a < 5) dt1)
select * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2100.00 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join) 3 DERIVEDtable as * from java.lang.StringIndexOutOfBoundsException: Range [36, 37) out of bounds for length 36
:
Note 1003 with cte as test analyze status Engineindependentstatistics collected
explain extended
with cteas ( count* t1 ( * fromt1 a<5)dt1)
selecttest.analyzestatus
id select_type table type possible_keys key java.lang.StringIndexOutOfBoundsException: Index 50 out of bounds for length 50 1 PRIMARY<derived2> ALL NULL NULL NULL NULL 200100.00 2 DERIVED <derived3> ALLidselect_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED t1 NULL idx_a 5 NULL 100.0 Using index 3 DERIVED t1 range idx_a,idx_ab2DERIVED t1 ALL,dx_ab NULL 10010000
Warnings:
NoteNote1003 explainextended withcteas(selectcount(*)fromt1,(select*fromt1wherea<5)dt1)
select /*+ no_bnl(@cte)*/ * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra
derived2> NULL NULL 20010000 2 DERIVED <java.lang.StringIndexOutOfBoundsException: Index 18 out of bounds for length 15
index idx_a5100100.Using 3 DERIVED t1 range idx_a,idselect_type keykey_len rows
Warnings:
Note 2 DERIVED ALL, NULLNULL 10010000java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56
explain extended
with cte as (select count( /* select#1 */ select /*+ NO_BNL(@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt`
select /*+ no_index(t1@cte)*/ * from cte;
idjava.lang.StringIndexOutOfBoundsException: Range [15, 14) out of bounds for length 75 1 select* 3 tjava.lang.StringIndexOutOfBoundsException: Index 33 out of bounds for length 33 2 DERIVED <derived3> ALL 1PRIMARY <>ALL NULLNULLNULL 2100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 100100.00 Using join buffer (flat, BNL join) 3 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition
Note 1003/* select#1 */ select /*+ NO_INDEX(`t1`@`T`) */ `t`.`a` AS `a`,`t`.`b` AS `b`,`t`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`T`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 3) `t`
Notejava.lang.StringIndexOutOfBoundsException: Range [8, 7) out of bounds for length 51
extended
as( count* ,(elect*fromt1where a )dt1)
select /*+ no_index(t1@cte) no_index(t1@dt1)*/ * from cte;
id select_type table type possible_keys key key_len ref rows filtered Extra
<erived2>ALL NULL NULL NULL 400100. 2 DERIVED <derived3> ALL NULL NULL NULL NULL 4100.00 2 DERIVED t1 Note/* select# * select/java.lang.StringIndexOutOfBoundsException: Index 314 out of bounds for length 314 3 DERIVED t1 ALL NULL NULL NULL NULL 1004.00 Using where
Warnings:
Note 1003 with cte as (/* select#2 */ select /*+ QB_NAME(`cte`) */ count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select /*+ QB_NAME(`dt1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`)/* select#1 */ select /*+ NO_INDEX(`t1`@`cte`) NO_INDEX(`t1`@`dt1`) */ `cte`.`count(*)` AS `count(*)` from `cte` name hint
explain extended
with cte as (select count(*) from t1, (select * from t1 where a < 5) dt1)
java.lang.StringIndexOutOfBoundsException: Range [7, 6) out of bounds for length 60
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 30010000java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56 2 DERIVED <derived3> ALL NULL NULL NULL NULL 3 extended/* t@ )t1@1)/ 2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index
DERIVED t1 rangeidx_ab 5NULL 100.0 Using condition
Warnings:
Note 1003 with cte as (/* select#2 */ select /*+ QB_NAME(`cte`) */ count(0) AS `count(*)` from `test`.`t1` join (/* select#3 */ select /*+ QB_NAME(`dt1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1`)/* select#1 */ select /*+ NO_INDEX(`t1`@`dt1` `idx_a`) NO_BNL(@`cte`) */ `cte`.`count(*)` AS `count(*)` from `cte`join(flat, )
# Ambiguity: multiple occurencies of `cte`, the hint is ignored
explain extended
with cte as(elect count* as cntfromt1 (elect *from t1 a<5) dt1
select /*+ no_bnl(@cte)*/ * from cte where cnt > 10
union
select * from cte where cnt <explain extended select /*+ no_index(t1@t1 idx_a, idx_ab)*/ * from
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY<>ALL NULL NULL 200100. where
<java.lang.StringIndexOutOfBoundsException: Range [20, 19) out of bounds for length 54
indexNULL 5NULL10010000Using ; Using join buffer (flat, BNL join) 3 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition 4 UNION <derived5> ALL NULL NULL NULL NULL 200100.00 Using where 5 DERIVED <derived6> ALL NULL NULL NULL NULL 2100.00 5 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join) 6 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL
Warningsjava.lang.StringIndexOutOfBoundsException: Index 9 out of bounds for length 9
Warning 4259 Query block name `cte` is ambiguous for NO_BNL hint
Note 1003 with cte as (/* select#2 */ select count(0) AS `cnt` from `test`.`t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1` having `cnt` > 10)/* select#1 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` > 10 union /* select#4 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` < 100 ifoccurencieshavedifferentaliases hint can java.lang.StringIndexOutOfBoundsException: Index 77 out of bounds for length 77
with cte as (select count(*) as cnt from t1, (select * from2DERIVEDt1 NULL 5NULL10010000Usingindex
select /*+ no_index(t1@cte1)*/ * from cte as cte1 where cnt > 10
union
select * from cte where cnt < 100;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 200100.00 Using where 2 DERIVED <derived3> ALL NULL NULL NULL NULL 2100.00 2 DERIVED(* t1,(java.lang.StringIndexOutOfBoundsException: Range [36, 33) out of bounds for length 69 5 NULL 2100..00 condition
UNION<erived5> NULLNULL NULL 200100.00 Using where 5 DERIVED <derived6> ALL NULL NULL NULL NULL 2100.00 5 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join) 6 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 2100.00 Using index condition
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 with cte as (/* select#2 */ select /*+ QB_NAME(`cte1`) */ count(0) AS `cnt` from `test`.`t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 5) `dt1` having `cnt` > 10)/* select#1 */ select /*+ NO_INDEX(`t1`@`cte1`) */ `cte1`.`cnt` AS `cnt` from `cte` `cte1` where `cte1`.`cnt` > 10 union /* select#4 */ select `cte`.`cnt` AS `cnt` from `cte` where `cte`.`cnt` < 100
# ==(select count() from, (elect /*+ qb_name(dt2)*/ * from t1 where a < 5) dt1) as dt2;
#Testviews
elect*from a<;
# Default execution plan
explain extended select *
from v1, v1 as v2 where v1.a = v2.a2DERIVED t1 index idx_a 10010000 Using index joinbufferflat BNL )
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range 1 SIMPLE t1 range idx_aNULL NULL 1004.0 Using where
t1 idx_ata110000
Warnings:
Note 1003 select `test extended /*+ no_index(t1@dt1) no_bnl(t1@dt2)*/ * from
explain java.lang.StringIndexOutOfBoundsException: Index 16 out of bounds for length 16
/
from v1, v1 as v2 where v1.a = v2.a and v1.a < 3;
id select_type >NULL NULL100.java.lang.StringIndexOutOfBoundsException: Index 54 out of bounds for length 54 1SIMPLEt1 range idx_ab idx_ab 5 NULL 2100.00 Using index condition
NULL NULL.0 Using java.lang.StringIndexOutOfBoundsException: Index 93 out of bounds for length 93
Warnings:
select/*+ INDEX(`t1`@`v1` `idx_ab`) NO_INDEX(`t1`@`v2`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 and `test`.`t1`.`a` < 100 and `test`.`t1`.`a` < 100 java.lang.StringIndexOutOfBoundsException: Range [61, 62) out of bounds for length 61
#Nested
create view v2 select* from v1 where a<300
# Default execution plan
explain extended select * from v2;
id select_type2 DERIVED index NULL idx_a 510010000Usingindex; Using join buffer (flat, BNL join) 10094.00 Using java.lang.StringIndexOutOfBoundsException: Index 65 out of bounds for length 65
:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS`c` from ``.t1` `test```.`` < 300 nd`testt1`.`a` < 100
# Addressing an object inside a nested view
explain extended select /*+ index(t1@`v1` idx_ab)*/ * from v2;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range/*+ no_index(t2@t1)*/* from
Warnings:
Note 1003 select /*+ INDEX(`t1`@`v1` `idx_ab`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`a` < 300 and `test`.`t1`.`a` < 100 type possible_keys key key_len ref filtered
create view v3 as select * from 2 DERIVED t1 range idx_a,idx_ab idx_a t1 range ,dx_abidx_a5110000 Using condition
# Unable to apply the hint to a view with UNION
explain extended select /*+ no_index(t1@v3) */ * from v3;
id select_type table type possible_keys key key_len ref rows filtered Extra
PRIMARY <>ALL NULL NULL 200100.00 2DERIVEDt1ALL NULL NULL NULL 100100.00 3 UNION t2 ALL NULL NULL NULL NULL 100100.00
NULL UNION RESULT <union2,3> ALL NULL NULL NULL( *from t1 where a 3union select *fromt2 where a<9 t1
Warnings:
Warning 4260 Implicit query block name `v3` is not supported 2 DERIVED t1 range idx_a,idx_ab idx_a 5 NULL 1 100.00 Using index 1003java.lang.StringIndexOutOfBoundsException: Index 96 out of bounds for length 96
# Warnings
explain select/*+ index(t2@v1) */ * from v1,
(select a from v1 where b > 5) dt;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL idx_a,idx_ab NULL NULL NULL 10094.00 Using where 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 99100.00 Using join buffer (flat, BNL join) 5 NULL 9990.20; Using index
: 4259 Queryblock name``is ambiguous for INDEX hint /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`dt`.`a` AS `a` from `test`.`t1` join (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` > 5 and `test`.`t1`.`a` < 100) `dt` where `test`.`t1`.`a` < 100idx_ab NULLNULL 100.0java.lang.StringIndexOutOfBoundsException: Index 56 out of bounds for length 56
# Implicit QB names are not supported inside views
create view v4 as select /*+ no_bnl(t2@dt)*/ * from
(select t1.* from t1, t2 where t1.a > t2.a) as dt;
Warnings:
Warning 4242 Implicit query block names are ignored for hints specified within VIEWs
show create view v4;
View Create
v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `dt`.`a#Test MERGE and NO_MERGE hints
# However, a derived table inside a view can be addressed from outer query(select* from select t1.* from t1, t2 where t1.a > t2.a) as dt1) as dt;
create view v5 as select dt.a from
t1 (select t1* from t1, t2 where .a>t2.) as dt wheret1.a=ta;
# Addressing a single table
explain extended select /*+ no_bnl(t2@dt) */* from v5;
select_type typepossible_keyskey refrows Extra 1 PRIMARY t1 index idx_a,idx_ab idx_a 5 NULL 1003/* select#1 */ select /*+ MERGE(@`dt`) */ `dt`.`a` AS `a`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from (/* select#2 */ select /*+ QB_NAME(`dt`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt` 1 PRIMARY <derived3> ref key0 key0 5 test.t1.a 100100.00
DERIVEDALL,dx_ab NULLNULL .00 3 DERIVED t2 ALL NULL NULL NULL NULL 100100.00 Using where
(elect*from select from wheret1. t2.) asdt1 dt; 1003/* select#1 */ select /*+ NO_BNL(`t2`@`dt`) */ `dt`.`a` AS `a` from `test`.`t1` join (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt` where `dt`.`a` = `test`.`t1`.`a`
# Addressing the whole derived table
explain extended select /*+ no_bnl(@dt) */* from v5;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 index idx_a,idx_ab idx_a 5 NULL 100100.00 Using where; Using index 1 PRIMARY <derived3> ref key0 key0 5 Note 1003 /* select#1 */ select /*+ NO_ME(`` /`.a ```dt.b AS b,`t``` cfrom(* select#2 */ select /*+ QB_NAME(`dt`) */ `dt1`.`a` AS `a`,`dt1`.`b` AS `b`,`dt1`.`c` AS `c` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` > `test`.`t2`.`a`) `dt1`) `dt` 3 DERIVED t1 ALL idx_a,idx_ab NULL NULL/*+ no_merge(dt) */ * from ( 3 DERIVED t2 ALL NULL NULL NULL NULL 100100.00 Using where
Warnings:
elect# /selectjava.lang.StringIndexOutOfBoundsException: Index 295 out of bounds for length 295
# Derived tables inside views can be addressed by their aliases
explain extended select /*+ no_bnl(t2@dt) */ * from v4;
id select_type table type 1PRIMARY derived2 NULL NULL 10000100.00
<derived3> NULL NULL NULL 1000010000java.lang.StringIndexOutOfBoundsException: Index 58 out of bounds for length 58 3 DERIVED t1 ALL idx_a,idx_ab NULL NULL NULL 100100.00 3 DERIVED t2 .
:
drop view v1, v2, v3, v4, v5;
# ======================================
# Not supported for ,check presence warnings
explain extended
update /*+ no_range_optimization(t1@dt)*/ t2,
(select a from t1 where a > 10) dt set b=1 where t2.a = dt.a;
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 100100.00 Using where 1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 9100.00
DERIVED t1 range idx_a, 5 NULL9210000Using ; Using index
Warnings:
Warning 4220 Query block java.lang.StringIndexOutOfBoundsException: Index 26 out of bounds for length 16
Note 1003/* select#1 */ update `test`.`t2` join (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 10) `dt` set `test`.`t2`.`b` = 1 where `dt`.`a` = `test`.`t2`.`a`
explainextended
delete /*+ no_index(t1@dt)*/ from t2
where t2.a in
java.lang.StringIndexOutOfBoundsException: Range [8, 7) out of bounds for length 67
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL2 DERIVED t1 index NULL idx_a 5 NULL 100100.00 Using index; Using join buffer (flat, BNL join) 1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t2.a 1100.00 3 t1 idx_aidx_ab idx_ab 5NULL 10000 where; Using index
Warnings:
java.lang.StringIndexOutOfBoundsException: Range [63, 7) out of bounds for length 65
Note select/
# ====idtable type possible_keys key key_len ref rows filtered Extra
rsand functions must notbevisibleforjava.lang.StringIndexOutOfBoundsException: Index 72 out of bounds for length 72
create table t3 (a int, b int);
# with derived table inside
create triggertr1 beforeinsert on
for each row
begin set new.b = (select maxexplainextended
(elect a wherenew.a) ;
end|
# Warning expected
explain extended select /*+ no_index(t1@dt) */ max(a) from t3 where a<2;
id select_type table type <> ALLNULL NULL NULL200
NULL NULL NULLNULL NULL Impossible WHERE noticed after reading const tables
:
java.lang.StringIndexOutOfBoundsException: Range [9, 8) out of bounds for length 9
Note 1003 select max(NULL) AS `max(a)` from `test`.`t3` where 0
# Stored function with derived table inside
create function get_max(p_a int) returns int return (select max(a) from
(select a from t1 where a < p_aexplainextended
#Warning expected
explain extended select
id select_type table type possible_keys key key_len ref rows filtered Extra 10000 Using index
Warnings:
Warning 4220 Query block name `dt` is not found for NO_INDEX hint
Note 1003 select `test`.`t1`.`a` AS `a`,`get_max`(`test`.`t1`.`a`) AS `2 DERIVED t1 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (flat, BNL join
drop function get_max;
drop table t1, t2, t3; set optimizer_switch = default;
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
