mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
b3c74bdc1f
MDEV-28073 Slow query performance in MariaDB when using many tables The faster we can find a good query plan, the more options we have for finding and pruning (ignoring) bad plans. This patch adds sorting of plans to best_extension_by_limited_search(). The plans, from best_access_path() are sorted according to the numbers of found rows. This allows us to faster find 'good tables' and we are thus able to eliminate 'bad plans' faster. One side effect of this patch is that if two tables have equal cost, the table that which was used earlier in the query is preferred. This allows users to improve plans by reordering eq_ref tables in the order they would like them to be uses. Result changes caused by the patch: - Traces are different as now we print the cost for using tables before we start considering them in the plan. - Table order are changed for some plans. In most cases this is because the plans are equal and tables are in this case sorted according to their usage in the original query. - A few plans was changed as the optimizer was able to find a better plan (that was pruned by the original code). Other things: - Added a new statistic variable: "optimizer_join_prefixes_check_calls", which counts number of calls to best_extension_by_limited_search(). This can be used to check the prune efficiency in greedy_search(). - Added variable "JOIN_TAB::embedded_dependent" to be able to handle XX IN (SELECT..) in the greedy_optimizer. The idea is that we should prune a table if any of the tables in embedded_dependent is not yet read. - When using many tables in a query, there will be some additional memory usage as we need to pre-allocate table of table_count*table_count*sizeof(POSITION) objects (POSITION is 312 bytes for now) to hold the pre-calculated best_access_path() information. This memory usage is offset by the expected performance improvement when using many tables in a query. - Removed the code from an earlier patch to keep the table order in join->best_ref in the original order. This is not needed anymore as we are now sorting the tables for each best_extension_by_limited_search() call.
761 lines
25 KiB
Text
761 lines
25 KiB
Text
create table t1 (a int, b int);
|
|
insert into t1
|
|
values (1,2), (4,6), (9,7),
|
|
(1,1), (2,5), (7,8);
|
|
create table t2 (a int, b int, c int);
|
|
insert into t2
|
|
values (1,2,3), (5,1,2), (4,3,7),
|
|
(8,9,0), (10,7,1), (5,5,1);
|
|
create table t3 (a int, b varchar(16), index idx(a));
|
|
insert into t3 values
|
|
(1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"),
|
|
(2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"),
|
|
(3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"),
|
|
(1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"),
|
|
(11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"),
|
|
(4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"),
|
|
(6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe");
|
|
# optimization is not used
|
|
select * from t1 where a in (1,2);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1 where a in (1,2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2)
|
|
# set minimum number of values in VALUEs list when optimization works to 2
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
# single IN-predicate in WHERE-part
|
|
select * from t1 where a in (1,2);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1 where a in (1,2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
# AND-condition with IN-predicates in WHERE-part
|
|
select * from t1
|
|
where a in (1,2) and
|
|
b in (1,5);
|
|
a b
|
|
1 1
|
|
2 5
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
)
|
|
and b in
|
|
(
|
|
select *
|
|
from (values (1),(5)) as tvc_1
|
|
);
|
|
a b
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1
|
|
where a in (1,2) and
|
|
b in (1,5);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2)) as tvc_0
|
|
)
|
|
and b in
|
|
(
|
|
select *
|
|
from (values (1),(5)) as tvc_1
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where 1
|
|
# subquery with IN-predicate
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select a
|
|
from t2 where b in (3,4)
|
|
);
|
|
a b
|
|
4 6
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select a from t2
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(4)) as tvc_0
|
|
)
|
|
);
|
|
a b
|
|
4 6
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select a
|
|
from t2 where b in (3,4)
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`_col_1`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select a from t2
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(4)) as tvc_0
|
|
)
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
|
|
# derived table with IN-predicate
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
# non-recursive CTE with IN-predicate
|
|
with tvc_0 as
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
select * from tvc_0;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended with tvc_0 as
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
select * from tvc_0;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
explain extended select * from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
# VIEW with IN-predicate
|
|
create view v1 as
|
|
select *
|
|
from t1
|
|
where a in (1,2);
|
|
create view v2 as
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
;
|
|
select * from v1;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
select * from v2;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
explain extended select * from v1;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
explain extended select * from v2;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
|
|
drop view v1,v2;
|
|
# subselect defined by derived table with IN-predicate
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
as dr_table
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
)
|
|
as dr_table
|
|
);
|
|
a b
|
|
1 2
|
|
1 1
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in (1,2)
|
|
)
|
|
as dr_table
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00
|
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`_col_1`
|
|
explain extended select * from t1
|
|
where a in
|
|
(
|
|
select 1
|
|
from
|
|
(
|
|
select *
|
|
from t1
|
|
where a in
|
|
(
|
|
select *
|
|
from (values (1),(2))
|
|
as tvc_0
|
|
)
|
|
)
|
|
as dr_table
|
|
);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
2 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00
|
|
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join)
|
|
5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1`
|
|
# derived table with IN-predicate and group by
|
|
select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in (3,5)
|
|
group by b
|
|
) as dr_table;
|
|
max(a) b
|
|
2 5
|
|
select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(5))
|
|
as tvc_0
|
|
)
|
|
group by b
|
|
) as dr_table;
|
|
max(a) b
|
|
2 5
|
|
explain extended select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in (3,5)
|
|
group by b
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
|
|
2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
|
|
explain extended select * from
|
|
(
|
|
select max(a),b
|
|
from t1
|
|
where b in
|
|
(
|
|
select *
|
|
from (values (3),(5))
|
|
as tvc_0
|
|
)
|
|
group by b
|
|
) as dr_table;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
|
|
2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
|
3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00
|
|
4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
|
|
# prepare statement
|
|
prepare stmt from "select * from t1 where a in (1,2)";
|
|
execute stmt;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
execute stmt;
|
|
a b
|
|
1 2
|
|
1 1
|
|
2 5
|
|
deallocate prepare stmt;
|
|
# use inside out access from tvc rows
|
|
set @@in_predicate_conversion_threshold= default;
|
|
select * from t3 where a in (1,4);
|
|
a b
|
|
1 abc
|
|
1 todd
|
|
1 sm
|
|
4 yq
|
|
explain extended select * from t3 where a in (1,4);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4)
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
select * from t3 where a in (1,4);
|
|
a b
|
|
1 abc
|
|
1 todd
|
|
1 sm
|
|
4 yq
|
|
explain extended select * from t3 where a in (1,4);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00
|
|
1 PRIMARY t3 ref idx idx 5 tvc_0._col_1 3 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`_col_1`
|
|
# use vectors in IN predeicate
|
|
set @@in_predicate_conversion_threshold= 4;
|
|
select * from t1 where (a,b) in ((1,2),(3,4));
|
|
a b
|
|
1 2
|
|
explain extended select * from t1 where (a,b) in ((1,2),(3,4));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where 1
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
# trasformation works for the one IN predicate and doesn't work for the other
|
|
set @@in_predicate_conversion_threshold= 5;
|
|
select * from t2
|
|
where (a,b) in ((1,2),(8,9)) and
|
|
(a,c) in ((1,3),(8,0),(5,1));
|
|
a b c
|
|
1 2 3
|
|
8 9 0
|
|
explain extended select * from t2
|
|
where (a,b) in ((1,2),(8,9)) and
|
|
(a,c) in ((1,3),(8,0),(5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 100.00
|
|
2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where (`test`.`t2`.`a`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
|
|
set @@in_predicate_conversion_threshold= 2;
|
|
#
|
|
# mdev-14281: conversion of NOT IN predicate into subquery predicate
|
|
#
|
|
select * from t1
|
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
|
a b
|
|
4 6
|
|
9 7
|
|
1 1
|
|
2 5
|
|
7 8
|
|
select * from t1
|
|
where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
|
|
a b
|
|
4 6
|
|
9 7
|
|
1 1
|
|
2 5
|
|
7 8
|
|
explain extended select * from t1
|
|
where (a,b) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
|
|
explain extended select * from t1
|
|
where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`2`)))))
|
|
select * from t1
|
|
where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
|
|
a b
|
|
4 6
|
|
1 1
|
|
2 5
|
|
explain extended select * from t1
|
|
where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t1`.`b`) = `tvc_0`.`_col_2`)))))
|
|
select * from t2
|
|
where (a,c) not in ((1,2),(8,9), (5,1));
|
|
a b c
|
|
1 2 3
|
|
5 1 2
|
|
4 3 7
|
|
8 9 0
|
|
10 7 1
|
|
explain extended select * from t2
|
|
where (a,c) not in ((1,2),(8,9), (5,1));
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
|
|
2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 8 func,func 2 100.00 Using where; Full scan on NULL key
|
|
3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in (temporary) on key0 where trigcond(<cache>(`test`.`t2`.`a`) = `tvc_0`.`_col_1`) and trigcond(<cache>(`test`.`t2`.`c`) = `tvc_0`.`_col_2`)))))
|
|
drop table t1, t2, t3;
|
|
set @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-14947: conversion to TVC with only NULL values
|
|
#
|
|
CREATE TABLE t1 (i INT);
|
|
INSERT INTO t1 VALUES (3), (2), (7);
|
|
SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
i
|
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
|
|
SET in_predicate_conversion_threshold= 5;
|
|
SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
i
|
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` in (NULL,NULL,NULL,NULL,NULL)
|
|
SET in_predicate_conversion_threshold= default;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-14835: conversion to TVC with BIGINT or YEAR values
|
|
#
|
|
SET @@in_predicate_conversion_threshold= 2;
|
|
CREATE TABLE t1 (a BIGINT);
|
|
CREATE TABLE t2 (y YEAR);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
INSERT INTO t2 VALUES (2009), (2010), (2011);
|
|
SELECT * FROM t1 WHERE a IN ('1','5','3');
|
|
a
|
|
1
|
|
3
|
|
SELECT * FROM t2 WHERE y IN ('2009','2011');
|
|
y
|
|
2009
|
|
2011
|
|
DROP TABLE t1,t2;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-17222: conversion to TVC with no names for constants
|
|
# conversion to TVC with the same constants in the first row
|
|
#
|
|
SET @@in_predicate_conversion_threshold= 2;
|
|
CREATE TABLE t1 (f BINARY(16)) ENGINE=MYISAM;
|
|
INSERT INTO t1 VALUES
|
|
(x'BAE56AF2B1C2397D99D58E2A06761DDB'), (x'9B9B698BCCB939EE8F1EA56C1A2E5DAA'),
|
|
(x'A0A1C4FE39A239BABD3E0D8985E6BEA5');
|
|
SELECT COUNT(*) FROM t1 WHERE f IN
|
|
(x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2',
|
|
x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB');
|
|
COUNT(*)
|
|
2
|
|
CREATE TABLE t2 (f1 BINARY(16), f2 BINARY(16)) ENGINE=MYISAM;
|
|
INSERT INTO t2 VALUES
|
|
(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
|
|
(x'86052C062AAF368D84247ED0F6346A70', x'BF5C35045C6037C79E11026ABB9A3A4E');
|
|
SELECT COUNT(*) FROM t2 WHERE (f1,f2) IN
|
|
((x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2'),
|
|
(x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'),
|
|
(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'),
|
|
(x'1606014E7C4A312F83EDC9D91BBFCACA', x'33F6068E56FD3A1D8326517F0D81CB5A'));
|
|
COUNT(*)
|
|
1
|
|
CREATE TABLE t3 (f1 int, f2 int) ENGINE=MYISAM;
|
|
INSERT INTO t3 VALUES (2,5), (2,3), (1,2), (7,8), (1,1);
|
|
SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1));
|
|
f1 f2
|
|
1 2
|
|
1 1
|
|
DROP TABLE t1,t2,t3;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-20900: IN predicate to IN subquery conversion causes performance regression
|
|
#
|
|
create table t1(a int, b int);
|
|
insert into t1 select seq-1, seq-1 from seq_1_to_10;
|
|
set in_predicate_conversion_threshold=2;
|
|
explain select * from t1 where t1.a IN ("1","2","3","4");
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where t1.a IN ("1","2","3","4");
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=0;
|
|
explain select * from t1 where t1.a IN ("1","2","3","4");
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where t1.a IN ("1","2","3","4");
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=2;
|
|
explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
set in_predicate_conversion_threshold=0;
|
|
explain select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where
|
|
select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4));
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
drop table t1;
|
|
SET @@in_predicate_conversion_threshold= default;
|
|
#
|
|
# MDEV-27937: Prepared statement with ? in the list if IN predicate
|
|
#
|
|
set in_predicate_conversion_threshold=2;
|
|
create table t1 (id int, a int, b int);
|
|
insert into t1 values (1,3,30), (2,7,70), (3,1,10);
|
|
prepare stmt from "
|
|
select * from t1 where a in (7, ?, 5, 1);
|
|
";
|
|
execute stmt using 3;
|
|
id a b
|
|
1 3 30
|
|
2 7 70
|
|
3 1 10
|
|
deallocate prepare stmt;
|
|
prepare stmt from "
|
|
select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10));
|
|
";
|
|
execute stmt using 30;
|
|
id a b
|
|
1 3 30
|
|
2 7 70
|
|
3 1 10
|
|
deallocate prepare stmt;
|
|
drop table t1;
|
|
set in_predicate_conversion_threshold=default;
|
|
# End of 10.3 tests
|