From 9f718041101d81d612c38eaef52b64fc187d5619 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 13 Feb 2020 23:50:17 +0300 Subject: MDEV-21628: Index condition pushdown condition ... not used with BKA Partitioning storage now supports MRR but doesn't support Index Condition Pushdown (aka ICP). This causes counter-intuitive query plans for queries that use BKA and conditions that depend on index fields: - If the condition refers to other tables, BKA's variant of ICP is used to handle it. - If the condition depends on this table only, the optimizer will try to use regular ICP for it, which will fail because the storage engine doesn't support ICP. Make the optimizer be smarter in the second case: if we were not able to use regular ICP, use BKA's variant of ICP.. --- mysql-test/main/partition_mrr_myisam.result | 59 +++++++++++++++++++++++++++++ mysql-test/main/partition_mrr_myisam.test | 59 +++++++++++++++++++++++++++++ 2 files changed, 118 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index 1f1cea8e9d6..d989536324c 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -77,3 +77,62 @@ ID part_id key_col col2 28 8 2 123456 29 9 2 123456 drop table t1,t3; +# +# MDEV-21628: Index condition pushdown for a simple condition over +# index fields is not used for ref access of partitioned tables when employing BKA +# +create table t0 ( +tp int, a int, b int, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int, c varchar(12), index idx (a,b) +) engine=myisam +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)) engine=myisam; +insert into t2 values (1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status Engine-independent statistics collected +test.t0 analyze status OK +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; +set join_cache_level=6, optimizer_switch='mrr=on'; +explain +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +# This will use "Using index condition(BKA)" +explain +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +set join_cache_level=@tmp1, optimizer_switch=@tmp2; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_myisam.test b/mysql-test/main/partition_mrr_myisam.test index d67a37ab3d2..eb30319d81f 100644 --- a/mysql-test/main/partition_mrr_myisam.test +++ b/mysql-test/main/partition_mrr_myisam.test @@ -1,3 +1,62 @@ let $engine_type= myisam; --source include/partition_mrr.inc + +--echo # +--echo # MDEV-21628: Index condition pushdown for a simple condition over +--echo # index fields is not used for ref access of partitioned tables when employing BKA +--echo # + +create table t0 ( +tp int, a int, b int, c varchar(12), index idx (a,b) +); + +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); + +create table t1 ( +tp int, a int, b int, c varchar(12), index idx (a,b) +) engine=myisam +partition by list (tp) +( partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3)); +insert into t1 select * from t0; + +create table t2 (a int, index idx(a)) engine=myisam; +insert into t2 values (1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; + +analyze table t0,t1,t2; + +set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; +set join_cache_level=6, optimizer_switch='mrr=on'; + +explain +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; + +--echo # This will use "Using index condition(BKA)" +explain +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; + +set join_cache_level=@tmp1, optimizer_switch=@tmp2; + +drop table t0,t1,t2; + + -- cgit v1.2.1