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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci set optimizer_use_condition_selectivity=2; select 100 * (select count(*) from lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 ) / (select count(*) from lineitem where l_shipdate BETWEEN '1997-01-01' AND '1997-06-30') as correct_r_filtered_when_using_l_shipdate; correct_r_filtered_when_using_l_shipdate 11.7647 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 (12%) 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, "nested_loop": [ { "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": 702, "selectivity_pct": 11.69025812 }, "rows": 509, "filtered": 11.69025803, "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 (12%) 60.00 (11%) 11.69 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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": 702, "selectivity_pct": 11.69025812, "r_rows": 605, "r_lookups": 510, "r_selectivity_pct": 11.76470588, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 509, "r_rows": 60, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 11.69025803, "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, "nested_loop": [ { "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.69025803, "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.69 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 11.69025803, "r_filtered": 11.76470588, "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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition 1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) 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, "nested_loop": [ { "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": 69, "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": "i_l_orderkey", "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": 98, "selectivity_pct": 1.631973356 }, "rows": 4, "filtered": 1.631973386, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" } } ] } } 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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 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 i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) 0.15 (2%) 1.63 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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": 69, "r_rows": 71, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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": "i_l_orderkey", "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": 98, "selectivity_pct": 1.631973356, "r_rows": 98, "r_lookups": 476, "r_selectivity_pct": 2.31092437, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 71, "rows": 4, "r_rows": 0.154929577, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 1.631973386, "r_filtered": 100, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" } } ] } } 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, "nested_loop": [ { "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": 4.599999905, "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 4.60 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 4.599999905, "r_filtered": 11.2244898, "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 (12%) 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 (9%) 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, "nested_loop": [ { "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": 702, "selectivity_pct": 11.69025812 }, "rows": 509, "filtered": 11.69025803, "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": 139, "selectivity_pct": 9.266666667 }, "rows": 1, "filtered": 9.266666412, "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 (12%) 60.00 (11%) 11.69 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 (9%) 0.27 (25%) 9.27 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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": 702, "selectivity_pct": 11.69025812, "r_rows": 605, "r_lookups": 510, "r_selectivity_pct": 11.76470588, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 509, "r_rows": 60, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 11.69025803, "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": 139, "selectivity_pct": 9.266666667, "r_rows": 144, "r_lookups": 59, "r_selectivity_pct": 25.42372881, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 60, "rows": 1, "r_rows": 0.266666667, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 9.266666412, "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, "nested_loop": [ { "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.69025803, "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": 9.266666412, "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.69 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 9.27 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 11.69025803, "r_filtered": 11.76470588, "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 9.266666412, "r_filtered": 26.66666667, "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 69 Using index condition 1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|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, "nested_loop": [ { "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": 69, "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": "i_l_orderkey", "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.476269775 }, "rows": 4, "filtered": 8.476269722, "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 69 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 i_l_orderkey|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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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": 69, "r_rows": 71, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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": "i_l_orderkey", "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.476269775, "r_rows": 510, "r_lookups": 476, "r_selectivity_pct": 7.773109244, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 71, "rows": 4, "r_rows": 0.521126761, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 8.476269722, "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 69 Using index condition 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 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, "nested_loop": [ { "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": 69, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 8.476269722, "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 69 71.00 100.00 100.00 Using index condition 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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": 69, "r_rows": 71, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": 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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 71, "rows": 4, "r_rows": 6.704225352, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 8.476269722, "r_filtered": 7.773109244, "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, "nested_loop": [ { "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.566194832, "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": 7.466666698, "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 7.47 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 0.566194832, "r_filtered": 38.88888889, "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 7.466666698, "r_filtered": 14.28571429, "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, "nested_loop": [ { "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.566194832, "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": 7.466666698, "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 7.47 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 0.566194832, "r_filtered": 38.88888889, "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 7.466666698, "r_filtered": 14.28571429, "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 i_l_orderkey 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, "nested_loop": [ { "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.200000048, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 3.047460556, "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.20 2.44 Using index condition; Using where 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 3.200000048, "r_filtered": 2.43902439, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 3.047460556, "r_filtered": 66.66666667, "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 i_l_orderkey 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, "nested_loop": [ { "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.200000048, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 3.047460556, "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.20 2.44 Using index condition; Using where 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 3.200000048, "r_filtered": 2.43902439, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 3.047460556, "r_filtered": 66.66666667, "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 i_l_orderkey 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, "nested_loop": [ { "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": "REPLACED", "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": "REPLACED", "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 # 2.44 Using index condition; Using where 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 # 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": {}, "filtered": "REPLACED", "r_filtered": 2.43902439, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": {}, "filtered": "REPLACED", "r_filtered": 66.66666667, "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 i_l_orderkey 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, "nested_loop": [ { "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": "REPLACED", "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": "REPLACED", "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 # 2.44 Using index condition; Using where 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey 4 dbt3_s001.orders.o_orderkey 4 6.00 # 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_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "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_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": {}, "filtered": "REPLACED", "r_filtered": 2.43902439, "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": "i_l_orderkey", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 1, "rows": 4, "r_rows": 6, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": {}, "filtered": "REPLACED", "r_filtered": 66.66666667, "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 t2 ref i1,i2 i1 5 const 1 100.00 Using index condition; Using where 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(a1), 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,'i'); INSERT INTO t2 SELECT * FROM t1; INSERT INTO t1 SELECT pk1+200, a1, b1 FROM t1; INSERT INTO t1 SELECT pk1+400, a1, b1 FROM t1; ANALYZE TABLE t1,t2 PERSISTENT FOR ALL; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); pk1 a1 b1 pk2 a2 b2 17 1 f 16 1 j 37 3 g 36 3 a 105 8 i 104 8 e EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = 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 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where 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`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2 EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); EXPLAIN { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "ALL", "rows": 101, "filtered": 100, "attached_condition": "t2.a2 is not null" } }, { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["a1", "b1"], "key": "a1", "key_length": "5", "used_key_parts": ["a1"], "ref": ["test.t2.a2"], "rows": 36, "filtered": 28.75, "attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2" } } ], "subqueries": [ { "query_block": { "select_id": 2, "nested_loop": [ { "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; # # MDEV-21794: Optimizer flag rowid_filter leads to long query # create table t10(a int); insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t11(a int); insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; CREATE TABLE t1 ( el_id int(10) unsigned NOT NULL , el_index blob NOT NULL, el_index_60 varbinary(60) NOT NULL, filler blob, PRIMARY KEY (el_id), KEY el_index (el_index(60)), KEY el_index_60 (el_index_60,el_id) ); insert into t1 select A.a+1000*B.a, A.a+1000*B.a + 10000, A.a+1000*B.a + 10000, 'filler-data-filler-data' from t11 A, t10 B; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze Warning Engine-independent statistics are not collected for column 'el_index' test.t1 analyze Warning Engine-independent statistics are not collected for column 'filler' test.t1 analyze status Table is already up to date # This must not use rowid_filter with key=el_index|el_index_60: explain select * from t1 where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range el_index,el_index_60 el_index 62 NULL 645 Using where drop table t10, t11, t1; # # MDEV-22160: SIGSEGV in st_join_table::save_explain_data on SELECT # set @save_optimizer_switch= @@optimizer_switch; SET @@optimizer_switch="index_merge_sort_union=OFF"; CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b)); INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4); INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT * FROM t1; ANALYZE table t1 PERSISTENT FOR ALL; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain SELECT * FROM t1 WHERE a > 0 AND b=0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range|filter a,b a|b 5|5 NULL 77 (34%) Using index condition; Using where; Using rowid filter SELECT * FROM t1 WHERE a > 0 AND b=0; a b 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 1 0 drop table t1; SET @@optimizer_switch=@save_optimizer_switch; # # MDEV-28846: Poor performance when rowid filter contains no elements # create table t1 ( pk int primary key auto_increment, nm varchar(32), fl1 tinyint default 0, fl2 tinyint default 0, index idx1(nm, fl1), index idx2(fl2) ) engine=myisam; create table name ( pk int primary key auto_increment, nm bigint ) engine=myisam; create table flag2 ( pk int primary key auto_increment, fl2 tinyint ) engine=myisam; insert into name(nm) select seq from seq_1_to_1000 order by rand(17); insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date select '500%' as a; a 500% set optimizer_switch='rowid_filter=on'; explain select * from t1 where nm like '500%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where analyze format=json select * from t1 where nm like '500%' AND fl2 = 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["idx1", "idx2"], "key": "idx1", "key_length": "35", "used_key_parts": ["nm"], "r_loops": 1, "rows": 1, "r_rows": 1, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 49.20000076, "r_filtered": 100, "index_condition": "t1.nm like '500%'", "attached_condition": "t1.fl2 = 0" } } ] } } select * from t1 where nm like '500%' AND fl2 = 0; pk nm fl1 fl2 517 500 0 0 truncate table name; truncate table flag2; truncate table t1; insert into name(nm) select seq from seq_1_to_1000 order by rand(17); insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date set optimizer_switch='rowid_filter=off'; explain select * from t1 where nm like '500%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where analyze format=json select * from t1 where nm like '500%' AND fl2 = 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "range", "possible_keys": ["idx1", "idx2"], "key": "idx1", "key_length": "35", "used_key_parts": ["nm"], "r_loops": 1, "rows": 1, "r_rows": 1, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 49.20000076, "r_filtered": 100, "index_condition": "t1.nm like '500%'", "attached_condition": "t1.fl2 = 0" } } ] } } select * from t1 where nm like '500%' AND fl2 = 0; pk nm fl1 fl2 517 500 0 0 truncate table name; truncate table flag2; truncate table t1; insert into name(nm) select seq from seq_1_to_1000 order by rand(17); insert into flag2(fl2) select seq mod 10 from seq_1_to_1000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date select '607%' as a; a 607% set optimizer_switch='rowid_filter=on'; explain select * from t1 where nm like '607%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where select * from t1 where nm like '607%' AND fl2 = 0; pk nm fl1 fl2 721 607 0 0 truncate table name; truncate table flag2; truncate table t1; insert into name(nm) select seq from seq_1_to_10000 order by rand(17); insert into flag2(fl2) select seq mod 100 from seq_1_to_10000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date select '75%' as a; a 75% set optimizer_switch='rowid_filter=on'; explain select * from t1 where nm like '75%' AND fl2 = 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref|filter idx1,idx2 idx2|idx1 2|35 const 55 (1%) Using where; Using rowid filter analyze format=json select * from t1 where nm like '75%' AND fl2 = 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx1", "idx2"], "key": "idx2", "key_length": "2", "used_key_parts": ["fl2"], "ref": ["const"], "rowid_filter": { "range": { "key": "idx1", "used_key_parts": ["nm"] }, "rows": 115, "selectivity_pct": 1.15, "r_rows": 111, "r_lookups": 100, "r_selectivity_pct": 2, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 55, "r_rows": 2, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 1.149999976, "r_filtered": 100, "attached_condition": "t1.nm like '75%'" } } ] } } select * from t1 where nm like '75%' AND fl2 = 0; pk nm fl1 fl2 4543 7503 0 0 7373 7518 0 0 drop table name, flag2; drop table t1; create table t1 ( pk int primary key auto_increment, nm char(255), fl1 tinyint default 0, fl2 int default 0, index idx1(nm, fl1), index idx2(fl2) ) engine=myisam; create table name ( pk int primary key auto_increment, nm bigint ) engine=myisam; create table flag2 ( pk int primary key auto_increment, fl2 int ) engine=myisam; insert into name(nm) select seq from seq_1_to_10000 order by rand(17); insert into flag2(fl2) select seq mod 10 from seq_1_to_10000 order by rand(19); insert into t1(nm,fl2) select nm, fl2 from name, flag2 where name.pk = flag2.pk; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date select * from t1 where ( nm like '3400%' or nm like '3402%' or nm like '3403%' or nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or nm like '3409%' or nm like '3411%' or nm like '3412%' or nm like '3413%' or nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or nm like '3418%' or nm like '3419%' or nm like '3421%' or nm like '3422%' or nm like '3423%' or nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or nm like '3428%' or nm like '3429%' or nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or nm like '3439%' or nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or nm like '3448%' ) and fl2 = 0; pk nm fl1 fl2 analyze format=json select * from t1 where ( nm like '3400%' or nm like '3402%' or nm like '3403%' or nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or nm like '3409%' or nm like '3411%' or nm like '3412%' or nm like '3413%' or nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or nm like '3418%' or nm like '3419%' or nm like '3421%' or nm like '3422%' or nm like '3423%' or nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or nm like '3428%' or nm like '3429%' or nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or nm like '3439%' or nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or nm like '3448%' ) and fl2 = 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx1", "idx2"], "key": "idx2", "key_length": "5", "used_key_parts": ["fl2"], "ref": ["const"], "rowid_filter": { "range": { "key": "idx1", "used_key_parts": ["nm"] }, "rows": 44, "selectivity_pct": 0.44, "r_rows": 44, "r_lookups": 1000, "r_selectivity_pct": 0, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 863, "r_rows": 0, "r_table_time_ms": "REPLACED", "r_other_time_ms": "REPLACED", "r_engine_stats": REPLACED, "filtered": 0.439999998, "r_filtered": 100, "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } ] } } create table t0 select * from t1 where nm like '34%'; delete from t1 using t1,t0 where t1.nm=t0.nm; analyze format=json select * from t1 where ( nm like '3400%' or nm like '3402%' or nm like '3403%' or nm like '3404%' or nm like '3405%' or nm like '3406%' or nm like '3407%' or nm like '3409%' or nm like '3411%' or nm like '3412%' or nm like '3413%' or nm like '3414%' or nm like '3415%' or nm like '3416%' or nm like '3417%' or nm like '3418%' or nm like '3419%' or nm like '3421%' or nm like '3422%' or nm like '3423%' or nm like '3424%' or nm like '3425%' or nm like '3426%' or nm like '3427%' or nm like '3428%' or nm like '3429%' or nm like '3430%' or nm like '3431%' or nm like '3432%' or nm like '3433%' or nm like '3434%' or nm like '3435%' or nm like '3436%' or nm like '3437%' or nm like '3439%' or nm like '3440%' or nm like '3441%' or nm like '3442%' or nm like '3443%' or nm like '3444%' or nm like '3445%' or nm like '3446%' or nm like '3447%' or nm like '3448%' ) and fl2 = 0; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ref", "possible_keys": ["idx1", "idx2"], "key": "idx2", "key_length": "5", "used_key_parts": ["fl2"], "ref": ["const"], "rowid_filter": { "range": { "key": "idx1", "used_key_parts": ["nm"] }, "rows": 44, "selectivity_pct": 0.44, "r_rows": 0, "r_lookups": 0, "r_selectivity_pct": 0, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 853, "r_rows": 0, "r_engine_stats": REPLACED, "filtered": 0.439999998, "r_filtered": 100, "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } ] } } drop table t0; set optimizer_switch='rowid_filter=default'; drop table name, flag2; drop table t1; set @@use_stat_tables=@save_use_stat_tables;