revision-id: 2e352a4424a26c7a634278dec7fae93b30bd9855 (mariadb-10.3.6-105-g2e352a4) parent(s): 46dc54ef035dec65ac3e996e039d622aa451013a author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-03 12:22:13 -0800 message: MDEV-16188: Added more comments Added rowid_filter_innodb.test. --- mysql-test/main/rowid_filter.result | 248 +++-------- mysql-test/main/rowid_filter.test | 145 +----- mysql-test/main/rowid_filter_innodb.result | 690 +++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 7 + sql/rowid_filter.h | 65 +++ sql/table.h | 8 +- 6 files changed, 833 insertions(+), 330 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 8d224ee..9420d72 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -3,10 +3,56 @@ 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); +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables=preferably; 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 +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + PRIMARY KEY (`l_orderkey`,`l_linenumber`), + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`), + KEY `i_l_quantity` (`l_quantity`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table orders; +Table Create Table +orders CREATE TABLE `orders` ( + `o_orderkey` int(11) NOT NULL, + `o_custkey` int(11) DEFAULT NULL, + `o_orderstatus` char(1) DEFAULT NULL, + `o_totalprice` double DEFAULT NULL, + `o_orderDATE` date DEFAULT NULL, + `o_orderpriority` char(15) DEFAULT NULL, + `o_clerk` char(15) DEFAULT NULL, + `o_shippriority` int(11) DEFAULT NULL, + `o_comment` varchar(79) DEFAULT NULL, + PRIMARY KEY (`o_orderkey`), + KEY `i_o_orderdate` (`o_orderDATE`), + KEY `i_o_custkey` (`o_custkey`), + KEY `i_o_totalprice` (`o_totalprice`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 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 @@ -666,199 +712,5 @@ o_orderkey l_linenumber l_shipdate o_totalprice 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 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 -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 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 -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 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 -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_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, -# 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 73 (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 (0%) Using index condition; Using where; Using filter -1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 -# Same as above but EXPLAIN FORMAT=JSON: -EXPLAIN FORMAT=JSON 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'; -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "lineitem", - "access_type": "range", - "possible_keys": [ - "PRIMARY", - "i_l_shipdate", - "i_l_receiptdate", - "i_l_orderkey", - "i_l_orderkey_quantity" - ], - "key": "i_l_receiptdate", - "key_length": "4", - "used_key_parts": ["l_receiptDATE"], - "rowid_filter": { - "range": { - "key": "i_l_shipdate", - "used_key_parts": ["l_shipDATE"] - }, - "rows": 8, - "selectivity_pct": 0.1332 - }, - "rows": 6, - "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'" - }, - "table": { - "table_name": "orders", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["o_orderkey"], - "ref": ["dbt3_s001.lineitem.l_orderkey"], - "rows": 1, - "filtered": 100 - } - } -} DROP DATABASE dbt3_s001; +set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 274fab6..6e378a7 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -18,7 +18,18 @@ CREATE INDEX i_l_quantity ON lineitem(l_quantity); CREATE INDEX i_o_totalprice ON orders(o_totalprice); +set @save_use_stat_tables= @@use_stat_tables; + +set @@use_stat_tables=preferably; + +--disable_result_log +--disable_warnings ANALYZE TABLE lineitem, orders; +--enable_warnings +--enable_result_log + +show create table lineitem; +show create table orders; set optimizer_use_condition_selectivity=2; @@ -92,135 +103,7 @@ 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 -WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-02-01' AND - l_receiptdate BETWEEN '1997-01-10' AND '1997-01-25'; - ---echo # lineitem : {i_l_receiptdate, i_l_shipdate} -> i_l_receiptdate ---echo # 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'; - ---echo # lineitem : {i_l_receiptdate, i_l_shipdate, ---echo # 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'; - ---echo # lineitem : {i_l_receiptdate, i_l_shipdate, ---echo # 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'; - -CREATE INDEX i_l_extendedprice ON lineitem(l_extendedprice); - ---echo # lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate, ---echo # 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; - ---echo # 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; - ---echo # lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_extendedprice ---echo # 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; - ---echo # lineitem : {i_l_shipdate, i_l_extendedprice} -> i_l_shipdate ---echo # 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; - - -CREATE INDEX i_l_discount ON lineitem(l_discount); -CREATE INDEX i_l_tax ON lineitem(l_tax); - ---echo # lineitem : {i_l_receiptdate, i_l_shipdate, i_l_commitdate, ---echo # i_l_extendedprice, i_l_discount, i_l_tax} ---echo # -> {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; - -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; - ---echo # lineitem : {i_l_shipdate, i_l_receiptdate, i_l_commitdate} ---echo # -> i_l_shipdate ---echo # 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'; - -SET max_rowid_filter_size= DEFAULT; - ---echo # 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'; - ---echo # 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'; - ---echo # lineitem : {i_l_shipdate, i_l_commitdate, i_l_receiptdate} ---echo # -> 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'; - ---echo # lineitem : {i_l_shipdate, i_l_receiptdate} -> i_l_receiptdate ---echo # 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'; - ---echo # Same as above but EXPLAIN FORMAT=JSON: -EXPLAIN FORMAT=JSON 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'; - DROP DATABASE dbt3_s001; + +set @@use_stat_tables=@save_use_stat_tables; + diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result new file mode 100644 index 0000000..04414a7 --- /dev/null +++ b/mysql-test/main/rowid_filter_innodb.result @@ -0,0 +1,690 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +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); +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables=preferably; +ANALYZE TABLE lineitem, orders; +show create table lineitem; +Table Create Table +lineitem CREATE TABLE `lineitem` ( + `l_orderkey` int(11) NOT NULL DEFAULT 0, + `l_partkey` int(11) DEFAULT NULL, + `l_suppkey` int(11) DEFAULT NULL, + `l_linenumber` int(11) NOT NULL DEFAULT 0, + `l_quantity` double DEFAULT NULL, + `l_extendedprice` double DEFAULT NULL, + `l_discount` double DEFAULT NULL, + `l_tax` double DEFAULT NULL, + `l_returnflag` char(1) DEFAULT NULL, + `l_linestatus` char(1) DEFAULT NULL, + `l_shipDATE` date DEFAULT NULL, + `l_commitDATE` date DEFAULT NULL, + `l_receiptDATE` date DEFAULT NULL, + `l_shipinstruct` char(25) DEFAULT NULL, + `l_shipmode` char(10) DEFAULT NULL, + `l_comment` varchar(44) DEFAULT NULL, + PRIMARY KEY (`l_orderkey`,`l_linenumber`), + KEY `i_l_shipdate` (`l_shipDATE`), + KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), + KEY `i_l_partkey` (`l_partkey`), + KEY `i_l_suppkey` (`l_suppkey`), + KEY `i_l_receiptdate` (`l_receiptDATE`), + KEY `i_l_orderkey` (`l_orderkey`), + KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), + KEY `i_l_commitdate` (`l_commitDATE`), + KEY `i_l_quantity` (`l_quantity`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table orders; +Table Create Table +orders CREATE TABLE `orders` ( + `o_orderkey` int(11) NOT NULL, + `o_custkey` int(11) DEFAULT NULL, + `o_orderstatus` char(1) DEFAULT NULL, + `o_totalprice` double DEFAULT NULL, + `o_orderDATE` date DEFAULT NULL, + `o_orderpriority` char(15) DEFAULT NULL, + `o_clerk` char(15) DEFAULT NULL, + `o_shippriority` int(11) DEFAULT NULL, + `o_comment` varchar(79) DEFAULT NULL, + PRIMARY KEY (`o_orderkey`), + KEY `i_o_orderdate` (`o_orderDATE`), + KEY `i_o_custkey` (`o_custkey`), + KEY `i_o_totalprice` (`o_totalprice`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +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 510 (10%) 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": 605, + "selectivity_pct": 10.075 + }, + "rows": 510, + "filtered": 10.075, + "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 510 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": 510, + "filtered": 10.075, + "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 where; Using index +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=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, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", + "using_index": true + }, + "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": 4.7333, + "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 where; Using index +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, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", + "using_index": true + }, + "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": 4.7333, + "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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +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": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "rows": 144, + "filtered": 100, + "attached_condition": "orders.o_totalprice between 180000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "rows": 4, + "filtered": 0.8557, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" + } + } +} +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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_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, 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": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "rows": 144, + "filtered": 100, + "attached_condition": "orders.o_totalprice between 180000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "rows": 4, + "filtered": 0.8557, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" + } + } +} +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 71 Using where; Using index +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=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": 71, + "filtered": 100, + "attached_condition": "orders.o_totalprice between 200000 and 230000", + "using_index": true + }, + "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.4929, + "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 71 Using where; Using index +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": 71, + "filtered": 100, + "attached_condition": "orders.o_totalprice between 200000 and 230000", + "using_index": true + }, + "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.4929, + "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 +DROP DATABASE dbt3_s001; +set @@use_stat_tables=@save_use_stat_tables; +SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test new file mode 100644 index 0000000..5408f04 --- /dev/null +++ b/mysql-test/main/rowid_filter_innodb.test @@ -0,0 +1,7 @@ +--source include/have_innodb.inc + +SET SESSION STORAGE_ENGINE='InnoDB'; + +--source rowid_filter.test + +SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index a90201d..8991234 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -73,6 +73,71 @@ */ +/* + + How and when the optimizer builds and uses range rowid filters + -------------------------------------------------------------- + + 1. In make_join_statistics() + for each join table s + after the call of get_quick_record_count() + the TABLE::method init_cost_info_for_usable_range_rowid_filters() + is called + The method build an array of Range_rowid_filter_cost_info elements + containing the cost info on possible range filters for s->table. + The array is optimized for further usage. + + 2. For each partial join order when the optimizer considers joining + table s to this partial join + In the function best_access_path() + a. When evaluating a ref access r by index idx to join s + the optimizer estimates the effect of usage of each possible + range filter f and chooses one with the best gain. The gain + is taken into account when the cost of thr ref access r is + calculated. If it turns out that this is the best ref access + to join s then the info about the chosen filter together + with the info on r is remembered in the corresponding element + of the array of POSITION structures. + [We evaluate every pair (ref access, range_filter) rather then + every pair (best ref access, range filter) because if the index + ref_idx used for ref access r correlates with the index rf_idx + used by the filter f then the pair (r,f) is not evaluated + at all as we don't know how to estimate the effect of correlation + between ref_idx and rf_idx.] + b. When evaluating the best range access to join table s the + optimizer estimates the effect of usage of each possible + range filter f and chooses one with the best gain. + [Here we should have evaluated every pair (range access, + range filter) as well, but it's not done yet.] + + 3. When the cheapest execution plan has been chosen and after the + call of JOIN::get_best_combination() + The method JOIN::make_range_rowid_filters() is called + For each range rowid filter used in the chosen execution plan + the method creates a quick select object to be able to perform + index range scan to fill the filter at the execution stage. + The method also creates Range_rowid_filter objects that are + used at the execution stage. + + 4. Just before the execution stage + The method JOIN::init_range_rowid_filters() is called. + For each join table s that is to be accessed with usage of a range + filter the method allocates containers for the range filter and + it lets the engine know that the filter will be used when + accessing s. + + 5. At the execution stage + In the function sub_select() just before the first access of a join + table s employing a range filter + The method JOIN_TAB::build_range_rowid_filter_if_needed() is called + The method fills the filter using the quick select created by + JOIN::make_range_rowid_filters(). + + 6. The accessed key tuples are checked against the filter within the engine + using the info pushed into it. + +*/ + class TABLE; class SQL_SELECT; class Rowid_filter_container; diff --git a/sql/table.h b/sql/table.h index 9918758..1b8b837 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1504,14 +1504,20 @@ struct TABLE void add_splitting_info_for_key_field(struct KEY_FIELD *key_field); key_map with_impossible_ranges; + + /* Number of cost info elements for possible range filters */ uint range_rowid_filter_cost_info_elems; - Range_rowid_filter_cost_info **range_rowid_filter_cost_info_ptr; + /* Pointer to the array of cost info elements for range filters */ Range_rowid_filter_cost_info *range_rowid_filter_cost_info; + /* The array of pointers to cost info elements for range filters */ + Range_rowid_filter_cost_info **range_rowid_filter_cost_info_ptr; + void init_cost_info_for_usable_range_rowid_filters(THD *thd); void prune_range_rowid_filters(); Range_rowid_filter_cost_info * best_range_rowid_filter_for_partial_join(uint access_key_no, double records); + /** System Versioning support */