mirror of
https://github.com/MariaDB/server.git
synced 2025-08-08 11:31:35 +02:00
62a1f0d990
Support for optimizer hints NO_SPLIT_MATERIALIZED and SPLIT_MATERIALIZED. These hints allow fine-grained control of the "lateral derived" optimization within a query. Introduces new overload of hint_table_state function which tells both a hint's value as well as whether it is present. This is useful to disambiguate cases that the other version of hint_table_state cannot, such as when a hint is forcing a behavior in the optimizer that it would not normally do and the corresponding optimizer switch is enabled.
781 lines
29 KiB
Text
781 lines
29 KiB
Text
#
|
|
# MDEV-36092 New-style hint: [NO_]SPLIT_MATERIALIZED
|
|
#
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
#
|
|
# case 1
|
|
#
|
|
create table t1 (
|
|
n1 int(10) not null,
|
|
n2 int(10) not null,
|
|
c1 char(1) not null,
|
|
key c1 (c1),
|
|
key n1_c1_n2 (n1,c1,n2)
|
|
) engine=innodb charset=latin1;
|
|
insert into t1 values (0, 2, 'a'), (1, 3, 'a');
|
|
insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# default showing lateral derived optimization in use
|
|
explain
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
n1
|
|
0
|
|
1
|
|
# cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
2 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
select /*+ no_split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
n1
|
|
0
|
|
1
|
|
# query having two subqueries, both of which are subject to LATERAL DERIVED optimization
|
|
# explain and result without hints
|
|
explain
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with first half of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with second half of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with both halves of query disabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ NO_SPLIT_MATERIALIZED(t@qb1) NO_SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
# default showing lateral derived optimization not used
|
|
explain
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
2 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
n1
|
|
0
|
|
1
|
|
# cases where hint enables lateral derived optimization
|
|
explain
|
|
select /*+ split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
select /*+ split_materialized(t) */ t1.n1 from t1,
|
|
(select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1;
|
|
n1
|
|
0
|
|
1
|
|
# query having two subqueries, both of which are subject to LATERAL DERIVED optimization
|
|
# explain and result without hints
|
|
explain
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with first half of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with second half of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 DERIVED t1 ref c1 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
# explain and result with both halves of query enabling the optimization
|
|
explain
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
|
|
2 DERIVED t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
2 DERIVED <derived3> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
3 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
4 UNION t1 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort
|
|
4 UNION <derived5> ref key0 key0 8 test.t1.n1,test.t1.n2 1
|
|
5 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index
|
|
NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL
|
|
with cte as (
|
|
select /*+ QB_NAME(qb1) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1
|
|
union
|
|
select /*+ QB_NAME(qb2) */ t1.n1 from t1, (select n1, n2 from t1 where c1 = 'a' group by n1) as t
|
|
where t.n1 = t1.n1 and t.n2 = t1.n2 and c1 = 'a' group by n1)
|
|
select /*+ SPLIT_MATERIALIZED(t@qb1) SPLIT_MATERIALIZED(t@qb2) */ * from cte;
|
|
n1
|
|
0
|
|
1
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1;
|
|
#
|
|
# end case 1
|
|
#
|
|
#
|
|
# case 2
|
|
#
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
create table t1(a int, b int);
|
|
insert into t1 select seq,seq from seq_1_to_5;
|
|
create table t2(a int, b int, key(a));
|
|
insert into t2
|
|
select a.seq,b.seq from seq_1_to_25 a, seq_1_to_2 b;
|
|
create table t3(a int, b int, key(a));
|
|
insert into t3
|
|
select a.seq,b.seq from seq_1_to_5 a, seq_1_to_3 b;
|
|
analyze table t1,t2,t3 persistent for all;
|
|
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
|
|
create table t10 (
|
|
grp_id int,
|
|
col1 int,
|
|
key(grp_id)
|
|
);
|
|
insert into t10
|
|
select
|
|
a.seq,
|
|
b.seq
|
|
from
|
|
seq_1_to_100 a,
|
|
seq_1_to_100 b;
|
|
create table t11 (
|
|
col1 int,
|
|
col2 int
|
|
);
|
|
insert into t11
|
|
select a.seq, a.seq from seq_1_to_10 a;
|
|
analyze table t10,t11 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
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
|
|
create table t21 (pk int primary key);
|
|
insert into t21 values (1),(2),(3);
|
|
create table t22 (pk int primary key);
|
|
insert into t22 values (1),(2),(3);
|
|
# default showing lateral derived optimization in use
|
|
explain
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
|
|
1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
|
|
1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
|
|
2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index
|
|
2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
|
|
2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
pk a b a b a b grp_id count(*)
|
|
1 1 1 1 1 1 1 1 100
|
|
1 1 1 1 1 1 2 1 100
|
|
1 1 1 1 1 1 3 1 100
|
|
1 1 1 1 2 1 1 1 100
|
|
1 1 1 1 2 1 2 1 100
|
|
1 1 1 1 2 1 3 1 100
|
|
1 2 2 2 1 2 1 2 100
|
|
1 2 2 2 1 2 2 2 100
|
|
1 2 2 2 1 2 3 2 100
|
|
1 2 2 2 2 2 1 2 100
|
|
1 2 2 2 2 2 2 2 100
|
|
1 2 2 2 2 2 3 2 100
|
|
1 3 3 3 1 3 1 3 100
|
|
1 3 3 3 1 3 2 3 100
|
|
1 3 3 3 1 3 3 3 100
|
|
1 3 3 3 2 3 1 3 100
|
|
1 3 3 3 2 3 2 3 100
|
|
1 3 3 3 2 3 3 3 100
|
|
1 4 4 4 1 4 1 4 100
|
|
1 4 4 4 1 4 2 4 100
|
|
1 4 4 4 1 4 3 4 100
|
|
1 4 4 4 2 4 1 4 100
|
|
1 4 4 4 2 4 2 4 100
|
|
1 4 4 4 2 4 3 4 100
|
|
1 5 5 5 1 5 1 5 100
|
|
1 5 5 5 1 5 2 5 100
|
|
1 5 5 5 1 5 3 5 100
|
|
1 5 5 5 2 5 1 5 100
|
|
1 5 5 5 2 5 2 5 100
|
|
1 5 5 5 2 5 3 5 100
|
|
# cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
|
|
1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
|
|
1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
|
|
2 DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary; Using filesort
|
|
2 DERIVED t10 ALL NULL NULL NULL NULL 10000
|
|
2 DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
|
|
select /*+ no_split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
pk a b a b a b grp_id count(*)
|
|
1 1 1 1 1 1 1 1 100
|
|
1 1 1 1 1 1 2 1 100
|
|
1 1 1 1 1 1 3 1 100
|
|
1 1 1 1 2 1 1 1 100
|
|
1 1 1 1 2 1 2 1 100
|
|
1 1 1 1 2 1 3 1 100
|
|
1 2 2 2 1 2 1 2 100
|
|
1 2 2 2 1 2 2 2 100
|
|
1 2 2 2 1 2 3 2 100
|
|
1 2 2 2 2 2 1 2 100
|
|
1 2 2 2 2 2 2 2 100
|
|
1 2 2 2 2 2 3 2 100
|
|
1 3 3 3 1 3 1 3 100
|
|
1 3 3 3 1 3 2 3 100
|
|
1 3 3 3 1 3 3 3 100
|
|
1 3 3 3 2 3 1 3 100
|
|
1 3 3 3 2 3 2 3 100
|
|
1 3 3 3 2 3 3 3 100
|
|
1 4 4 4 1 4 1 4 100
|
|
1 4 4 4 1 4 2 4 100
|
|
1 4 4 4 1 4 3 4 100
|
|
1 4 4 4 2 4 1 4 100
|
|
1 4 4 4 2 4 2 4 100
|
|
1 4 4 4 2 4 3 4 100
|
|
1 5 5 5 1 5 1 5 100
|
|
1 5 5 5 1 5 2 5 100
|
|
1 5 5 5 1 5 3 5 100
|
|
1 5 5 5 2 5 1 5 100
|
|
1 5 5 5 2 5 2 5 100
|
|
1 5 5 5 2 5 3 5 100
|
|
# test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
# default showing lateral derived optimization not used
|
|
explain
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
|
|
1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
|
|
1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
|
|
2 DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary; Using filesort
|
|
2 DERIVED t10 ALL NULL NULL NULL NULL 10000
|
|
2 DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
|
|
select * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
pk a b a b a b grp_id count(*)
|
|
1 1 1 1 1 1 1 1 100
|
|
1 1 1 1 1 1 2 1 100
|
|
1 1 1 1 1 1 3 1 100
|
|
1 1 1 1 2 1 1 1 100
|
|
1 1 1 1 2 1 2 1 100
|
|
1 1 1 1 2 1 3 1 100
|
|
1 2 2 2 1 2 1 2 100
|
|
1 2 2 2 1 2 2 2 100
|
|
1 2 2 2 1 2 3 2 100
|
|
1 2 2 2 2 2 1 2 100
|
|
1 2 2 2 2 2 2 2 100
|
|
1 2 2 2 2 2 3 2 100
|
|
1 3 3 3 1 3 1 3 100
|
|
1 3 3 3 1 3 2 3 100
|
|
1 3 3 3 1 3 3 3 100
|
|
1 3 3 3 2 3 1 3 100
|
|
1 3 3 3 2 3 2 3 100
|
|
1 3 3 3 2 3 3 3 100
|
|
1 4 4 4 1 4 1 4 100
|
|
1 4 4 4 1 4 2 4 100
|
|
1 4 4 4 1 4 3 4 100
|
|
1 4 4 4 2 4 1 4 100
|
|
1 4 4 4 2 4 2 4 100
|
|
1 4 4 4 2 4 3 4 100
|
|
1 5 5 5 1 5 1 5 100
|
|
1 5 5 5 1 5 2 5 100
|
|
1 5 5 5 1 5 3 5 100
|
|
1 5 5 5 2 5 1 5 100
|
|
1 5 5 5 2 5 2 5 100
|
|
1 5 5 5 2 5 3 5 100
|
|
# cases where hint enables lateral derived optimization
|
|
explain
|
|
select /*+ split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t21 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 5
|
|
1 PRIMARY t2 ref a a 5 test.t1.b 2 Using where
|
|
1 PRIMARY t3 ref a a 5 test.t1.b 3 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.t1.b 10 Using where
|
|
2 LATERAL DERIVED t22 const PRIMARY PRIMARY 4 const 1 Using index
|
|
2 LATERAL DERIVED t10 ref grp_id grp_id 5 test.t1.b 100
|
|
2 LATERAL DERIVED t11 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
|
|
select /*+ split_materialized(t) */ * from
|
|
t21,
|
|
(
|
|
(t1 left join t2 on t2.a=t1.b)
|
|
left join t3 on t3.a=t1.b
|
|
) left join (select grp_id, count(*)
|
|
from
|
|
t22 join t10 left join t11 on t11.col1=t10.col1
|
|
where
|
|
t22.pk=1
|
|
group by grp_id) t on t.grp_id=t1.b
|
|
where
|
|
t21.pk=1;
|
|
pk a b a b a b grp_id count(*)
|
|
1 1 1 1 1 1 1 1 100
|
|
1 1 1 1 1 1 2 1 100
|
|
1 1 1 1 1 1 3 1 100
|
|
1 1 1 1 2 1 1 1 100
|
|
1 1 1 1 2 1 2 1 100
|
|
1 1 1 1 2 1 3 1 100
|
|
1 2 2 2 1 2 1 2 100
|
|
1 2 2 2 1 2 2 2 100
|
|
1 2 2 2 1 2 3 2 100
|
|
1 2 2 2 2 2 1 2 100
|
|
1 2 2 2 2 2 2 2 100
|
|
1 2 2 2 2 2 3 2 100
|
|
1 3 3 3 1 3 1 3 100
|
|
1 3 3 3 1 3 2 3 100
|
|
1 3 3 3 1 3 3 3 100
|
|
1 3 3 3 2 3 1 3 100
|
|
1 3 3 3 2 3 2 3 100
|
|
1 3 3 3 2 3 3 3 100
|
|
1 4 4 4 1 4 1 4 100
|
|
1 4 4 4 1 4 2 4 100
|
|
1 4 4 4 1 4 3 4 100
|
|
1 4 4 4 2 4 1 4 100
|
|
1 4 4 4 2 4 2 4 100
|
|
1 4 4 4 2 4 3 4 100
|
|
1 5 5 5 1 5 1 5 100
|
|
1 5 5 5 1 5 2 5 100
|
|
1 5 5 5 1 5 3 5 100
|
|
1 5 5 5 2 5 1 5 100
|
|
1 5 5 5 2 5 2 5 100
|
|
1 5 5 5 2 5 3 5 100
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1, t2, t3, t10, t11, t21, t22;
|
|
#
|
|
# end case 2
|
|
#
|
|
#
|
|
# case 3
|
|
#
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
create table t1 (a char(1)) engine=myisam;
|
|
insert into t1 values ('1'),('2');
|
|
create table t2 (b int, key(b)) engine=myisam;
|
|
alter table t2 disable keys;
|
|
insert into t2 values (1),(2),(3);
|
|
alter table t2 enable keys;
|
|
create table t3 (c int) engine=myisam;
|
|
insert into t3 (c) select seq from seq_1_to_101;
|
|
# default showing lateral derived optimization in use
|
|
explain
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 Using where
|
|
3 LATERAL DERIVED t2 ref b b 5 test.t1.a 1 Using where; Using index; Using temporary; Using filesort
|
|
4 SUBQUERY t3 ALL NULL NULL NULL NULL 101
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
a
|
|
# cases where hint disables lateral derived optimization
|
|
explain
|
|
select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 Using where
|
|
3 DERIVED t2 range NULL b 5 NULL 3 Using where; Using index for group-by
|
|
4 SUBQUERY t3 ALL NULL NULL NULL NULL 101
|
|
select /*+ no_split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
a
|
|
# test opposite way, where hint enables the optimization instead of disabling it
|
|
set optimizer_switch='split_materialized=off';
|
|
# default showing lateral derived optimization not used
|
|
explain
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 Using where
|
|
3 DERIVED t2 range NULL b 5 NULL 3 Using where; Using index for group-by
|
|
4 SUBQUERY t3 ALL NULL NULL NULL NULL 101
|
|
select * from t1 where t1.a in (
|
|
select b from (
|
|
select t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
a
|
|
# cases where hint enables lateral derived optimization
|
|
explain select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 5 test.t1.a 1 Using where
|
|
3 LATERAL DERIVED t2 ref b b 5 test.t1.a 1 Using where; Using index; Using temporary; Using filesort
|
|
4 SUBQUERY t3 ALL NULL NULL NULL NULL 101
|
|
select /*+ split_materialized(@qb1 sq) */ * from t1 where t1.a in (
|
|
select /*+ qb_name(qb1) */ b from (
|
|
select /*+ qb_name(qb2) */ t2.b from t2 where not exists (
|
|
select 1 from t3
|
|
) group by b
|
|
) sq
|
|
);
|
|
a
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
drop table t1, t2, t3;
|
|
#
|
|
# end case 3
|
|
#
|
|
#
|
|
# case 4
|
|
#
|
|
create table one_k(a int);
|
|
insert into one_k select seq from seq_1_to_1000;
|
|
CREATE TABLE t1000 (
|
|
grp int(11) DEFAULT NULL,
|
|
val int(11) DEFAULT NULL,
|
|
KEY grp (grp)
|
|
);
|
|
insert into t1000 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_10 B;
|
|
analyze table t1000;
|
|
Table Op Msg_type Msg_text
|
|
test.t1000 analyze status Engine-independent statistics collected
|
|
test.t1000 analyze status Table is already up to date
|
|
# force lateral derived when optimizer otherwise wouldn't use it
|
|
explain
|
|
select *
|
|
from
|
|
one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY T1 ALL NULL NULL NULL NULL 1000 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.T1.a 10
|
|
2 DERIVED t1000 index grp grp 5 NULL 1000 Using index; Using temporary; Using filesort
|
|
explain
|
|
select /*+ SPLIT_MATERIALIZED(TBL) */ *
|
|
from
|
|
one_k T1, (select grp, count(*) from t1000 group by grp) TBL where TBL.grp=T1.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY T1 ALL NULL NULL NULL NULL 1000 Using where
|
|
1 PRIMARY <derived2> ref key0 key0 5 test.T1.a 1
|
|
2 LATERAL DERIVED t1000 ref grp grp 5 test.T1.a 10 Using index
|
|
drop table one_k, t1000;
|
|
#
|
|
# end case 4
|
|
#
|
|
#
|
|
# End 12.1 tests
|
|
#
|