[Commits] 8a9532f: MDEV-16188: Fixed several problems and bugs.

revision-id: 8a9532f2cc1a8eeb53ff04ca2c28b4756afc845b (mariadb-10.3.6-102-g8a9532f) parent(s): 5c4b7e6878126cc498f87391875807c68739914f author: Igor Babaev committer: Igor Babaev timestamp: 2019-01-17 11:29:13 -0800 message: MDEV-16188: Fixed several problems and bugs. 1. Completely re-wrote the function that prunes range filter candidates. The function now properly takes into account filter indexes that overlap. 2. Fixed bugs that calculate the cost of using filter. Ensured that the gain promised by usage of a filter would never be greater than the cost of accessing a table without it. 3. Fixed a bug that led to an improper detection of overlapping indexes. 4. Fixed a few other bugs. 5. Adjusted test cases and their output. --- mysql-test/main/index_intersect.result | 2 + mysql-test/main/index_intersect.test | 2 + mysql-test/main/index_intersect_innodb.result | 2 + mysql-test/main/index_merge_innodb.result | 1 + mysql-test/main/index_merge_innodb.test | 1 + mysql-test/main/index_merge_myisam.result | 25 +- mysql-test/main/index_merge_myisam.test | 25 +- mysql-test/main/join_outer_innodb.result | 8 +- mysql-test/main/key_cache.result | 6 +- mysql-test/main/mrr_icp_extra.result | 1 + mysql-test/main/mrr_icp_extra.test | 1 + mysql-test/main/null_key.result | 2 +- mysql-test/main/order_by.result | 2 +- mysql-test/main/partition_innodb.result | 2 + mysql-test/main/partition_innodb.test | 2 + mysql-test/main/range_mrr_icp.result | 21 +- mysql-test/main/range_mrr_icp.test | 1 + mysql-test/main/rowid_filter.result | 675 +++++++++++++++++++++++++- mysql-test/main/rowid_filter.test | 78 +++ mysql-test/main/select.result | 13 +- mysql-test/main/select.test | 2 + mysql-test/main/select_jcl6.result | 13 +- mysql-test/main/select_pkeycache.result | 13 +- mysql-test/main/subselect2.result | 2 +- mysql-test/main/subselect_mat_cost.result | 4 +- mysql-test/main/subselect_mat_cost.test | 4 + sql/handler.h | 2 + sql/opt_range.cc | 2 + sql/rowid_filter.cc | 269 +++++----- sql/rowid_filter.h | 45 +- sql/sql_select.cc | 59 ++- sql/structs.h | 2 +- sql/table.cc | 35 +- sql/table.h | 21 +- storage/innobase/handler/ha_innodb.cc | 3 +- storage/myisam/ha_myisam.cc | 3 +- 36 files changed, 1068 insertions(+), 281 deletions(-) diff --git a/mysql-test/main/index_intersect.result b/mysql-test/main/index_intersect.result index 04484c7..bb2478c 100644 --- a/mysql-test/main/index_intersect.result +++ b/mysql-test/main/index_intersect.result @@ -38,6 +38,7 @@ SELECT COUNT(*) FROM CountryLanguage; COUNT(*) 984 CREATE INDEX Name ON City(Name); +SET SESSION optimizer_switch='rowid_filter=off'; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; SELECT COUNT(*) FROM City; COUNT(*) @@ -972,3 +973,4 @@ f1 f4 f5 998 a 0 DROP TABLE t1; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION optimizer_switch='rowid_filter=default'; diff --git a/mysql-test/main/index_intersect.test b/mysql-test/main/index_intersect.test index 51a3b29..c77eccc 100644 --- a/mysql-test/main/index_intersect.test +++ b/mysql-test/main/index_intersect.test @@ -33,6 +33,7 @@ ANALYZE TABLE City; --enable_result_log --enable_query_log +SET SESSION optimizer_switch='rowid_filter=off'; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; SELECT COUNT(*) FROM City; @@ -460,3 +461,4 @@ WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; DROP TABLE t1; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION optimizer_switch='rowid_filter=default'; diff --git a/mysql-test/main/index_intersect_innodb.result b/mysql-test/main/index_intersect_innodb.result index c305886..854bcd7 100644 --- a/mysql-test/main/index_intersect_innodb.result +++ b/mysql-test/main/index_intersect_innodb.result @@ -44,6 +44,7 @@ SELECT COUNT(*) FROM CountryLanguage; COUNT(*) 984 CREATE INDEX Name ON City(Name); +SET SESSION optimizer_switch='rowid_filter=off'; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; SELECT COUNT(*) FROM City; COUNT(*) @@ -978,6 +979,7 @@ f1 f4 f5 998 a 0 DROP TABLE t1; SET SESSION optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION optimizer_switch='rowid_filter=default'; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result index 6a3ea83..26b51ba 100644 --- a/mysql-test/main/index_merge_innodb.result +++ b/mysql-test/main/index_merge_innodb.result @@ -1,5 +1,6 @@ set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; +set optimizer_switch='rowid_filter=off'; #---------------- Index merge test 2 ------------------------------------------- SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1,t2; diff --git a/mysql-test/main/index_merge_innodb.test b/mysql-test/main/index_merge_innodb.test index 53ce311..e8dc837 100644 --- a/mysql-test/main/index_merge_innodb.test +++ b/mysql-test/main/index_merge_innodb.test @@ -20,6 +20,7 @@ let $merge_table_support= 0; set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='index_merge_sort_intersection=off'; +set optimizer_switch='rowid_filter=off'; # The first two tests are disabled because of non deterministic explain output. # If include/index_merge1.inc can be enabled for InnoDB and all other diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index eebdf6c..8e83bdf 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1,4 +1,5 @@ set @optimizer_switch_save= @@optimizer_switch; +set optimizer_switch='rowid_filter=off'; set optimizer_switch='index_merge_sort_intersection=off'; #---------------- Index merge test 1 ------------------------------------------- SET SESSION STORAGE_ENGINE = MyISAM; @@ -1520,12 +1521,12 @@ explain select * from t1 where a=1 or b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where This should use ALL: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a=1 or b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where This should use sort-union: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=1 or b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where @@ -1535,17 +1536,17 @@ explain select * from t1 where a<1 or b <1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where This should use ALL: -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where This should use ALL: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where This will use sort-union: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where @@ -1557,7 +1558,7 @@ explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where And if we disable sort_union, union: -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where @@ -1576,22 +1577,22 @@ explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where No intersect when index_merge is disabled: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 49 Using where No intersect if it is disabled: -set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off'; +set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 49 Using where Do intersect when union was disabled -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where Do intersect when sort_union was disabled -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where @@ -1601,13 +1602,13 @@ explain select * from t1 where a=10 and b=10 or c=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where Should be only union left: -set optimizer_switch='default,index_merge_intersection=off'; +set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10 or c=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where This will switch to sort-union (intersection will be gone, too, that's a known limitation: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10 or c=10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test index 75beb9b..c3ac7fd 100644 --- a/mysql-test/main/index_merge_myisam.test +++ b/mysql-test/main/index_merge_myisam.test @@ -16,6 +16,7 @@ let $merge_table_support= 1; set @optimizer_switch_save= @@optimizer_switch; +set optimizer_switch='rowid_filter=off'; set optimizer_switch='index_merge_sort_intersection=off'; --source include/index_merge1.inc @@ -39,11 +40,11 @@ from t0 A, t0 B, t0 C; explain select * from t1 where a=1 or b=1; --echo This should use ALL: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a=1 or b=1; --echo This should use sort-union: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=1 or b=1; --echo This will use sort-union: @@ -51,16 +52,16 @@ set optimizer_switch=default; explain select * from t1 where a<1 or b <1; --echo This should use ALL: -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; --echo This should use ALL: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; --echo This will use sort-union: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a<1 or b <1; alter table t1 add d int, add key(d); @@ -71,7 +72,7 @@ set optimizer_switch=default; explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); --echo And if we disable sort_union, union: -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); drop table t1; @@ -92,19 +93,19 @@ set optimizer_switch=default; explain select * from t1 where a=10 and b=10; --echo No intersect when index_merge is disabled: -set optimizer_switch='default,index_merge=off'; +set optimizer_switch='default,index_merge=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; --echo No intersect if it is disabled: -set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off'; +set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; --echo Do intersect when union was disabled -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; --echo Do intersect when sort_union was disabled -set optimizer_switch='default,index_merge_sort_union=off'; +set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10; # Now take union-of-intersection and see how we can disable parts of it @@ -113,12 +114,12 @@ set optimizer_switch=default; explain select * from t1 where a=10 and b=10 or c=10; --echo Should be only union left: -set optimizer_switch='default,index_merge_intersection=off'; +set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10 or c=10; --echo This will switch to sort-union (intersection will be gone, too, --echo that's a known limitation: -set optimizer_switch='default,index_merge_union=off'; +set optimizer_switch='default,index_merge_union=off,rowid_filter=off'; explain select * from t1 where a=10 and b=10 or c=10; set optimizer_switch=default; diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index f00a723..6f3fb09 100644 --- a/mysql-test/main/join_outer_innodb.result +++ b/mysql-test/main/join_outer_innodb.result @@ -444,9 +444,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter +1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter +1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index @@ -466,9 +466,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where -1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter +1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter +1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index 1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index 36c75ad..b3368b2 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -739,13 +739,13 @@ p 1019 explain select i from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k1,k2 k1 5 const 189 Using where +1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using filter select i from t2 where a='yyyy' and i=3; i 3 explain select a from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref k1,k2 k1 5 const 189 Using where +1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using filter select a from t2 where a='yyyy' and i=3 ; a yyyy @@ -753,7 +753,7 @@ select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES default 2 NULL 32768 1024 # # 0 3178 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 NULL 262143 2048 # # 0 3231 43 1594 30 +keycache1 7 NULL 262143 2048 # # 0 3283 43 1594 30 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; insert into t2 values (7000, 3, 'yyyy'); diff --git a/mysql-test/main/mrr_icp_extra.result b/mysql-test/main/mrr_icp_extra.result index 6943f12..49acd7b 100644 --- a/mysql-test/main/mrr_icp_extra.result +++ b/mysql-test/main/mrr_icp_extra.result @@ -1,6 +1,7 @@ call mtr.add_suppression("Can't find record in .*"); set @mrr_icp_extra_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set optimizer_switch='rowid_filter=off'; SET NAMES latin1; CREATE TABLE t1 (s1 char(10) COLLATE latin1_german1_ci, diff --git a/mysql-test/main/mrr_icp_extra.test b/mysql-test/main/mrr_icp_extra.test index 75ddc85..38306f5 100644 --- a/mysql-test/main/mrr_icp_extra.test +++ b/mysql-test/main/mrr_icp_extra.test @@ -4,6 +4,7 @@ call mtr.add_suppression("Can't find record in .*"); set @mrr_icp_extra_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set optimizer_switch='rowid_filter=off'; SET NAMES latin1; CREATE TABLE t1 (s1 char(10) COLLATE latin1_german1_ci, diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index afb9b42..525a121 100644 --- a/mysql-test/main/null_key.result +++ b/mysql-test/main/null_key.result @@ -181,7 +181,7 @@ insert into t2 values (7),(8); explain select * from t2 straight_join t1 where t1.a=t2.a and b is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 ref|filter a,b a|b 10|5 test.t2.a,const 2 (13%) Using where; Using index; Using filter +1 SIMPLE t1 ref a,b a 10 test.t2.a,const 2 Using where; Using index drop index b on t1; explain select * from t2,t1 where t1.a=t2.a and b is null; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 17c5a94..6e1518f 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1577,7 +1577,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort -1 SIMPLE t2 ref|filter a,b,c a|b 40|5 test.t1.a,const 11 (26%) Using index condition; Using filter +1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; diff --git a/mysql-test/main/partition_innodb.result b/mysql-test/main/partition_innodb.result index 151218f..46353c6 100644 --- a/mysql-test/main/partition_innodb.result +++ b/mysql-test/main/partition_innodb.result @@ -704,9 +704,11 @@ insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, 10+A.a + 10*B.a + 100*C.a + 1000*D.a, 2000 + A.a + 10*B.a + 100*C.a + 1000*D.a from t2 A, t2 B, t2 C ,t2 D; +set statement optimizer_switch='rowid_filter=off' for explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,a,b b,a 4,4 NULL # Using intersect(b,a); Using where; Using index +set statement optimizer_switch='rowid_filter=off' for create temporary table t3 as select * from t1 where a=1 and b=2 and pk between 1 and 999 ; select count(*) from t3; diff --git a/mysql-test/main/partition_innodb.test b/mysql-test/main/partition_innodb.test index bc1b323..a8bbb7c 100644 --- a/mysql-test/main/partition_innodb.test +++ b/mysql-test/main/partition_innodb.test @@ -782,8 +782,10 @@ insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a, # This should show index_merge, using intersect --replace_column 9 # +set statement optimizer_switch='rowid_filter=off' for explain select * from t1 where a=1 and b=2 and pk between 1 and 999999 ; # 794 rows in output +set statement optimizer_switch='rowid_filter=off' for create temporary table t3 as select * from t1 where a=1 and b=2 and pk between 1 and 999 ; select count(*) from t3; diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index a39237b..91fd84a 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1,5 +1,6 @@ set @mrr_icp_extra_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set optimizer_switch='rowid_filter=off'; set @innodb_stats_persistent_save= @@innodb_stats_persistent; set @innodb_stats_persistent_sample_pages_save= @@innodb_stats_persistent_sample_pages; @@ -2592,7 +2593,7 @@ explain select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx idx 5 NULL 6 Using index condition; Rowid-ordered scan -1 SIMPLE t2 ref|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (14%) Using where; Using filter +1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where explain format=json select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; EXPLAIN @@ -2619,14 +2620,6 @@ EXPLAIN "key_length": "5", "used_key_parts": ["d"], "ref": ["test.t1.a"], - "rowid_filter": { - "range": { - "key": "idx2", - "used_key_parts": ["e"] - }, - "rows": 15, - "selectivity_pct": 14.423 - }, "rows": 12, "filtered": 100, "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" @@ -2697,7 +2690,7 @@ insert into t1 select * from t1; explain select * from t1,t2 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 7 (7%) Using index condition; Using where; Rowid-ordered scan; Using filter +1 SIMPLE t2 range idx1,idx2 idx1 5 NULL 7 Using index condition; Using where; Rowid-ordered scan 1 SIMPLE t1 ref idx idx 5 test.t2.d 11 explain format=json select * from t1,t2 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1; @@ -2712,14 +2705,6 @@ EXPLAIN "key": "idx1", "key_length": "5", "used_key_parts": ["d"], - "rowid_filter": { - "range": { - "key": "idx2", - "used_key_parts": ["e"] - }, - "rows": 7, - "selectivity_pct": 6.7308 - }, "rows": 7, "filtered": 100, "index_condition": "t2.d is not null", diff --git a/mysql-test/main/range_mrr_icp.test b/mysql-test/main/range_mrr_icp.test index 29e7af3..4c6983c 100644 --- a/mysql-test/main/range_mrr_icp.test +++ b/mysql-test/main/range_mrr_icp.test @@ -1,5 +1,6 @@ set @mrr_icp_extra_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set optimizer_switch='rowid_filter=off'; --source range.test diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index eb20ad1..8d224ee 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1,13 +1,678 @@ DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; +CREATE INDEX i_l_quantity ON lineitem(l_quantity); +CREATE INDEX i_o_totalprice ON orders(o_totalprice); +ANALYZE TABLE lineitem, orders; +Table Op Msg_type Msg_text +dbt3_s001.lineitem analyze status Table is already up to date +dbt3_s001.orders analyze status Table is already up to date +set optimizer_use_condition_selectivity=2; +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using filter +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rowid_filter": { + "range": { + "key": "i_l_quantity", + "used_key_parts": ["l_quantity"] + }, + "rows": 662, + "selectivity_pct": 11.024 + }, + "rows": 509, + "filtered": 11.024, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +l_orderkey l_linenumber l_shipdate l_quantity +1121 5 1997-04-27 47 +1121 6 1997-04-21 50 +1441 7 1997-06-07 50 +1443 1 1997-02-05 47 +1473 1 1997-05-05 50 +1568 2 1997-04-06 46 +1632 1 1997-01-25 47 +1632 3 1997-01-29 47 +1954 7 1997-06-04 49 +1959 1 1997-05-05 46 +2151 3 1997-01-20 49 +2177 5 1997-05-10 46 +2369 2 1997-01-02 47 +2469 3 1997-01-11 48 +2469 6 1997-03-03 49 +2470 2 1997-06-02 50 +260 1 1997-03-24 50 +288 2 1997-04-19 49 +289 4 1997-03-14 48 +3009 1 1997-03-19 48 +3105 3 1997-02-28 48 +3106 2 1997-02-27 49 +3429 1 1997-04-08 48 +3490 2 1997-06-27 50 +3619 1 1997-01-22 49 +3619 3 1997-01-31 46 +3969 3 1997-05-29 46 +4005 4 1997-01-31 49 +4036 1 1997-06-21 46 +4066 4 1997-02-17 49 +4098 1 1997-01-26 46 +422 3 1997-06-21 46 +4258 3 1997-01-02 46 +4421 2 1997-04-21 46 +4421 3 1997-05-25 46 +4453 3 1997-05-29 48 +4484 7 1997-03-17 50 +4609 3 1997-02-11 46 +484 1 1997-03-06 49 +484 3 1997-01-24 50 +484 5 1997-03-05 48 +485 1 1997-03-28 50 +4868 1 1997-04-29 47 +4868 3 1997-04-23 49 +4934 1 1997-05-20 48 +4967 1 1997-05-27 50 +5090 2 1997-04-05 46 +5152 2 1997-03-10 50 +5158 4 1997-04-10 49 +5606 3 1997-03-11 46 +5606 7 1997-02-01 46 +5762 4 1997-03-02 47 +581 3 1997-02-27 49 +5829 5 1997-01-31 49 +5831 4 1997-02-24 46 +5895 2 1997-04-27 47 +5895 3 1997-03-15 49 +5952 1 1997-06-30 49 +705 1 1997-04-18 46 +836 3 1997-03-21 46 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 509 Using index condition; Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rows": 509, + "filtered": 11.024, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +l_orderkey l_linenumber l_shipdate l_quantity +1121 5 1997-04-27 47 +1121 6 1997-04-21 50 +1441 7 1997-06-07 50 +1443 1 1997-02-05 47 +1473 1 1997-05-05 50 +1568 2 1997-04-06 46 +1632 1 1997-01-25 47 +1632 3 1997-01-29 47 +1954 7 1997-06-04 49 +1959 1 1997-05-05 46 +2151 3 1997-01-20 49 +2177 5 1997-05-10 46 +2369 2 1997-01-02 47 +2469 3 1997-01-11 48 +2469 6 1997-03-03 49 +2470 2 1997-06-02 50 +260 1 1997-03-24 50 +288 2 1997-04-19 49 +289 4 1997-03-14 48 +3009 1 1997-03-19 48 +3105 3 1997-02-28 48 +3106 2 1997-02-27 49 +3429 1 1997-04-08 48 +3490 2 1997-06-27 50 +3619 1 1997-01-22 49 +3619 3 1997-01-31 46 +3969 3 1997-05-29 46 +4005 4 1997-01-31 49 +4036 1 1997-06-21 46 +4066 4 1997-02-17 49 +4098 1 1997-01-26 46 +422 3 1997-06-21 46 +4258 3 1997-01-02 46 +4421 2 1997-04-21 46 +4421 3 1997-05-25 46 +4453 3 1997-05-29 48 +4484 7 1997-03-17 50 +4609 3 1997-02-11 46 +484 1 1997-03-06 49 +484 3 1997-01-24 50 +484 5 1997-03-05 48 +485 1 1997-03-28 50 +4868 1 1997-04-29 47 +4868 3 1997-04-23 49 +4934 1 1997-05-20 48 +4967 1 1997-05-27 50 +5090 2 1997-04-05 46 +5152 2 1997-03-10 50 +5158 4 1997-04-10 49 +5606 3 1997-03-11 46 +5606 7 1997-02-01 46 +5762 4 1997-03-02 47 +581 3 1997-02-27 49 +5829 5 1997-01-31 49 +5831 4 1997-02-24 46 +5895 2 1997-04-27 47 +5895 3 1997-03-15 49 +5952 1 1997-06-30 49 +705 1 1997-04-18 46 +836 3 1997-03-21 46 +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using filter +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rows": 98, + "filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 81, + "selectivity_pct": 5.4 + }, + "rows": 1, + "filtered": 5.4, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +o_orderkey l_linenumber l_shipdate o_totalprice +1156 3 1997-01-24 217682.81 +1156 4 1997-01-18 217682.81 +1156 6 1997-01-27 217682.81 +1156 7 1997-01-01 217682.81 +2180 2 1997-01-03 208481.57 +2180 3 1997-01-03 208481.57 +3619 1 1997-01-22 222274.54 +3619 3 1997-01-31 222274.54 +3619 6 1997-01-25 222274.54 +484 3 1997-01-24 219920.62 +5606 6 1997-01-11 219959.08 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rows": 98, + "filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rows": 1, + "filtered": 5.4, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +o_orderkey l_linenumber l_shipdate o_totalprice +1156 3 1997-01-24 217682.81 +1156 4 1997-01-18 217682.81 +1156 6 1997-01-27 217682.81 +1156 7 1997-01-01 217682.81 +2180 2 1997-01-03 208481.57 +2180 3 1997-01-03 208481.57 +3619 1 1997-01-22 222274.54 +3619 3 1997-01-31 222274.54 +3619 6 1997-01-25 222274.54 +484 3 1997-01-24 219920.62 +5606 6 1997-01-11 219959.08 +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) Using where; Using filter +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rowid_filter": { + "range": { + "key": "i_l_quantity", + "used_key_parts": ["l_quantity"] + }, + "rows": 662, + "selectivity_pct": 11.024 + }, + "rows": 509, + "filtered": 11.024, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 152, + "selectivity_pct": 10.133 + }, + "rows": 1, + "filtered": 10.133, + "attached_condition": "orders.o_totalprice between 180000 and 230000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +o_orderkey l_linenumber l_shipdate l_quantity o_totalprice +1632 1 1997-01-25 47 183286.33 +1632 3 1997-01-29 47 183286.33 +2177 5 1997-05-10 46 183493.42 +2469 3 1997-01-11 48 192074.23 +2469 6 1997-03-03 49 192074.23 +3619 1 1997-01-22 49 222274.54 +3619 3 1997-01-31 46 222274.54 +484 1 1997-03-06 49 219920.62 +484 3 1997-01-24 50 219920.62 +484 5 1997-03-05 48 219920.62 +4934 1 1997-05-20 48 180478.16 +5606 3 1997-03-11 46 219959.08 +5606 7 1997-02-01 46 219959.08 +5829 5 1997-01-31 49 183734.56 +5895 2 1997-04-27 47 201419.83 +5895 3 1997-03-15 49 201419.83 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate 4 NULL 509 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rows": 509, + "filtered": 11.024, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rows": 1, + "filtered": 10.133, + "attached_condition": "orders.o_totalprice between 180000 and 230000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +o_orderkey l_linenumber l_shipdate l_quantity o_totalprice +1632 1 1997-01-25 47 183286.33 +1632 3 1997-01-29 47 183286.33 +2177 5 1997-05-10 46 183493.42 +2469 3 1997-01-11 48 192074.23 +2469 6 1997-03-03 49 192074.23 +3619 1 1997-01-22 49 222274.54 +3619 3 1997-01-31 46 222274.54 +484 1 1997-03-06 49 219920.62 +484 3 1997-01-24 50 219920.62 +484 5 1997-03-05 48 219920.62 +4934 1 1997-05-20 48 180478.16 +5606 3 1997-03-11 46 219959.08 +5606 7 1997-02-01 46 219959.08 +5829 5 1997-01-31 49 183734.56 +5895 2 1997-04-27 47 201419.83 +5895 3 1997-03-15 49 201419.83 +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 Using index condition +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using filter +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "rows": 81, + "filtered": 100, + "index_condition": "orders.o_totalprice between 200000 and 230000" + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "rowid_filter": { + "range": { + "key": "i_l_shipdate", + "used_key_parts": ["l_shipDATE"] + }, + "rows": 509, + "selectivity_pct": 8.4763 + }, + "rows": 4, + "filtered": 8.4763, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +o_orderkey l_linenumber l_shipdate o_totalprice +1156 3 1997-01-24 217682.81 +1156 4 1997-01-18 217682.81 +1156 6 1997-01-27 217682.81 +1156 7 1997-01-01 217682.81 +1890 1 1997-04-02 202364.58 +1890 3 1997-02-09 202364.58 +1890 4 1997-04-08 202364.58 +1890 5 1997-04-15 202364.58 +1890 6 1997-02-13 202364.58 +2180 2 1997-01-03 208481.57 +2180 3 1997-01-03 208481.57 +3619 1 1997-01-22 222274.54 +3619 3 1997-01-31 222274.54 +3619 4 1997-03-18 222274.54 +3619 6 1997-01-25 222274.54 +453 1 1997-06-30 216826.73 +453 2 1997-06-30 216826.73 +484 1 1997-03-06 219920.62 +484 2 1997-04-09 219920.62 +484 3 1997-01-24 219920.62 +484 4 1997-04-29 219920.62 +484 5 1997-03-05 219920.62 +484 6 1997-04-06 219920.62 +5606 2 1997-02-23 219959.08 +5606 3 1997-03-11 219959.08 +5606 4 1997-02-06 219959.08 +5606 6 1997-01-11 219959.08 +5606 7 1997-02-01 219959.08 +5859 2 1997-05-15 210643.96 +5859 5 1997-05-28 210643.96 +5859 6 1997-06-15 210643.96 +5895 1 1997-04-05 201419.83 +5895 2 1997-04-27 201419.83 +5895 3 1997-03-15 201419.83 +5895 4 1997-03-03 201419.83 +5895 5 1997-04-30 201419.83 +5895 6 1997-04-19 201419.83 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "rows": 81, + "filtered": 100, + "index_condition": "orders.o_totalprice between 200000 and 230000" + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "rows": 4, + "filtered": 8.4763, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +o_orderkey l_linenumber l_shipdate o_totalprice +1156 3 1997-01-24 217682.81 +1156 4 1997-01-18 217682.81 +1156 6 1997-01-27 217682.81 +1156 7 1997-01-01 217682.81 +1890 1 1997-04-02 202364.58 +1890 3 1997-02-09 202364.58 +1890 4 1997-04-08 202364.58 +1890 5 1997-04-15 202364.58 +1890 6 1997-02-13 202364.58 +2180 2 1997-01-03 208481.57 +2180 3 1997-01-03 208481.57 +3619 1 1997-01-22 222274.54 +3619 3 1997-01-31 222274.54 +3619 4 1997-03-18 222274.54 +3619 6 1997-01-25 222274.54 +453 1 1997-06-30 216826.73 +453 2 1997-06-30 216826.73 +484 1 1997-03-06 219920.62 +484 2 1997-04-09 219920.62 +484 3 1997-01-24 219920.62 +484 4 1997-04-29 219920.62 +484 5 1997-03-05 219920.62 +484 6 1997-04-06 219920.62 +5606 2 1997-02-23 219959.08 +5606 3 1997-03-11 219959.08 +5606 4 1997-02-06 219959.08 +5606 6 1997-01-11 219959.08 +5606 7 1997-02-01 219959.08 +5859 2 1997-05-15 210643.96 +5859 5 1997-05-28 210643.96 +5859 6 1997-06-15 210643.96 +5895 1 1997-04-05 201419.83 +5895 2 1997-04-27 201419.83 +5895 3 1997-03-15 201419.83 +5895 4 1997-03-03 201419.83 +5895 5 1997-04-30 201419.83 +5895 6 1997-04-19 201419.83 # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate EXPLAIN SELECT * FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 60 (1%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 53 (2%) Using index condition; Using where; Using filter 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate # orders : {i_o_orderdate} -> i_o_orderdate @@ -17,7 +682,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND o_orderdate > '1997-01-15'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 60 (1%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 53 (2%) Using index condition; Using where; Using filter 1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where # lineitem : {i_l_receiptdate, i_l_shipdate, # i_l_commitdate} -> i_l_receiptdate @@ -27,7 +692,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25' AND l_commitdate BETWEEN '1997-01-05' AND '1997-01-25'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_receiptdate|i_l_commitdate 4|4 NULL 60 (1%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_receiptdate|i_l_commitdate 4|4 NULL 53 (1%) Using index condition; Using where; Using filter 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 # lineitem : {i_l_receiptdate, i_l_shipdate, # i_l_commitdate} -> i_l_commitdate @@ -37,7 +702,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND l_receiptdate BETWEEN '1997-01-01' AND '1997-01-25' AND l_commitdate BETWEEN '1997-01-15' AND '1997-01-25'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_commitdate|i_l_shipdate 4|4 NULL 35 (1%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_commitdate|i_l_receiptdate 4|4 NULL 28 (1%) Using index condition; Using where; Using filter 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 CREATE INDEX i_l_extendedprice ON lineitem(l_extendedprice); # lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate, @@ -179,7 +844,7 @@ EXPLAIN "selectivity_pct": 0.1332 }, "rows": 6, - "filtered": 100, + "filtered": 0.1332, "index_condition": "lineitem.l_receiptDATE between '1997-01-09' and '1997-01-10'", "attached_condition": "lineitem.l_shipDATE between '1997-01-09' and '1997-01-10'" }, diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 73d7326..274fab6 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -14,6 +14,84 @@ use dbt3_s001; --enable_result_log --enable_query_log +CREATE INDEX i_l_quantity ON lineitem(l_quantity); + +CREATE INDEX i_o_totalprice ON orders(o_totalprice); + +ANALYZE TABLE lineitem, orders; + +set optimizer_use_condition_selectivity=2; + +let $with_filter= +set statement optimizer_switch='rowid_filter=on' for; + +let $without_filter= +set statement optimizer_switch='rowid_filter=off' for; + +let $q1= +SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem + WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND + l_quantity > 45; + +eval $with_filter EXPLAIN $q1; +eval $with_filter EXPLAIN FORMAT=JSON $q1; +--sorted_result +eval $with_filter $q1; + +eval $without_filter EXPLAIN $q1; +eval $without_filter EXPLAIN FORMAT=JSON $q1; +--sorted_result +eval $without_filter $q1; + +let $q2= +SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice + FROM orders JOIN lineitem ON o_orderkey=l_orderkey + WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND + o_totalprice between 200000 and 230000; + +eval $with_filter EXPLAIN $q2; +eval $with_filter EXPLAIN FORMAT=JSON $q2; +--sorted_result +eval $with_filter $q2; + +eval $without_filter EXPLAIN $q2; +eval $without_filter EXPLAIN FORMAT=JSON $q2; +--sorted_result +eval $without_filter $q2; + +let $q3= +SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice + FROM orders JOIN lineitem ON o_orderkey=l_orderkey + WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND + l_quantity > 45 AND + o_totalprice between 180000 and 230000; + +eval $with_filter EXPLAIN $q3; +eval $with_filter EXPLAIN FORMAT=JSON $q3; +--sorted_result +eval $with_filter $q3; + +eval $without_filter EXPLAIN $q3; +eval $without_filter EXPLAIN FORMAT=JSON $q3; +--sorted_result +eval $without_filter $q3; + +let $q4= +SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice + FROM orders JOIN lineitem ON o_orderkey=l_orderkey + WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND + o_totalprice between 200000 and 230000; + +eval $with_filter EXPLAIN $q4; +eval $with_filter EXPLAIN FORMAT=JSON $q4; +--sorted_result +eval $with_filter $q4; + +eval $without_filter EXPLAIN $q4; +eval $without_filter EXPLAIN FORMAT=JSON $q4; +--sorted_result +eval $without_filter $q4; + --echo # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate EXPLAIN SELECT * FROM orders JOIN lineitem ON o_orderkey=l_orderkey diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 75106b5..220f500 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition +1 SIMPLE t2 ref c c 5 test.t1.a 2 EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3698,6 +3698,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) 24 @@ -3740,7 +3743,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx1 5 const 2 Using index condition; Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test index e7525f8..0d43dfd 100644 --- a/mysql-test/main/select.test +++ b/mysql-test/main/select.test @@ -3230,6 +3230,8 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +ANALYZE TABLE t1; + SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; SELECT COUNT(*) FROM t1 WHERE ID1_with_null=3 AND ID2_with_null IS NULL; SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL; diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 16672bd..bc86a27 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3485,13 +3485,13 @@ INSERT INTO t2 VALUES EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t2 ref c c 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3709,6 +3709,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) 24 @@ -3751,7 +3754,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx1 5 const 2 Using index condition; Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 75106b5..220f500 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3474,13 +3474,13 @@ INSERT INTO t2 VALUES EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition +1 SIMPLE t2 ref c c 5 test.t1.a 2 EXPLAIN SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL c NULL NULL NULL 18 Using where -1 SIMPLE t1 eq_ref|filter PRIMARY,b PRIMARY|b 4|5 test.t2.c 1 (30%) Using where; Using filter +1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where +1 SIMPLE t2 ref c c 5 test.t1.a 2 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3698,6 +3698,9 @@ INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID1_with_null IS NULL; INSERT INTO t1 SELECT * FROM t1 WHERE ID2_with_null IS NULL; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null=3; COUNT(*) 24 @@ -3740,7 +3743,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx1 5 const 2 Using index condition; Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index f2047bf..7620842 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -132,7 +132,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where 1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where -1 PRIMARY t3 eq_ref|filter PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY|CMFLDRPARNT_IDX 34|35 test.t3.PARENTID 1 (29%) Using where; Using filter +1 PRIMARY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 Using where drop table t1, t2, t3, t4; CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; INSERT INTO t1 VALUES (1),(2); diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result index 245739c..5acf8cb 100644 --- a/mysql-test/main/subselect_mat_cost.result +++ b/mysql-test/main/subselect_mat_cost.result @@ -280,6 +280,7 @@ Q2.2m: Countries that speak French OR Spanish, but do not speak English MATERIALIZATION because the outer query filters less rows than Q5-a, so there are more lookups. +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT Country.Name FROM Country, CountryLanguage @@ -289,7 +290,8 @@ AND Code = Country; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Using where; Rowid-ordered scan 1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where -2 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 48 Using index condition +3 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 48 Using index condition +set statement optimizer_switch='rowid_filter=off' for SELECT Country.Name FROM Country, CountryLanguage WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') diff --git a/mysql-test/main/subselect_mat_cost.test b/mysql-test/main/subselect_mat_cost.test index 5a1fb55..5f44d0d 100644 --- a/mysql-test/main/subselect_mat_cost.test +++ b/mysql-test/main/subselect_mat_cost.test @@ -205,6 +205,9 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English -- echo Countries that speak French OR Spanish, but do not speak English -- echo MATERIALIZATION because the outer query filters less rows than Q5-a, -- echo so there are more lookups. + + +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT Country.Name FROM Country, CountryLanguage @@ -212,6 +215,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish') AND Code = Country; +set statement optimizer_switch='rowid_filter=off' for SELECT Country.Name FROM Country, CountryLanguage WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English') diff --git a/sql/handler.h b/sql/handler.h index f122a26..8869d3d 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -325,6 +325,8 @@ enum enum_alter_inplace_result { */ #define HA_CLUSTERED_INDEX 512 +#define HA_DO_RANGE_FILTER_PUSHDOWN 1024 + /* bits in alter_table_flags: */ diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 4fc321f..ba2705b 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2520,6 +2520,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, quick=0; needed_reg.clear_all(); quick_keys.clear_all(); + head->with_impossible_ranges.clear_all(); DBUG_ASSERT(!head->is_filled_at_execution()); if (keys_to_use.is_clear_all() || head->is_filled_at_execution()) DBUG_RETURN(0); @@ -8556,6 +8557,7 @@ int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree1, SEL_TREE *tree2, if (key && key->type == SEL_ARG::IMPOSSIBLE) { result->type= SEL_TREE::IMPOSSIBLE; + param->table->with_impossible_ranges.set_bit(param->real_keynr[key_no]); DBUG_RETURN(1); } result_keys.set_bit(key_no); diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index 2ce6d83..7af9c4e 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -5,6 +5,27 @@ #include "rowid_filter.h" #include "sql_select.h" +inline +double Range_filter_cost_info::lookup_cost( + Rowid_filter_container_type cont_type) +{ + switch (cont_type) { + case ORDERED_ARRAY_CONTAINER: + return log(est_elements)*0.01; + default: + DBUG_ASSERT(0); + return 0; + } +} + + +inline +double Range_filter_cost_info::avg_access_and_eval_gain_per_row( + Rowid_filter_container_type cont_type) +{ + return (1+1.0/TIME_FOR_COMPARE) * (1 - selectivity) - + lookup_cost(cont_type); +} /** Sets information about filter with key_numb index. @@ -12,15 +33,19 @@ and gets slope and interscept values. */ -void Range_filter_cost_info::init(TABLE *tab, uint key_numb) +void Range_filter_cost_info::init(Rowid_filter_container_type cont_type, + TABLE *tab, uint idx) { + container_type= cont_type; table= tab; - key_no= key_numb; + key_no= idx; est_elements= table->quick_rows[key_no]; - b= build_cost(ORDERED_ARRAY_CONTAINER); + b= build_cost(container_type); selectivity= est_elements/((double) table->stat_records()); - a= (1 + COST_COND_EVAL)*(1 - selectivity) - lookup_cost(); - intersect_x_axis_abcissa= b/a; + a= avg_access_and_eval_gain_per_row(container_type); + if (a > 0) + cross_x= b/a; + abs_independent.clear_all(); } double @@ -43,128 +68,99 @@ Range_filter_cost_info::build_cost(Rowid_filter_container_type container_type) return cost; } -/** - @brief - Sort available filters by their building cost in the increasing order - - @details - The method starts sorting available filters from the first filter that - is not defined as the best filter. If there are two filters that are - defined as the best filters there is no need to sort other filters. - Best filters are already sorted by their building cost and have the - smallest bulding cost in comparison with other filters by definition. - - As the sorting method bubble sort is used. -*/ -void TABLE::sort_range_filter_cost_info_array() +static +int compare_range_filter_cost_info_by_a(Range_filter_cost_info **filter_ptr_1, + Range_filter_cost_info **filter_ptr_2) { - if (best_filter_count <= 2) - return; - - for (uint i= best_filter_count; i < range_filter_cost_info_elements-1; i++) - { - for (uint j= i+1; j < range_filter_cost_info_elements; j++) - { - if (range_filter_cost_info[i].intersect_x_axis_abcissa > - range_filter_cost_info[j].intersect_x_axis_abcissa) - swap_variables(Range_filter_cost_info, - range_filter_cost_info[i], - range_filter_cost_info[j]); - } - } + double diff= (*filter_ptr_2)->a - (*filter_ptr_1)->a; + return (diff < 0 ? -1 : (diff > 0 ? 1 : 0)); } - /** @brief - The method searches for the filters that can reduce the join cost the most @details - The method looks through the available filters trying to choose the best - filter and eliminate as many filters as possible. - - Filters are considered as a linear functions. The best filter is the linear - function that intersects all other linear functions not in the I quadrant - and has the biggest a (slope) value. This filter will reduce the partial - join cost the most. If it is possible the second best filter is also - chosen. The second best filter can be used if the ref access is made on - the index of the first best filter. - - So there is no need to store all other filters except filters that - intersect in the I quadrant. It is impossible to say on this step which - filter is better and will give the biggest gain. - - The number of filters that can be used is stored in the - range_filter_cost_info_elements variable. */ void TABLE::prune_range_filters() { - key_map pruned_filter_map; - pruned_filter_map.clear_all(); - Range_filter_cost_info *max_slope_filters[2] = {0, 0}; + uint i, j; - for (uint i= 0; i < range_filter_cost_info_elements; i++) + Range_filter_cost_info **filter_ptr_1= range_filter_cost_info_ptr; + for (i= 0; i < range_filter_cost_info_elems; i++, filter_ptr_1++) { - Range_filter_cost_info *filter= &range_filter_cost_info[i]; - if (filter->a < 0) + uint key_no= (*filter_ptr_1)->key_no; + Range_filter_cost_info **filter_ptr_2= filter_ptr_1 + 1; + for (j= i+1; j < range_filter_cost_info_elems; j++, filter_ptr_2++) { - range_filter_cost_info_elements--; - swap_variables(Range_filter_cost_info, range_filter_cost_info[i], - range_filter_cost_info[range_filter_cost_info_elements]); - i--; - continue; + key_map map= key_info[key_no].overlapped; + map.intersect(key_info[(*filter_ptr_2)->key_no].overlapped); + if (map.is_clear_all()) + { + (*filter_ptr_1)->abs_independent.set_bit((*filter_ptr_2)->key_no); + (*filter_ptr_2)->abs_independent.set_bit(key_no); + } } - for (uint j= i+1; j < range_filter_cost_info_elements; j++) - { - Range_filter_cost_info *cand_filter= &range_filter_cost_info[j]; + } - double intersect_x= filter->get_intersect_x(cand_filter); - double intersect_y= filter->get_intersect_y(intersect_x); + /* Sort the array range_filter_cost_info by 'a' */ + my_qsort(range_filter_cost_info_ptr, + range_filter_cost_info_elems, + sizeof(Range_filter_cost_info *), + (qsort_cmp) compare_range_filter_cost_info_by_a); - if (intersect_x > 0 && intersect_y > 0) + Range_filter_cost_info **cand_filter_ptr= range_filter_cost_info_ptr; + for (i= 0; i < range_filter_cost_info_elems; i++, cand_filter_ptr++) + { + bool is_pruned= false; + Range_filter_cost_info **usable_filter_ptr= range_filter_cost_info_ptr; + key_map abs_indep; + abs_indep.clear_all(); + for (uint j= 0; j < i; j++, usable_filter_ptr++) + { + if ((*cand_filter_ptr)->cross_x >= (*usable_filter_ptr)->cross_x) { - pruned_filter_map.set_bit(cand_filter->key_no); - pruned_filter_map.set_bit(filter->key_no); + if (abs_indep.is_set((*usable_filter_ptr)->key_no)) + { + is_pruned= true; + break; + } + abs_indep.merge((*usable_filter_ptr)->abs_independent); } - } - if (!pruned_filter_map.is_set(filter->key_no)) - { - if (!max_slope_filters[0]) - max_slope_filters[0]= filter; else { - if (!max_slope_filters[1] || - max_slope_filters[1]->a < filter->a) - max_slope_filters[1]= filter; - if (max_slope_filters[0]->a < max_slope_filters[1]->a) - swap_variables(Range_filter_cost_info*, max_slope_filters[0], - max_slope_filters[1]); + Range_filter_cost_info *moved= *cand_filter_ptr; + memmove(usable_filter_ptr+1, usable_filter_ptr, + sizeof(Range_filter_cost_info *) * (i-j-1)); + *usable_filter_ptr= moved; } } - } - - for (uint i= 0; i<2; i++) - { - if (max_slope_filters[i]) + if (is_pruned) { - swap_variables(Range_filter_cost_info, - range_filter_cost_info[i], - *max_slope_filters[i]); - if (i == 0 && - max_slope_filters[1] == &range_filter_cost_info[0]) - max_slope_filters[1]= max_slope_filters[0]; - - best_filter_count++; - max_slope_filters[i]= &range_filter_cost_info[i]; + memmove(cand_filter_ptr, cand_filter_ptr+1, + sizeof(Range_filter_cost_info *) * + (range_filter_cost_info_elems - 1 - i)); + range_filter_cost_info_elems--; } } - sort_range_filter_cost_info_array(); } -void TABLE::select_usable_range_filters(THD *thd) +static uint +get_max_range_filter_elements_for_table(THD *thd, TABLE *tab, + Rowid_filter_container_type cont_type) +{ + switch (cont_type) { + case ORDERED_ARRAY_CONTAINER : + return thd->variables.max_rowid_filter_size/tab->file->ref_length; + default : + DBUG_ASSERT(0); + return 0; + } +} + +void TABLE::init_cost_info_for_usable_range_filters(THD *thd) { uint key_no; key_map usable_range_filter_keys; @@ -172,73 +168,74 @@ void TABLE::select_usable_range_filters(THD *thd) key_map::Iterator it(quick_keys); while ((key_no= it++) != key_map::Iterator::BITMAP_END) { - if (quick_rows[key_no] > - thd->variables.max_rowid_filter_size/file->ref_length) + if (!(file->index_flags(key_no, 0, 1) & HA_DO_RANGE_FILTER_PUSHDOWN)) + continue; + if (key_no == s->primary_key && file->primary_key_is_clustered()) + continue; + if (quick_rows[key_no] > + get_max_range_filter_elements_for_table(thd, this, + ORDERED_ARRAY_CONTAINER)) continue; usable_range_filter_keys.set_bit(key_no); } - if (usable_range_filter_keys.is_clear_all()) + range_filter_cost_info_elems= usable_range_filter_keys.bits_set(); + if (!range_filter_cost_info_elems) return; - range_filter_cost_info_elements= usable_range_filter_keys.bits_set(); + range_filter_cost_info_ptr= + (Range_filter_cost_info **) thd->calloc(sizeof(Range_filter_cost_info *) * + range_filter_cost_info_elems); range_filter_cost_info= - new (thd->mem_root) Range_filter_cost_info [range_filter_cost_info_elements]; + new (thd->mem_root) Range_filter_cost_info[range_filter_cost_info_elems]; + if (!range_filter_cost_info_ptr || !range_filter_cost_info) + { + range_filter_cost_info_elems= 0; + return; + } + + Range_filter_cost_info **curr_ptr= range_filter_cost_info_ptr; Range_filter_cost_info *curr_filter_cost_info= range_filter_cost_info; key_map::Iterator li(usable_range_filter_keys); while ((key_no= li++) != key_map::Iterator::BITMAP_END) { - curr_filter_cost_info->init(this, key_no); + *curr_ptr= curr_filter_cost_info; + curr_filter_cost_info->init(ORDERED_ARRAY_CONTAINER, this, key_no); + curr_ptr++; curr_filter_cost_info++; } prune_range_filters(); } -Range_filter_cost_info -*TABLE::best_filter_for_current_join_order(uint ref_key_no, - double record_count, - double records) +Range_filter_cost_info *TABLE::best_filter_for_partial_join(uint access_key_no, + double records) { - if (!this || range_filter_cost_info_elements == 0) + if (!this || range_filter_cost_info_elems == 0 || + covering_keys.is_set(access_key_no)) return 0; - double card= record_count*records; - Range_filter_cost_info *best_filter= &range_filter_cost_info[0]; - - if (card < best_filter->intersect_x_axis_abcissa) + if (access_key_no == s->primary_key && file->primary_key_is_clustered()) return 0; - if (best_filter_count != 0) - { - if (best_filter->key_no == ref_key_no) - { - if (best_filter_count == 2) - { - best_filter= &range_filter_cost_info[1]; - if (card < best_filter->intersect_x_axis_abcissa) - return 0; - return best_filter; - } - } - else - return best_filter; - } - double best_filter_improvement= 0.0; - best_filter= 0; + Range_filter_cost_info *best_filter= 0; + double best_filter_gain= 0; - key_map *intersected_with= &key_info->intersected_with; - for (uint i= best_filter_count; i < range_filter_cost_info_elements; i++) + key_map *overlapped= &key_info[access_key_no].overlapped; + for (uint i= 0; i < range_filter_cost_info_elems ; i++) { - Range_filter_cost_info *filter= &range_filter_cost_info[i]; - if ((filter->key_no == ref_key_no) || intersected_with->is_set(filter->key_no)) + double curr_gain = 0; + Range_filter_cost_info *filter= range_filter_cost_info_ptr[i]; + if ((filter->key_no == access_key_no) || + overlapped->is_set(filter->key_no)) continue; - if (card < filter->intersect_x_axis_abcissa) + if (records < filter->cross_x) break; - if (best_filter_improvement < filter->get_filter_gain(card)) + curr_gain= filter->get_gain(records); + if (best_filter_gain < curr_gain) { - best_filter_improvement= filter->get_filter_gain(card); + best_filter_gain= curr_gain; best_filter= filter; } } diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index 9b93679..7cec865 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -129,48 +129,51 @@ typedef enum class Range_filter_cost_info : public Sql_alloc { public: + Rowid_filter_container_type container_type; TABLE *table; uint key_no; double est_elements; double b; // intercept of the linear function double a; // slope of the linear function double selectivity; - double intersect_x_axis_abcissa; + double cross_x; + key_map abs_independent; /** Filter cost functions */ - /* Cost to lookup into filter */ - inline double lookup_cost() - { - return log(est_elements)*0.01; - } Range_filter_cost_info() : table(0), key_no(0) {} - void init(TABLE *tab, uint key_numb); + void init(Rowid_filter_container_type cont_type, + TABLE *tab, uint key_numb); double build_cost(Rowid_filter_container_type container_type); - inline double get_intersect_x(Range_filter_cost_info *filter) + inline double lookup_cost(Rowid_filter_container_type cont_type); + + inline double + avg_access_and_eval_gain_per_row(Rowid_filter_container_type cont_type); + + /** + Get the gain that usage of filter promises for 'rows' key entries + */ + inline double get_gain(double rows) + { + return rows * a - b; + } + + inline double get_adjusted_gain(double rows, double worst_seeks) { - if (a == filter->a) - return DBL_MAX; - return (b - filter->b)/(a - filter->a); + return get_gain(rows) - + (1 - selectivity) * (rows - MY_MIN(rows, worst_seeks)); } - inline double get_intersect_y(double intersect_x) + + inline double get_cmp_gain(double rows) { - if (intersect_x == DBL_MAX) - return DBL_MAX; - return intersect_x*a - b; + return rows * (1 - selectivity) / TIME_FOR_COMPARE; } - /** - Get a gain that a usage of filter in some partial join order - with the cardinaly card gives - */ - inline double get_filter_gain(double card) - { return card*a - b; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3edf63f..724e156 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1490,6 +1490,7 @@ bool JOIN::make_range_filters() key_map filter_map; filter_map.clear_all(); filter_map.set_bit(tab->filter->key_no); + filter_map.merge(tab->table->with_impossible_ranges); bool force_index_save= tab->table->force_index; tab->table->force_index= true; (void) sel->test_quick_select(thd, filter_map, (table_map) 0, @@ -5118,9 +5119,8 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, select->quick=0; impossible_range= records == 0 && s->table->reginfo.impossible_range; if (join->thd->lex->sql_command == SQLCOM_SELECT && - join->table_count > 1 && optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER)) - s->table->select_usable_range_filters(join->thd); + s->table->init_cost_info_for_usable_range_filters(join->thd); } if (!impossible_range) { @@ -7328,11 +7328,14 @@ best_access_path(JOIN *join, if (records < DBL_MAX) { - filter= table->best_filter_for_current_join_order(start_key->key, - records, - record_count); - if (filter && (filter->get_filter_gain(record_count*records) < tmp)) - tmp= tmp - filter->get_filter_gain(record_count*records); + double rows= record_count * records; + filter= table->best_filter_for_partial_join(start_key->key, rows); + if (filter) + { + tmp-= filter->get_adjusted_gain(rows, s->worst_seeks) - + filter->get_cmp_gain(rows); + DBUG_ASSERT(tmp >= 0); + } } if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) @@ -7438,6 +7441,7 @@ best_access_path(JOIN *join, Here we estimate its cost. */ + filter= 0; if (s->quick) { /* @@ -7453,6 +7457,18 @@ best_access_path(JOIN *join, (s->quick->read_time + (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE); + if ( s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) + { + double rows= record_count * s->found_records; + uint key_no= s->quick->index; + filter= s->table->best_filter_for_partial_join(key_no, rows); + if (filter) + { + tmp-= filter->get_gain(rows); + DBUG_ASSERT(tmp >= 0); + } + } + loose_scan_opt.check_range_access(join, idx, s->quick); } else @@ -7498,24 +7514,23 @@ best_access_path(JOIN *join, else tmp+= s->startup_cost; - if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) - { - filter= s->table->best_filter_for_current_join_order(s->quick->index, - rnd_records, - record_count); - if (filter && (filter->get_filter_gain(record_count*rnd_records) < tmp)) - tmp= tmp - filter->get_filter_gain(record_count*rnd_records); - } - /* We estimate the cost of evaluating WHERE clause for found records as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus tmp give us total cost of using TABLE SCAN */ + + double filter_cmp_gain= 0; + if (filter) + { + filter_cmp_gain= filter->get_cmp_gain(record_count * s->found_records); + } + if (best == DBL_MAX || (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records < (best_key->is_for_hash_join() ? best_time : - best + record_count/(double) TIME_FOR_COMPARE*records))) + best + record_count/(double) TIME_FOR_COMPARE*records - + filter_cmp_gain))) { /* If the table has a range (s->quick is set) make_join_select() @@ -12708,9 +12723,7 @@ ha_rows JOIN_TAB::get_examined_rows() double examined_rows; SQL_SELECT *sel= filesort? filesort->select : this->select; - if (filter) - examined_rows= records_read; - else if (sel && sel->quick && use_quick != 2) + if (sel && sel->quick && use_quick != 2) examined_rows= (double)sel->quick->records; else if (type == JT_NEXT || type == JT_ALL || type == JT_HASH || type ==JT_HASH_NEXT) @@ -22477,6 +22490,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, tab->use_quick=1; tab->ref.key= -1; tab->ref.key_parts=0; // Don't use ref key. + tab->filter= 0; + if (tab->rowid_filter) + { + delete tab->rowid_filter; + tab->rowid_filter= 0; + } tab->read_first_record= join_init_read_record; if (tab->is_using_loose_index_scan()) tab->join->tmp_table_param.precomputed_group_by= TRUE; diff --git a/sql/structs.h b/sql/structs.h index 5cc64c1..a45cc34 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -120,7 +120,7 @@ typedef struct st_key { */ LEX_CSTRING name; key_part_map ext_key_part_map; - key_map intersected_with; + key_map overlapped; uint block_size; enum ha_key_alg algorithm; /* diff --git a/sql/table.cc b/sql/table.cc index a2408d6..67c369f 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1228,12 +1228,13 @@ static const Type_handler *old_frm_type_handler(uint pack_flag, } -void TABLE_SHARE::set_intersected_keys() +void TABLE_SHARE::set_overlapped_keys() { KEY *key1= key_info; for (uint i= 0; i < keys; i++, key1++) { - key1->intersected_with.clear_all(); + key1->overlapped.clear_all(); + key1->overlapped.set_bit(i); } key1= key_info; for (uint i= 0; i < keys; i++, key1++) @@ -1242,18 +1243,23 @@ void TABLE_SHARE::set_intersected_keys() for (uint j= i+1; j < keys; j++, key2++) { KEY_PART_INFO *key_part1= key1->key_part; - KEY_PART_INFO *key_part2= key2->key_part; - uint n= key1->user_defined_key_parts; - set_if_smaller(n, key2->user_defined_key_parts); - for (uint k= 0; k < n; k++, key_part1++, key_part2++) + uint n1= key1->user_defined_key_parts; + uint n2= key2->user_defined_key_parts; + for (uint k= 0; k < n1; k++, key_part1++) { - if (key_part1->fieldnr == key_part2->fieldnr) + KEY_PART_INFO *key_part2= key2->key_part; + for (uint l= 0; l < n2; l++, key_part2++) { - key1->intersected_with.set_bit(j); - key2->intersected_with.set_bit(i); - break; + if (key_part1->fieldnr == key_part2->fieldnr) + { + key1->overlapped.set_bit(j); + key2->overlapped.set_bit(i); + goto end_checking_overlap; + } } - } + } + end_checking_overlap: + ; } } } @@ -2553,7 +2559,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write, null_length, 255); } - set_intersected_keys(); + set_overlapped_keys(); /* Handle virtual expressions */ if (vcol_screen_length && share->frm_version >= FRM_VER_EXPRESSSIONS) @@ -4689,8 +4695,9 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL; - best_filter_count= 0; - range_filter_cost_info_elements= 0; + range_filter_cost_info_elems= 0; + range_filter_cost_info_ptr= NULL; + range_filter_cost_info= NULL; #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; diff --git a/sql/table.h b/sql/table.h index a027c85..feeb9ee 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1003,7 +1003,7 @@ struct TABLE_SHARE /* frees the memory allocated in read_frm_image */ void free_frm_image(const uchar *frm); - void set_intersected_keys(); + void set_overlapped_keys(); }; @@ -1503,21 +1503,14 @@ struct TABLE double get_materialization_cost(); // Now used only if is_splittable()==true void add_splitting_info_for_key_field(struct KEY_FIELD *key_field); - - /** - Range filter info - */ - /* Minimum possible #T value to apply filter*/ - uint best_filter_count; - uint range_filter_cost_info_elements; + key_map with_impossible_ranges; + uint range_filter_cost_info_elems; + Range_filter_cost_info **range_filter_cost_info_ptr; Range_filter_cost_info *range_filter_cost_info; - Range_filter_cost_info - *best_filter_for_current_join_order(uint ref_key_no, - double record_count, - double records); - void sort_range_filter_cost_info_array(); + void init_cost_info_for_usable_range_filters(THD *thd); void prune_range_filters(); - void select_usable_range_filters(THD *thd); + Range_filter_cost_info *best_filter_for_partial_join(uint access_key_no, + double records); /** System Versioning support */ diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index e43019b..8a092d6 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -5250,7 +5250,8 @@ ha_innobase::index_flags( ulong flags = HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER | HA_READ_RANGE | HA_KEYREAD_ONLY | extra_flag - | HA_DO_INDEX_COND_PUSHDOWN; + | HA_DO_INDEX_COND_PUSHDOWN + | HA_DO_RANGE_FILTER_PUSHDOWN; /* For spatial index, we don't support descending scan and ICP so far. */ diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index de7a71d..012691e 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -769,7 +769,8 @@ ulong ha_myisam::index_flags(uint inx, uint part, bool all_parts) const else { flags= HA_READ_NEXT | HA_READ_PREV | HA_READ_RANGE | - HA_READ_ORDER | HA_KEYREAD_ONLY | HA_DO_INDEX_COND_PUSHDOWN; + HA_READ_ORDER | HA_KEYREAD_ONLY | HA_DO_INDEX_COND_PUSHDOWN | + HA_DO_RANGE_FILTER_PUSHDOWN; } return flags; }