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=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 l_quantity > 45; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": ["i_l_shipdate", "i_l_quantity"], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rowid_filter": { "range": { "key": "i_l_quantity", "used_key_parts": ["l_quantity"] }, "rows": 662, "selectivity_pct": 11.024 }, "rows": 509, "filtered": 11.024, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) 60.00 (3%) 11.02 100.00 Using index condition; Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": ["i_l_shipdate", "i_l_quantity"], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rowid_filter": { "range": { "key": "i_l_quantity", "used_key_parts": ["l_quantity"] }, "rows": 662, "selectivity_pct": 11.024, "r_rows": 605, "r_selectivity_pct": 3.6855, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 509, "r_rows": 60, "r_total_time_ms": "REPLACED", "filtered": 11.024, "r_filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" } } } set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; l_orderkey l_linenumber l_shipdate l_quantity 1121 5 1997-04-27 47 1121 6 1997-04-21 50 1441 7 1997-06-07 50 1443 1 1997-02-05 47 1473 1 1997-05-05 50 1568 2 1997-04-06 46 1632 1 1997-01-25 47 1632 3 1997-01-29 47 1954 7 1997-06-04 49 1959 1 1997-05-05 46 2151 3 1997-01-20 49 2177 5 1997-05-10 46 2369 2 1997-01-02 47 2469 3 1997-01-11 48 2469 6 1997-03-03 49 2470 2 1997-06-02 50 260 1 1997-03-24 50 288 2 1997-04-19 49 289 4 1997-03-14 48 3009 1 1997-03-19 48 3105 3 1997-02-28 48 3106 2 1997-02-27 49 3429 1 1997-04-08 48 3490 2 1997-06-27 50 3619 1 1997-01-22 49 3619 3 1997-01-31 46 3969 3 1997-05-29 46 4005 4 1997-01-31 49 4036 1 1997-06-21 46 4066 4 1997-02-17 49 4098 1 1997-01-26 46 422 3 1997-06-21 46 4258 3 1997-01-02 46 4421 2 1997-04-21 46 4421 3 1997-05-25 46 4453 3 1997-05-29 48 4484 7 1997-03-17 50 4609 3 1997-02-11 46 484 1 1997-03-06 49 484 3 1997-01-24 50 484 5 1997-03-05 48 485 1 1997-03-28 50 4868 1 1997-04-29 47 4868 3 1997-04-23 49 4934 1 1997-05-20 48 4967 1 1997-05-27 50 5090 2 1997-04-05 46 5152 2 1997-03-10 50 5158 4 1997-04-10 49 5606 3 1997-03-11 46 5606 7 1997-02-01 46 5762 4 1997-03-02 47 581 3 1997-02-27 49 5829 5 1997-01-31 49 5831 4 1997-02-24 46 5895 2 1997-04-27 47 5895 3 1997-03-15 49 5952 1 1997-06-30 49 705 1 1997-04-18 46 836 3 1997-03-21 46 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 509 Using index condition; Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": ["i_l_shipdate", "i_l_quantity"], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rows": 509, "filtered": 11.024, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 509 510.00 11.02 11.76 Using index condition; Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 509, "r_rows": 510, "r_total_time_ms": "REPLACED", "filtered": 11.024, "r_filtered": 11.765, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" } } } set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; l_orderkey l_linenumber l_shipdate l_quantity 1121 5 1997-04-27 47 1121 6 1997-04-21 50 1441 7 1997-06-07 50 1443 1 1997-02-05 47 1473 1 1997-05-05 50 1568 2 1997-04-06 46 1632 1 1997-01-25 47 1632 3 1997-01-29 47 1954 7 1997-06-04 49 1959 1 1997-05-05 46 2151 3 1997-01-20 49 2177 5 1997-05-10 46 2369 2 1997-01-02 47 2469 3 1997-01-11 48 2469 6 1997-03-03 49 2470 2 1997-06-02 50 260 1 1997-03-24 50 288 2 1997-04-19 49 289 4 1997-03-14 48 3009 1 1997-03-19 48 3105 3 1997-02-28 48 3106 2 1997-02-27 49 3429 1 1997-04-08 48 3490 2 1997-06-27 50 3619 1 1997-01-22 49 3619 3 1997-01-31 46 3969 3 1997-05-29 46 4005 4 1997-01-31 49 4036 1 1997-06-21 46 4066 4 1997-02-17 49 4098 1 1997-01-26 46 422 3 1997-06-21 46 4258 3 1997-01-02 46 4421 2 1997-04-21 46 4421 3 1997-05-25 46 4453 3 1997-05-29 48 4484 7 1997-03-17 50 4609 3 1997-02-11 46 484 1 1997-03-06 49 484 3 1997-01-24 50 484 5 1997-03-05 48 485 1 1997-03-28 50 4868 1 1997-04-29 47 4868 3 1997-04-23 49 4934 1 1997-05-20 48 4967 1 1997-05-27 50 5090 2 1997-04-05 46 5152 2 1997-03-10 50 5158 4 1997-04-10 49 5606 3 1997-03-11 46 5606 7 1997-02-01 46 5762 4 1997-03-02 47 581 3 1997-02-27 49 5829 5 1997-01-31 49 5831 4 1997-02-24 46 5895 2 1997-04-27 47 5895 3 1997-03-15 49 5952 1 1997-06-30 49 705 1 1997-04-18 46 836 3 1997-03-21 46 set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rows": 98, "filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rowid_filter": { "range": { "key": "i_o_totalprice", "used_key_parts": ["o_totalprice"] }, "rows": 81, "selectivity_pct": 5.4 }, "rows": 1, "filtered": 5.4, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 5.40 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 98, "r_rows": 98, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rowid_filter": { "range": { "key": "i_o_totalprice", "used_key_parts": ["o_totalprice"] }, "rows": 81, "selectivity_pct": 5.4, "r_rows": 71, "r_selectivity_pct": 10.417, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 98, "rows": 1, "r_rows": 0.1122, "r_total_time_ms": "REPLACED", "filtered": 5.4, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 6 1997-01-25 222274.54 484 3 1997-01-24 219920.62 5606 6 1997-01-11 219959.08 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rows": 98, "filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rows": 1, "filtered": 5.4, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.40 11.22 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 98, "r_rows": 98, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "r_loops": 98, "rows": 1, "r_rows": 1, "r_total_time_ms": "REPLACED", "filtered": 5.4, "r_filtered": 11.224, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 6 1997-01-25 222274.54 484 3 1997-01-24 219920.62 5606 6 1997-01-11 219959.08 set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using rowid filter 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rowid_filter": { "range": { "key": "i_l_quantity", "used_key_parts": ["l_quantity"] }, "rows": 662, "selectivity_pct": 11.024 }, "rows": 509, "filtered": 11.024, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rowid_filter": { "range": { "key": "i_o_totalprice", "used_key_parts": ["o_totalprice"] }, "rows": 152, "selectivity_pct": 10.133 }, "rows": 1, "filtered": 10.133, "attached_condition": "orders.o_totalprice between 180000 and 230000" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) 60.00 (3%) 11.02 100.00 Using index condition; Using where; Using rowid filter 1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) 0.27 (25%) 10.13 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rowid_filter": { "range": { "key": "i_l_quantity", "used_key_parts": ["l_quantity"] }, "rows": 662, "selectivity_pct": 11.024, "r_rows": 605, "r_selectivity_pct": 3.6855, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 509, "r_rows": 60, "r_total_time_ms": "REPLACED", "filtered": 11.024, "r_filtered": 100, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rowid_filter": { "range": { "key": "i_o_totalprice", "used_key_parts": ["o_totalprice"] }, "rows": 152, "selectivity_pct": 10.133, "r_rows": 144, "r_selectivity_pct": 25.424, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 60, "rows": 1, "r_rows": 0.2667, "r_total_time_ms": "REPLACED", "filtered": 10.133, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000" } } } set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 1632 1 1997-01-25 47 183286.33 1632 3 1997-01-29 47 183286.33 2177 5 1997-05-10 46 183493.42 2469 3 1997-01-11 48 192074.23 2469 6 1997-03-03 49 192074.23 3619 1 1997-01-22 49 222274.54 3619 3 1997-01-31 46 222274.54 484 1 1997-03-06 49 219920.62 484 3 1997-01-24 50 219920.62 484 5 1997-03-05 48 219920.62 4934 1 1997-05-20 48 180478.16 5606 3 1997-03-11 46 219959.08 5606 7 1997-02-01 46 219959.08 5829 5 1997-01-31 49 183734.56 5895 2 1997-04-27 47 201419.83 5895 3 1997-03-15 49 201419.83 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate 4 NULL 509 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "rows": 509, "filtered": 11.024, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "rows": 1, "filtered": 10.133, "attached_condition": "orders.o_totalprice between 180000 and 230000" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate 4 NULL 509 510.00 11.02 11.76 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 10.13 26.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "lineitem", "access_type": "range", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "i_l_shipdate", "key_length": "4", "used_key_parts": ["l_shipDATE"], "r_loops": 1, "rows": 509, "r_rows": 510, "r_total_time_ms": "REPLACED", "filtered": 11.024, "r_filtered": 11.765, "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", "attached_condition": "lineitem.l_quantity > 45" }, "table": { "table_name": "orders", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["o_orderkey"], "ref": ["dbt3_s001.lineitem.l_orderkey"], "r_loops": 60, "rows": 1, "r_rows": 1, "r_total_time_ms": "REPLACED", "filtered": 10.133, "r_filtered": 26.667, "attached_condition": "orders.o_totalprice between 180000 and 230000" } } } set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 1632 1 1997-01-25 47 183286.33 1632 3 1997-01-29 47 183286.33 2177 5 1997-05-10 46 183493.42 2469 3 1997-01-11 48 192074.23 2469 6 1997-03-03 49 192074.23 3619 1 1997-01-22 49 222274.54 3619 3 1997-01-31 46 222274.54 484 1 1997-03-06 49 219920.62 484 3 1997-01-24 50 219920.62 484 5 1997-03-05 48 219920.62 4934 1 1997-05-20 48 180478.16 5606 3 1997-03-11 46 219959.08 5606 7 1997-02-01 46 219959.08 5829 5 1997-01-31 49 183734.56 5895 2 1997-04-27 47 201419.83 5895 3 1997-03-15 49 201419.83 set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 Using index condition 1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "i_o_totalprice", "key_length": "9", "used_key_parts": ["o_totalprice"], "rows": 81, "filtered": 100, "index_condition": "orders.o_totalprice between 200000 and 230000" }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rowid_filter": { "range": { "key": "i_l_shipdate", "used_key_parts": ["l_shipDATE"] }, "rows": 509, "selectivity_pct": 8.4763 }, "rows": 4, "filtered": 8.4763, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 71.00 100.00 100.00 Using index condition 1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 81, "r_rows": 71, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "index_condition": "orders.o_totalprice between 200000 and 230000" }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rowid_filter": { "range": { "key": "i_l_shipdate", "used_key_parts": ["l_shipDATE"] }, "rows": 509, "selectivity_pct": 8.4763, "r_rows": 510, "r_selectivity_pct": 7.7731, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 71, "rows": 4, "r_rows": 0.5211, "r_total_time_ms": "REPLACED", "filtered": 8.4763, "r_filtered": 100, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" } } } set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 1890 1 1997-04-02 202364.58 1890 3 1997-02-09 202364.58 1890 4 1997-04-08 202364.58 1890 5 1997-04-15 202364.58 1890 6 1997-02-13 202364.58 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 4 1997-03-18 222274.54 3619 6 1997-01-25 222274.54 453 1 1997-06-30 216826.73 453 2 1997-06-30 216826.73 484 1 1997-03-06 219920.62 484 2 1997-04-09 219920.62 484 3 1997-01-24 219920.62 484 4 1997-04-29 219920.62 484 5 1997-03-05 219920.62 484 6 1997-04-06 219920.62 5606 2 1997-02-23 219959.08 5606 3 1997-03-11 219959.08 5606 4 1997-02-06 219959.08 5606 6 1997-01-11 219959.08 5606 7 1997-02-01 219959.08 5859 2 1997-05-15 210643.96 5859 5 1997-05-28 210643.96 5859 6 1997-06-15 210643.96 5895 1 1997-04-05 201419.83 5895 2 1997-04-27 201419.83 5895 3 1997-03-15 201419.83 5895 4 1997-03-03 201419.83 5895 5 1997-04-30 201419.83 5895 6 1997-04-19 201419.83 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 Using index condition 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice"], "key": "i_o_totalprice", "key_length": "9", "used_key_parts": ["o_totalprice"], "rows": 81, "filtered": 100, "index_condition": "orders.o_totalprice between 200000 and 230000" }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 8.4763, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE 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 r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 71.00 100.00 100.00 Using index condition 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE 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; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 81, "r_rows": 71, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "index_condition": "orders.o_totalprice between 200000 and 230000" }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 71, "rows": 4, "r_rows": 6.7042, "r_total_time_ms": "REPLACED", "filtered": 8.4763, "r_filtered": 7.7731, "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 # # MDEV-18413: find constraint correlated indexes # ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate); # Filter on l_shipdate is not used because it participates in # the same constraint as l_receiptdate. # Access is made on l_receiptdate. set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; 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_receiptdate 4 NULL 18 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; 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"], "rows": 18, "filtered": 0.5662, "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" }, "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": 8.7333, "attached_condition": "orders.o_totalprice between 200000 and 250000" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 18, "r_rows": 18, "r_total_time_ms": "REPLACED", "filtered": 0.5662, "r_filtered": 38.889, "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" }, "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"], "r_loops": 7, "rows": 1, "r_rows": 1, "r_total_time_ms": "REPLACED", "filtered": 8.7333, "r_filtered": 14.286, "attached_condition": "orders.o_totalprice between 200000 and 250000" } } } set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; l_shipdate l_receiptdate o_totalprice 1996-10-07 1996-10-08 202623.92 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; 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_receiptdate 4 NULL 18 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; 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"], "rows": 18, "filtered": 0.5662, "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" }, "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": 8.7333, "attached_condition": "orders.o_totalprice between 200000 and 250000" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "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"], "r_loops": 1, "rows": 18, "r_rows": 18, "r_total_time_ms": "REPLACED", "filtered": 0.5662, "r_filtered": 38.889, "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" }, "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"], "r_loops": 7, "rows": 1, "r_rows": 1, "r_total_time_ms": "REPLACED", "filtered": 8.7333, "r_filtered": 14.286, "attached_condition": "orders.o_totalprice between 200000 and 250000" } } } set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice FROM orders, lineitem WHERE o_orderkey=l_orderkey AND l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND o_totalprice BETWEEN 200000 AND 250000; l_shipdate l_receiptdate o_totalprice 1996-10-07 1996-10-08 202623.92 ALTER TABLE orders ADD COLUMN o_totaldiscount double; UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000); CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount); ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount); # Filter on o_totalprice is not used because it participates in # the same constraint as o_discount. # Access is made on o_discount. set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; 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 set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "rows": 39, "filtered": 3.2667, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000" }, "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": 3.0475, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; 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 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "r_loops": 1, "rows": 39, "r_rows": 41, "r_total_time_ms": "REPLACED", "filtered": 3.2667, "r_filtered": 2.439, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000" }, "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"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_total_time_ms": "REPLACED", "filtered": 3.0475, "r_filtered": 66.667, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; o_totaldiscount o_totalprice l_shipdate 18016.04288 219707.84 1996-10-02 18016.04288 219707.84 1996-10-17 18016.04288 219707.84 1996-11-04 18016.04288 219707.84 1996-11-14 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; 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 set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "rows": 39, "filtered": 3.2667, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000" }, "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": 3.0475, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; 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 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "orders", "access_type": "range", "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "r_loops": 1, "rows": 39, "r_rows": 41, "r_total_time_ms": "REPLACED", "filtered": 3.2667, "r_filtered": 2.439, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000" }, "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"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_total_time_ms": "REPLACED", "filtered": 3.0475, "r_filtered": 66.667, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate FROM orders, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; o_totaldiscount o_totalprice l_shipdate 18016.04288 219707.84 1996-10-02 18016.04288 219707.84 1996-10-17 18016.04288 219707.84 1996-11-04 18016.04288 219707.84 1996-11-14 CREATE VIEW v1 AS SELECT * FROM orders WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01'; set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; 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 set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": [ "PRIMARY", "i_o_orderdate", "i_o_totalprice", "i_o_totaldiscount" ], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "rows": 39, "filtered": 1.9905, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" }, "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": 3.0475, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; 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 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "orders", "access_type": "range", "possible_keys": [ "PRIMARY", "i_o_orderdate", "i_o_totalprice", "i_o_totaldiscount" ], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "r_loops": 1, "rows": 39, "r_rows": 41, "r_total_time_ms": "REPLACED", "filtered": 1.9905, "r_filtered": 2.439, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" }, "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"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_total_time_ms": "REPLACED", "filtered": 3.0475, "r_filtered": 66.667, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; o_totaldiscount o_totalprice l_shipdate 18016.04288 219707.84 1996-10-02 18016.04288 219707.84 1996-10-17 18016.04288 219707.84 1996-11-04 18016.04288 219707.84 1996-11-14 set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; 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 set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "range", "possible_keys": [ "PRIMARY", "i_o_orderdate", "i_o_totalprice", "i_o_totaldiscount" ], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "rows": 39, "filtered": 1.9905, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" }, "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": 3.0475, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; 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 6.00 3.05 66.67 Using where set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { "table_name": "orders", "access_type": "range", "possible_keys": [ "PRIMARY", "i_o_orderdate", "i_o_totalprice", "i_o_totaldiscount" ], "key": "i_o_totaldiscount", "key_length": "9", "used_key_parts": ["o_totaldiscount"], "r_loops": 1, "rows": 39, "r_rows": 41, "r_total_time_ms": "REPLACED", "filtered": 1.9905, "r_filtered": 2.439, "index_condition": "orders.o_totaldiscount between 18000 and 20000", "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" }, "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"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_total_time_ms": "REPLACED", "filtered": 3.0475, "r_filtered": 66.667, "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" } } } set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate FROM v1, lineitem WHERE o_orderkey=l_orderkey AND o_totaldiscount BETWEEN 18000 AND 20000 AND o_totalprice BETWEEN 200000 AND 220000 AND l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; o_totaldiscount o_totalprice l_shipdate 18016.04288 219707.84 1996-10-02 18016.04288 219707.84 1996-10-17 18016.04288 219707.84 1996-11-04 18016.04288 219707.84 1996-11-14 ALTER TABLE lineitem DROP CONSTRAINT l_date; ALTER TABLE orders DROP CONSTRAINT o_price; ALTER TABLE orders DROP COLUMN o_totaldiscount; DROP VIEW v1; DROP DATABASE dbt3_s001; use test; # # MDEV-18816: potential range filter for one join table with # impossible WHERE for another # create table t1 ( pk int not null primary key, c2 varchar(10) , i1 int,key (c2) ) engine=myisam; insert into t1 values (1,'a',-5),(2,'a',null); create table t2 ( pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) ) engine=myisam; insert into t2 values (1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), (7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), (13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); select 1 from t1 left join t2 join t1 as t1_a on t2.i1 = t1_a.pk on t1.c2 = t2.c1 where t1_a.pk is null and t1_a.i1 != 3; 1 explain extended select 1 from t1 left join t2 join t1 as t1_a on t2.i1 = t1_a.pk on t1.c2 = t2.c1 where t1_a.pk is null and t1_a.i1 != 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 drop table t1,t2; # # MDEV-18640: TABLE::prune_range_rowid_filters: Conditional jump or # move depends on uninitialized value # CREATE TABLE t1 ( pk INT, i INT, PRIMARY KEY (pk), KEY (pk,i) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,10), (7,70), (2,20); SELECT * FROM t1 WHERE pk < 5; pk i 1 10 2 20 DROP TABLE t1; # # MDEV-18956: Possible rowid filter for subquery for which # in_to_exists strategy has been chosen # CREATE TABLE t1 (pk int) engine=myisam ; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 ( pk int auto_increment PRIMARY KEY, i1 int, i2 int, c2 varchar(1), KEY (i1), KEY (i2) ) engine=myisam; INSERT INTO t2 VALUES (1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'), (6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'), (11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u'); SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); pk EXPLAIN EXTENDED SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; # # MDEV-19255: rowid range filter built for range condition # that uses in expensive subquery # CREATE TABLE t1 ( pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), (17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), (24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), (31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), (38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), (45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), (52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), (59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), (66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), (73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), (80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), (87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), (94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), (101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), (107,8,'z'),(108,3,'k'),(109,65,NULL); CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,1,'x'); INSERT INTO t2 SELECT * FROM t1; SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); pk1 a1 b1 pk2 a2 b2 65 2 a 109 65 NULL EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where 1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "t2", "access_type": "ALL", "rows": 101, "filtered": 100, "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "b1"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk1"], "ref": ["test.t2.a2"], "rowid_filter": { "range": { "key": "b1", "used_key_parts": ["b1"] }, "rows": 87, "selectivity_pct": 87 }, "rows": 1, "filtered": 87, "attached_condition": "t1.b1 <= (subquery#2)" }, "subqueries": [ { "query_block": { "select_id": 2, "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk2"], "rows": 1, "filtered": 100, "index_condition": "t2.pk2 <= 1" } } } ] } } DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables;