[Commits] 6aaa69f: MDEV-16188 Use in-memory PK filters built from range index scans
revision-id: 6aaa69f37360200c4807282c8df1b2c21c707d2d (mariadb-10.3.6-97-g6aaa69f) parent(s): 5f46670bd09babbee75a24ac82eb4ade0706da66 author: Igor Babaev committer: Igor Babaev timestamp: 2018-11-13 22:08:29 -0800 message: MDEV-16188 Use in-memory PK filters built from range index scans Fixed bugs for phase 1. Also added quick objects for filters. --- mysql-test/main/index_merge_myisam.result | 2 +- mysql-test/main/join_cache.result | 2 +- mysql-test/main/join_nested_jcl6.result | 2 +- mysql-test/main/join_outer_innodb.result | 8 +- mysql-test/main/mdev13607.result | 30 +++--- mysql-test/main/mysql_client_test.result | 16 +-- mysql-test/main/mysqld--help.result | 7 +- mysql-test/main/null_key.result | 2 +- mysql-test/main/order_by.result | 2 +- mysql-test/main/ps_1general.result | 4 +- mysql-test/main/ps_2myisam.result | 2 +- mysql-test/main/ps_3innodb.result | 2 +- mysql-test/main/ps_4heap.result | 2 +- mysql-test/main/ps_5merge.result | 4 +- mysql-test/main/range.result | 10 +- mysql-test/main/range_mrr_icp.result | 10 +- mysql-test/main/rowid_filter.result | 151 ++++++++++++++++++++++++++++ mysql-test/main/select.result | 16 +-- mysql-test/main/select_jcl6.result | 16 +-- mysql-test/main/select_pkeycache.result | 16 +-- mysql-test/main/stat_tables.result | 2 +- mysql-test/main/stat_tables_disabled.result | 6 +- mysql-test/main/stat_tables_innodb.result | 4 +- mysql-test/main/subselect2.result | 2 +- sql/opt_subselect.h | 1 + sql/rowid_filter.cc | 3 +- sql/rowid_filter.h | 3 +- sql/sql_class.cc | 2 +- sql/sql_explain.cc | 42 ++++++-- sql/sql_explain.h | 12 ++- sql/sql_select.cc | 107 ++++++++++++++++---- sql/sql_select.h | 1 + tests/mysql_client_test.c | 4 +- 33 files changed, 373 insertions(+), 120 deletions(-) diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 725a888..b0c3f76 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -272,7 +272,7 @@ explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where +1 SIMPLE t0 ref|filter i1,i2 i1|i2 4|4 const 1 (1%) Using where; Using filter 1 SIMPLE t1 ref i1 i1 4 const 1 explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fde6e0f..46891e9 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -1213,7 +1213,7 @@ ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country range Name Name 52 NULL # Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_range|filter Population,Country #hash#Country:Population|Population 3:4|4 world.Country.Code # Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join); Using filter SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 6b1901d..cbd459e 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -2002,7 +2002,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (43%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter 1 SIMPLE t6 ALL PRIMARY,b_i NULL NULL NULL 7 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b diff --git a/mysql-test/main/join_outer_innodb.result b/mysql-test/main/join_outer_innodb.result index 6f3fb09..f00a723 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 PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 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 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 PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index +1 SIMPLE t13 ref|filter PRIMARY,m3 PRIMARY|m3 4|4 test.t1.a1 1 (33%) Using where; Using index; Using filter 1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index -1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 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 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/mdev13607.result b/mysql-test/main/mdev13607.result index 08848bc..0d573b0 100644 --- a/mysql-test/main/mdev13607.result +++ b/mysql-test/main/mdev13607.result @@ -76,21 +76,21 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived3> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived4> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived5> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived6> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived7> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived8> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived14> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived17> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived6> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived14> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) +1 PRIMARY <derived17> ALL NULL NULL NULL NULL 17319535557742690304 Using join buffer (incremental, BNL join) 17 DERIVED r1 ALL NULL NULL NULL NULL 2 17 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 17 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result index 6f65979..b5100f9 100644 --- a/mysql-test/main/mysql_client_test.result +++ b/mysql-test/main/mysql_client_test.result @@ -135,7 +135,7 @@ EXPALIN number of fields: 10 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 @@ -148,7 +148,7 @@ ANALYZE number of fields: 13 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 @@ -164,7 +164,7 @@ EXPALIN INSERT number of fields: 10 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON INSERT number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 @@ -177,7 +177,7 @@ ANALYZE INSERT number of fields: 13 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 @@ -193,7 +193,7 @@ EXPALIN UPDATE number of fields: 10 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON UPDATE number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 @@ -206,7 +206,7 @@ ANALYZE UPDATE number of fields: 13 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 @@ -222,7 +222,7 @@ EXPALIN DELETE number of fields: 10 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 EXPALIN JSON DELETE number of fields: 1 - 0: name: 'EXPLAIN'/''; table: ''/''; db: ''; catalog: 'def'; length: 234; max_length: 0; type: 253; decimals: 39 @@ -235,7 +235,7 @@ ANALYZE DELETE number of fields: 13 - 5: name: 'key'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 10; max_length: 0; type: 8; decimals: 0 + - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 5ee85ee..68ed55c 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -546,6 +546,8 @@ The following specify which files/extra groups are read (specified before remain relay log will be rotated automatically when the size exceeds this value. If 0 at startup, it's set to max_binlog_size + --max-rowid-filter-size=# + The maximum number of rows that fit in memory --max-seeks-for-key=# Limit assumed max number of seeks when looking up rows based on a key @@ -679,7 +681,7 @@ The following specify which files/extra groups are read (specified before remain optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, condition_pushdown_for_derived, split_materialized, - condition_pushdown_for_subquery + condition_pushdown_for_subquery, rowid_filter --optimizer-use-condition-selectivity=# Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial @@ -1509,6 +1511,7 @@ max-long-data-size 16777216 max-prepared-stmt-count 16382 max-recursive-iterations 18446744073709551615 max-relay-log-size 1073741824 +max-rowid-filter-size 131072 max-seeks-for-key 18446744073709551615 max-session-mem-used 9223372036854775807 max-sort-length 1024 @@ -1545,7 +1548,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on optimizer-use-condition-selectivity 1 performance-schema FALSE performance-schema-accounts-size -1 diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index 4b4a4b8..e14fbe6 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 a,b a 10 test.t2.a,const 2 Using where; Using index +1 SIMPLE t1 ref|filter a,b a|b 10|5 test.t2.a,const 2 (13%) Using where; Using index; Using filter 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 e2b05cc..4eeb26e 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1503,7 +1503,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 a,b,c a 40 test.t1.a,const 11 Using index condition +1 SIMPLE t2 ref|filter a,b,c a|b 40|5 test.t1.a,const 11 (26%) Using index condition; Using filter 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/ps_1general.result b/mysql-test/main/ps_1general.result index 035372a..e8cd2ef 100644 --- a/mysql-test/main/ps_1general.result +++ b/mysql-test/main/ps_1general.result @@ -451,7 +451,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 14 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort @@ -467,7 +467,7 @@ def possible_keys 253 4_OR_8_K 7 Y 0 39 8 def key 253 64 7 Y 0 39 8 def key_len 253 4_OR_8_K 1 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 37 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition; Using filesort diff --git a/mysql-test/main/ps_2myisam.result b/mysql-test/main/ps_2myisam.result index 3906875..c85abaa 100644 --- a/mysql-test/main/ps_2myisam.result +++ b/mysql-test/main/ps_2myisam.result @@ -1161,7 +1161,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/main/ps_3innodb.result b/mysql-test/main/ps_3innodb.result index 9f5c895..53f736f 100644 --- a/mysql-test/main/ps_3innodb.result +++ b/mysql-test/main/ps_3innodb.result @@ -1161,7 +1161,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/main/ps_4heap.result b/mysql-test/main/ps_4heap.result index 46b4d9c..0cf1ed1 100644 --- a/mysql-test/main/ps_4heap.result +++ b/mysql-test/main/ps_4heap.result @@ -1162,7 +1162,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/main/ps_5merge.result b/mysql-test/main/ps_5merge.result index cc2f0f8..4915539 100644 --- a/mysql-test/main/ps_5merge.result +++ b/mysql-test/main/ps_5merge.result @@ -1205,7 +1205,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 @@ -4573,7 +4573,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 50de308..44f7e51 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1896,7 +1896,7 @@ explain select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 64 -1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 range|filter i1,i2 i1|i2 4|4 NULL 78 (89%) Using where; Using join buffer (flat, BNL join); Using filter select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; count(*) 128 @@ -2530,7 +2530,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 -1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where +1 SIMPLE t2 ref|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (13%) Using where; Using filter 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 @@ -2553,7 +2553,7 @@ EXPLAIN "access_type": "ref", "possible_keys": ["idx1", "idx2"], "key": "idx1", - "key_length": "5", + "key_length": "5|5", "used_key_parts": ["d"], "ref": ["test.t1.a"], "rows": 12, @@ -2626,7 +2626,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 idx1,idx2 idx2 5 NULL 6 Using where +1 SIMPLE t2 range|filter idx1,idx2 idx2|idx1 5|5 NULL 6 (7%) Using where; Using filter 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; @@ -2639,7 +2639,7 @@ EXPLAIN "access_type": "range", "possible_keys": ["idx1", "idx2"], "key": "idx2", - "key_length": "5", + "key_length": "5|5", "used_key_parts": ["e"], "rows": 6, "filtered": 100, diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index a6c5737..94a2b5f 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1898,7 +1898,7 @@ explain select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 64 -1 SIMPLE t1 range i1,i2 i1 4 NULL 78 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +1 SIMPLE t1 range|filter i1,i2 i1|i2 4|4 NULL 78 (89%) Using where; Rowid-ordered scan; Using join buffer (flat, BNL join); Using filter select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; count(*) 128 @@ -2536,7 +2536,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 idx1,idx2 idx1 5 test.t1.a 12 Using where +1 SIMPLE t2 ref|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (13%) Using where; Using filter 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 @@ -2560,7 +2560,7 @@ EXPLAIN "access_type": "ref", "possible_keys": ["idx1", "idx2"], "key": "idx1", - "key_length": "5", + "key_length": "5|5", "used_key_parts": ["d"], "ref": ["test.t1.a"], "rows": 12, @@ -2633,7 +2633,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 idx1,idx2 idx2 5 NULL 6 Using where; Rowid-ordered scan +1 SIMPLE t2 range|filter idx1,idx2 idx2|idx1 5|5 NULL 6 (7%) Using where; Rowid-ordered scan; Using filter 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; @@ -2646,7 +2646,7 @@ EXPLAIN "access_type": "range", "possible_keys": ["idx1", "idx2"], "key": "idx2", - "key_length": "5", + "key_length": "5|5", "used_key_parts": ["e"], "rows": 6, "filtered": 100, diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result new file mode 100644 index 0000000..e1296c3 --- /dev/null +++ b/mysql-test/main/rowid_filter.result @@ -0,0 +1,151 @@ +DROP DATABASE IF EXISTS dbt3_s001; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +# 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 59 (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_receiptdate +# orders : {i_o_orderdate} -> i_o_orderdate +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' 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 59 (1%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_orderdate PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (26%) Using where; Using filter +# lineitem : {i_l_receiptdate, i_l_shipdate, +# i_l_commitdate} -> 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' 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 59 (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 +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-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 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, +# i_l_extendedprice} -> i_l_extendedprice +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1996-11-01' AND '1997-01-21' AND +l_receiptdate BETWEEN '1996-11-21' AND '1997-01-25' AND +l_commitdate BETWEEN '1996-11-25' AND '1997-01-20' AND +l_extendedprice BETWEEN 26000 AND 27000; +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_extendedprice i_l_extendedprice|i_l_commitdate 9|4 NULL 77 (3%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +# lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_shipdate +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-11' AND '1997-01-21' AND +l_extendedprice BETWEEN 26000 AND 27000; +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_extendedprice i_l_shipdate|i_l_extendedprice 4|9 NULL 29 (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_shipdate, i_l_extendedprice} -> i_l_extendedprice +# intersection point in the I quadrant +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE (l_shipdate BETWEEN '1997-01-11' AND '1997-01-26' OR +l_shipdate BETWEEN '1995-02-01' AND '1995-02-14' OR +l_shipdate BETWEEN '1994-12-12' AND '1994-12-28' + ) AND l_extendedprice BETWEEN 26000 AND 27000; +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_extendedprice i_l_extendedprice|i_l_shipdate 9|4 NULL 77 (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_shipdate, i_l_extendedprice} -> i_l_shipdate +# parallel lines +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE (l_shipdate BETWEEN '1997-01-11' AND '1997-01-26' OR +l_shipdate BETWEEN '1995-02-01' AND '1995-02-16' OR +l_shipdate BETWEEN '1994-12-12' AND '1994-12-27' + ) AND l_extendedprice BETWEEN 26000 AND 27000; +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_extendedprice i_l_extendedprice|i_l_shipdate 9|4 NULL 77 (2%) 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_discount ON lineitem(l_discount); +CREATE INDEX i_l_tax ON lineitem(l_tax); +# lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate, +# i_l_extendedprice, i_l_discount, i_l_tax} +# -> {i_l_extendedprice} +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1996-11-01' AND '1997-01-21' AND +l_receiptdate BETWEEN '1996-11-21' AND '1997-01-25' AND +l_commitdate BETWEEN '1996-11-25' AND '1997-01-20' AND +l_extendedprice BETWEEN 26000 AND 27000 AND +l_discount BETWEEN 0 AND 0.01 AND +l_tax BETWEEN 0.03 AND 0.04; +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_extendedprice,i_l_discount,i_l_tax i_l_extendedprice|i_l_commitdate 9|4 NULL 77 (3%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +DROP INDEX i_l_extendedprice on lineitem; +DROP INDEX i_l_discount on lineitem; +DROP INDEX i_l_tax on lineitem; +SET max_rowid_filter_size= 1024; +# lineitem : {i_l_shipdate, i_l_receiptdate, i_l_commitdate} +# -> i_l_shipdate +# i_l_commitdate isn't in-memory -> isn't used +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1996-12-28' AND '1997-01-20' AND +l_receiptdate BETWEEN '1996-12-21' AND '1997-01-25' AND +l_commitdate BETWEEN '1996-12-01' 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_shipdate|i_l_receiptdate 4|4 NULL 72 (2%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +SET max_rowid_filter_size= DEFAULT; +# lineitem : {i_l_shipdate, i_l_commitdate} -> i_l_commitdate +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND +l_commitdate BETWEEN '1997-01-10' AND '1997-01-12'; +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_commitdate i_l_commitdate 4 NULL 6 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +# lineitem : {i_l_shipdate, i_l_commitdate} -> i_l_commitdate +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND +l_commitdate BETWEEN '1993-01-10' AND '1997-01-12'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem ALL PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate NULL NULL NULL 6005 Using where +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +# lineitem : {i_l_shipdate, i_l_commitdate, i_l_receiptdate} +# -> i_l_receiptdate +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1993-01-01' AND '1997-01-30' AND +l_commitdate BETWEEN '1993-01-10' AND '1997-01-12' AND +l_receiptdate BETWEEN '1997-01-10' AND '1997-01-12'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity,i_l_commitdate i_l_receiptdate 4 NULL 10 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +# lineitem : {i_l_shipdate, i_l_receiptdate} -> i_l_receiptdate +# indexes with high selectivity +EXPLAIN SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND +l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10'; +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 6 (1%) Using index condition; Using where; Using filter +1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 94a9231..a03dbe4 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.a 2 +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 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 +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 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index b45fb8c..45adc5a 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 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 +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 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 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 +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 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3627,7 +3627,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3635,7 +3635,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3643,7 +3643,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3651,7 +3651,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 94a9231..a03dbe4 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition -1 SIMPLE t2 ref c c 5 test.t1.a 2 +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 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 t1 range PRIMARY,b b 5 NULL 3 Using index condition; Using where -1 SIMPLE t2 ref c c 5 test.t1.a 2 +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 DROP TABLE t1, t2; create table t1 ( a int unsigned not null auto_increment primary key, @@ -3616,7 +3616,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3624,7 +3624,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3632,7 +3632,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3640,7 +3640,7 @@ t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where -1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (25%) Using where; Using filter DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); CREATE TABLE t2 ( f11 int PRIMARY KEY ); diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 40290ca..9a5c023 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -332,7 +332,7 @@ and o_orderkey=l_orderkey and p_partkey=l_partkey; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where -1 SIMPLE part eq_ref PRIMARY,i_p_retailprice PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where +1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using filter select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' diff --git a/mysql-test/main/stat_tables_disabled.result b/mysql-test/main/stat_tables_disabled.result index f57abc3..6faa562 100644 --- a/mysql-test/main/stat_tables_disabled.result +++ b/mysql-test/main/stat_tables_disabled.result @@ -31,7 +31,7 @@ limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL # Using where; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey # Using where -1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey # Using where +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 # Using where; Using filter set use_stat_tables='preferably'; EXPLAIN select sql_calc_found_rows straight_join l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, @@ -46,7 +46,7 @@ limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where -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 +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 (54%) Using where; Using filter flush tables customer, orders, lineitem; EXPLAIN select sql_calc_found_rows straight_join l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, @@ -61,7 +61,7 @@ limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where -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 +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 (54%) Using where; Using filter # End of the test case for mdev-503 set optimizer_switch=@save_optimizer_switch; DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 070d13d..ce41412 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -357,9 +357,9 @@ from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' and o_orderkey=l_orderkey and p_partkey=l_partkey; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using where; Using index 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 Using index -1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 1 Using index +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using filter select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index 31e7774..517d834 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 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 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/sql/opt_subselect.h b/sql/opt_subselect.h index 0311182..846add7 100644 --- a/sql/opt_subselect.h +++ b/sql/opt_subselect.h @@ -303,6 +303,7 @@ class Loose_scan_opt pos->loosescan_picker.loosescan_parts= best_max_loose_keypart + 1; pos->use_join_buffer= FALSE; pos->table= tab; + pos->filter= tab->filter; // todo need ref_depend_map ? DBUG_PRINT("info", ("Produced a LooseScan plan, key %s, %s", tab->table->key_info[best_loose_scan_key].name.str, diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index bcca9a0..aa8194d 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -35,7 +35,7 @@ void Range_filter_cost_info::init(TABLE *tab, uint key_numb) void TABLE::sort_range_filter_cost_info_array() { - if (best_filter_count == 2) + if (best_filter_count <= 2) return; for (uint i= best_filter_count; i < range_filter_cost_info_elements-1; i++) @@ -216,3 +216,4 @@ Range_filter_cost_info } return best_filter; } + diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index 0d8520f..3097522 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -130,6 +130,7 @@ class Range_filter_cost_info : public Sql_alloc double a; // slope of the linear function double selectivity; double intersect_x_axis_abcissa; + SQL_SELECT *select; /** Filter cost functions @@ -155,7 +156,7 @@ class Range_filter_cost_info : public Sql_alloc } /* End of filter cost functions */ - Range_filter_cost_info() : table(0), key_no(0) {} + Range_filter_cost_info() : table(0), key_no(0), select(0) {} void init(TABLE *tab, uint key_numb); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 0dd8235..eae7d98 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2673,7 +2673,7 @@ void THD::make_explain_field_list(List<Item> &field_list, uint8 explain_flags, mem_root); item->maybe_null=1; field_list.push_back(item=new (mem_root) - Item_empty_string(this, "ref|filter", + Item_empty_string(this, "ref", NAME_CHAR_LEN*MAX_REF_PARTS, cs), mem_root); item->maybe_null=1; diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 23bc1e9..c7e9d6c 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -380,11 +380,13 @@ int print_explain_row(select_result_sink *result, item_list.push_back(item, mem_root); /* 'rows' */ + StringBuffer<64> rows_str; if (rows) { + rows_str.append_ulonglong((ulonglong)(*rows)); item_list.push_back(new (mem_root) - Item_int(thd, *rows, MY_INT64_NUM_DECIMAL_DIGITS), - mem_root); + Item_string_sys(thd, rows_str.ptr(), + rows_str.length()), mem_root); } else item_list.push_back(item_null, mem_root); @@ -1147,12 +1149,12 @@ void Explain_table_access::fill_key_len_str(String *key_len_str) const key_len_str->append(buf, length); } - if (key.get_filter_key_length() != (uint)-1) + if (key.get_filter_len() != (uint)-1) { char buf[64]; size_t length; - key_len_str->append(','); - length= longlong10_to_str(key.get_filter_key_length(), buf, 10) - buf; + key_len_str->append('|'); + length= longlong10_to_str(key.get_filter_len(), buf, 10) - buf; key_len_str->append(buf, length); } } @@ -1177,6 +1179,20 @@ bool Explain_index_use::set(MEM_ROOT *mem_root, KEY *key, uint key_len_arg) return 0; } +bool Explain_index_use::set_filter(MEM_ROOT *mem_root, KEY *key, uint key_len_arg) +{ + if (!(filter_name= strdup_root(mem_root, key->name.str))) + return 1; + filter_len= key_len_arg; + uint len= 0; + for (uint i= 0; i < key->usable_key_parts; i++) + { + len += key->key_part[i].store_length; + if (len >= key_len_arg) + break; + } + return 0; +} bool Explain_index_use::set_pseudo_key(MEM_ROOT *root, const char* key_name_arg) { @@ -1240,7 +1256,16 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai } /* `type` column */ - push_str(thd, &item_list, join_type_str[type]); + StringBuffer<64> join_type_buf; + if (!is_filter_set()) + push_str(thd, &item_list, join_type_str[type]); + else + { + join_type_buf.append(join_type_str[type]); + join_type_buf.append("|filter"); + item_list.push_back(new (mem_root) + Item_string_sys(thd, join_type_buf.ptr(), join_type_buf.length()), mem_root); + } /* `possible_keys` column */ StringBuffer<64> possible_keys_buf; @@ -1252,6 +1277,11 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai /* `key` */ StringBuffer<64> key_str; fill_key_str(&key_str, false); + if (key.get_filter_name()) + { + key_str.append("|"); + key_str.append(key.get_filter_name()); + } if (key_str.length() > 0) push_string(thd, &item_list, &key_str); diff --git a/sql/sql_explain.h b/sql/sql_explain.h index 71f9047..fc4e346 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -583,7 +583,8 @@ class Explain_index_use : public Sql_alloc { char *key_name; uint key_len; - uint key_len_for_filter; + char *filter_name; + uint filter_len; public: String_list key_parts_list; @@ -596,16 +597,17 @@ class Explain_index_use : public Sql_alloc { key_name= NULL; key_len= (uint)-1; - key_len_for_filter= (uint)-1; + filter_name= NULL; + filter_len= (uint)-1; } bool set(MEM_ROOT *root, KEY *key_name, uint key_len_arg); bool set_pseudo_key(MEM_ROOT *root, const char *key_name); - void set_filter_key_length(uint key_length_arg) - { key_len_for_filter= key_length_arg; } + bool set_filter(MEM_ROOT *root, KEY *key, uint key_len_arg); inline const char *get_key_name() const { return key_name; } inline uint get_key_len() const { return key_len; } - inline uint get_filter_key_length() const { return key_len_for_filter; } + inline const char *get_filter_name() const { return filter_name; } + inline uint get_filter_len() const { return filter_len; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 8f55497..f23a6b4 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -101,6 +101,9 @@ static int sort_keyuse(KEYUSE *a,KEYUSE *b); static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, bool allow_full_scan, table_map used_tables); +static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select, + TABLE *table, + const key_map *keys,ha_rows limit); void best_access_path(JOIN *join, JOIN_TAB *s, table_map remaining_tables, uint idx, bool disable_jbuf, double record_count, @@ -1462,6 +1465,45 @@ int JOIN::optimize() } +bool +JOIN::make_range_filter_select(SQL_SELECT *select) +{ + DBUG_ENTER("make_range_filter_selects"); + + JOIN_TAB *tab; + + for (tab= first_linear_tab(this, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES); + tab; + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) + { + if (tab->filter) + { + int err; + SQL_SELECT *sel; + Item **sargable_cond= get_sargable_cond(this, tab->table); + sel= make_select(tab->table, const_table_map, const_table_map, + *sargable_cond, (SORT_INFO*) 0, 1, &err); + if (!sel) + DBUG_RETURN(1); + tab->filter->select= sel; + + key_map filter_map; + filter_map.clear_all(); + filter_map.set_bit(tab->filter->key_no); + bool force_index_save= tab->table->force_index; + tab->table->force_index= true; + (void) sel->test_quick_select(thd, filter_map, (table_map) 0, + (ha_rows) HA_POS_ERROR, + true, false, true); + tab->table->force_index= force_index_save; + if (thd->is_error()) + DBUG_RETURN(1); + DBUG_ASSERT(sel->quick); + } + } + DBUG_RETURN(0); +} + int JOIN::init_join_caches() { JOIN_TAB *tab; @@ -1980,6 +2022,9 @@ int JOIN::optimize_stage2() if (get_best_combination()) DBUG_RETURN(1); + if (make_range_filter_select(select)) + DBUG_RETURN(1); + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) DBUG_RETURN(1); @@ -7243,11 +7288,14 @@ best_access_path(JOIN *join, loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp); } /* not ft_key */ - filter= table->best_filter_for_current_join_order(start_key->key, + 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); + if (filter && (filter->get_filter_gain(record_count*records) < tmp)) + tmp= tmp - filter->get_filter_gain(record_count*records); + } if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE) { @@ -7412,11 +7460,14 @@ best_access_path(JOIN *join, else tmp+= s->startup_cost; - filter= s->table->best_filter_for_current_join_order(MAX_KEY, - 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); + 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 @@ -7435,7 +7486,9 @@ best_access_path(JOIN *join, best= tmp; records= best_records; best_key= 0; - best_filter= filter; + best_filter= 0; + if (s->quick && s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE) + best_filter= filter; /* range/index_merge/ALL/index access method are "independent", so: */ best_ref_depends_map= 0; best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & @@ -12478,6 +12531,16 @@ void JOIN_TAB::cleanup() select= 0; delete quick; quick= 0; + if (filter && filter->select) + { + if (filter->select->quick) + { + delete filter->select->quick; + filter->select->quick= 0; + } + delete filter->select; + filter->select= 0; + } if (cache) { cache->free(); @@ -24977,11 +25040,13 @@ int print_explain_message_line(select_result_sink *result, item_list.push_back(item_null, mem_root); /* `rows` */ + StringBuffer<64> rows_str; if (rows) { - item_list.push_back(new (mem_root) Item_int(thd, *rows, - MY_INT64_NUM_DECIMAL_DIGITS), - mem_root); + rows_str.append_ulonglong((ulonglong)(*rows)); + item_list.push_back(new (mem_root) + Item_string_sys(thd, rows_str.ptr(), + rows_str.length()), mem_root); } else item_list.push_back(item_null, mem_root); @@ -25055,16 +25120,6 @@ bool JOIN_TAB::save_filter_explain_data(Explain_table_access *eta) { if (!filter) return 0; - KEY *pk_key= get_keyinfo_by_key_no(filter->key_no); - StringBuffer<64> buff_for_pk; - const char *tmp_buff; - buff_for_pk.append("filter:"); - tmp_buff= pk_key->name.str; - buff_for_pk.append(tmp_buff, strlen(tmp_buff), system_charset_info); - if (!(eta->ref_list.append_str(join->thd->mem_root, - buff_for_pk.c_ptr_safe()))) - return 1; - eta->key.set_filter_key_length(pk_key->key_length); (filter->selectivity*100 >= 1) ? eta->filter_perc= round(filter->selectivity*100) : eta->filter_perc= 1; return 0; @@ -25207,6 +25262,14 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, // psergey-todo: ^ check for error return code /* Build "key", "key_len", and "ref" */ + + if (filter) + { + eta->key.set_filter(thd->mem_root, + &filter->table->key_info[filter->key_no], + filter->select->quick->max_used_key_length); + } + if (tab_type == JT_NEXT) { key_info= table->key_info+index; diff --git a/sql/sql_select.h b/sql/sql_select.h index 1d08b74..cb0b7c3 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1625,6 +1625,7 @@ class JOIN :public Sql_alloc bool optimize_unflattened_subqueries(); bool optimize_constant_subqueries(); int init_join_caches(); + bool make_range_filter_select(SQL_SELECT *select); bool make_sum_func_list(List<Item> &all_fields, List<Item> &send_fields, bool before_group_by, bool recompute= FALSE); diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index cbf3dad..90c8b50 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -7606,8 +7606,8 @@ static void test_explain_bug() verify_prepare_field(result, 7, "ref", "", MYSQL_TYPE_VAR_STRING, "", "", "", NAME_CHAR_LEN * HA_MAX_KEY_SEG, 0); - verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_LONGLONG, - "", "", "", 10, 0); + verify_prepare_field(result, 8, "rows", "", MYSQL_TYPE_VAR_STRING, + "", "", "", NAME_CHAR_LEN, 0); verify_prepare_field(result, 9, "Extra", "", MYSQL_TYPE_VAR_STRING, "", "", "", 255, 0);
participants (1)
-
IgorBabaev