summaryrefslogtreecommitdiff
path: root/mysql-test/main/rowid_filter_innodb.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/rowid_filter_innodb.result')
-rw-r--r--mysql-test/main/rowid_filter_innodb.result690
1 files changed, 690 insertions, 0 deletions
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
new file mode 100644
index 00000000000..04414a7a5bc
--- /dev/null
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -0,0 +1,690 @@
+SET SESSION STORAGE_ENGINE='InnoDB';
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+CREATE INDEX i_l_quantity ON lineitem(l_quantity);
+CREATE INDEX i_o_totalprice ON orders(o_totalprice);
+set @save_use_stat_tables= @@use_stat_tables;
+set @@use_stat_tables=preferably;
+ANALYZE TABLE lineitem, orders;
+show create table lineitem;
+Table Create Table
+lineitem CREATE TABLE `lineitem` (
+ `l_orderkey` int(11) NOT NULL DEFAULT 0,
+ `l_partkey` int(11) DEFAULT NULL,
+ `l_suppkey` int(11) DEFAULT NULL,
+ `l_linenumber` int(11) NOT NULL DEFAULT 0,
+ `l_quantity` double DEFAULT NULL,
+ `l_extendedprice` double DEFAULT NULL,
+ `l_discount` double DEFAULT NULL,
+ `l_tax` double DEFAULT NULL,
+ `l_returnflag` char(1) DEFAULT NULL,
+ `l_linestatus` char(1) DEFAULT NULL,
+ `l_shipDATE` date DEFAULT NULL,
+ `l_commitDATE` date DEFAULT NULL,
+ `l_receiptDATE` date DEFAULT NULL,
+ `l_shipinstruct` char(25) DEFAULT NULL,
+ `l_shipmode` char(10) DEFAULT NULL,
+ `l_comment` varchar(44) DEFAULT NULL,
+ PRIMARY KEY (`l_orderkey`,`l_linenumber`),
+ KEY `i_l_shipdate` (`l_shipDATE`),
+ KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+ KEY `i_l_partkey` (`l_partkey`),
+ KEY `i_l_suppkey` (`l_suppkey`),
+ KEY `i_l_receiptdate` (`l_receiptDATE`),
+ KEY `i_l_orderkey` (`l_orderkey`),
+ KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+ KEY `i_l_commitdate` (`l_commitDATE`),
+ KEY `i_l_quantity` (`l_quantity`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table orders;
+Table Create Table
+orders CREATE TABLE `orders` (
+ `o_orderkey` int(11) NOT NULL,
+ `o_custkey` int(11) DEFAULT NULL,
+ `o_orderstatus` char(1) DEFAULT NULL,
+ `o_totalprice` double DEFAULT NULL,
+ `o_orderDATE` date DEFAULT NULL,
+ `o_orderpriority` char(15) DEFAULT NULL,
+ `o_clerk` char(15) DEFAULT NULL,
+ `o_shippriority` int(11) DEFAULT NULL,
+ `o_comment` varchar(79) DEFAULT NULL,
+ PRIMARY KEY (`o_orderkey`),
+ KEY `i_o_orderdate` (`o_orderDATE`),
+ KEY `i_o_custkey` (`o_custkey`),
+ KEY `i_o_totalprice` (`o_totalprice`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+set optimizer_use_condition_selectivity=2;
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) Using index condition; Using where; Using filter
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": ["i_l_shipdate", "i_l_quantity"],
+ "key": "i_l_shipdate",
+ "key_length": "4",
+ "used_key_parts": ["l_shipDATE"],
+ "rowid_filter": {
+ "range": {
+ "key": "i_l_quantity",
+ "used_key_parts": ["l_quantity"]
+ },
+ "rows": 605,
+ "selectivity_pct": 10.075
+ },
+ "rows": 510,
+ "filtered": 10.075,
+ "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+ "attached_condition": "lineitem.l_quantity > 45"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+l_orderkey l_linenumber l_shipdate l_quantity
+1121 5 1997-04-27 47
+1121 6 1997-04-21 50
+1441 7 1997-06-07 50
+1443 1 1997-02-05 47
+1473 1 1997-05-05 50
+1568 2 1997-04-06 46
+1632 1 1997-01-25 47
+1632 3 1997-01-29 47
+1954 7 1997-06-04 49
+1959 1 1997-05-05 46
+2151 3 1997-01-20 49
+2177 5 1997-05-10 46
+2369 2 1997-01-02 47
+2469 3 1997-01-11 48
+2469 6 1997-03-03 49
+2470 2 1997-06-02 50
+260 1 1997-03-24 50
+288 2 1997-04-19 49
+289 4 1997-03-14 48
+3009 1 1997-03-19 48
+3105 3 1997-02-28 48
+3106 2 1997-02-27 49
+3429 1 1997-04-08 48
+3490 2 1997-06-27 50
+3619 1 1997-01-22 49
+3619 3 1997-01-31 46
+3969 3 1997-05-29 46
+4005 4 1997-01-31 49
+4036 1 1997-06-21 46
+4066 4 1997-02-17 49
+4098 1 1997-01-26 46
+422 3 1997-06-21 46
+4258 3 1997-01-02 46
+4421 2 1997-04-21 46
+4421 3 1997-05-25 46
+4453 3 1997-05-29 48
+4484 7 1997-03-17 50
+4609 3 1997-02-11 46
+484 1 1997-03-06 49
+484 3 1997-01-24 50
+484 5 1997-03-05 48
+485 1 1997-03-28 50
+4868 1 1997-04-29 47
+4868 3 1997-04-23 49
+4934 1 1997-05-20 48
+4967 1 1997-05-27 50
+5090 2 1997-04-05 46
+5152 2 1997-03-10 50
+5158 4 1997-04-10 49
+5606 3 1997-03-11 46
+5606 7 1997-02-01 46
+5762 4 1997-03-02 47
+581 3 1997-02-27 49
+5829 5 1997-01-31 49
+5831 4 1997-02-24 46
+5895 2 1997-04-27 47
+5895 3 1997-03-15 49
+5952 1 1997-06-30 49
+705 1 1997-04-18 46
+836 3 1997-03-21 46
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 510 Using index condition; Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": ["i_l_shipdate", "i_l_quantity"],
+ "key": "i_l_shipdate",
+ "key_length": "4",
+ "used_key_parts": ["l_shipDATE"],
+ "rows": 510,
+ "filtered": 10.075,
+ "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'",
+ "attached_condition": "lineitem.l_quantity > 45"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45;
+l_orderkey l_linenumber l_shipdate l_quantity
+1121 5 1997-04-27 47
+1121 6 1997-04-21 50
+1441 7 1997-06-07 50
+1443 1 1997-02-05 47
+1473 1 1997-05-05 50
+1568 2 1997-04-06 46
+1632 1 1997-01-25 47
+1632 3 1997-01-29 47
+1954 7 1997-06-04 49
+1959 1 1997-05-05 46
+2151 3 1997-01-20 49
+2177 5 1997-05-10 46
+2369 2 1997-01-02 47
+2469 3 1997-01-11 48
+2469 6 1997-03-03 49
+2470 2 1997-06-02 50
+260 1 1997-03-24 50
+288 2 1997-04-19 49
+289 4 1997-03-14 48
+3009 1 1997-03-19 48
+3105 3 1997-02-28 48
+3106 2 1997-02-27 49
+3429 1 1997-04-08 48
+3490 2 1997-06-27 50
+3619 1 1997-01-22 49
+3619 3 1997-01-31 46
+3969 3 1997-05-29 46
+4005 4 1997-01-31 49
+4036 1 1997-06-21 46
+4066 4 1997-02-17 49
+4098 1 1997-01-26 46
+422 3 1997-06-21 46
+4258 3 1997-01-02 46
+4421 2 1997-04-21 46
+4421 3 1997-05-25 46
+4453 3 1997-05-29 48
+4484 7 1997-03-17 50
+4609 3 1997-02-11 46
+484 1 1997-03-06 49
+484 3 1997-01-24 50
+484 5 1997-03-05 48
+485 1 1997-03-28 50
+4868 1 1997-04-29 47
+4868 3 1997-04-23 49
+4934 1 1997-05-20 48
+4967 1 1997-05-27 50
+5090 2 1997-04-05 46
+5152 2 1997-03-10 50
+5158 4 1997-04-10 49
+5606 3 1997-03-11 46
+5606 7 1997-02-01 46
+5762 4 1997-03-02 47
+581 3 1997-02-27 49
+5829 5 1997-01-31 49
+5831 4 1997-02-24 46
+5895 2 1997-04-27 47
+5895 3 1997-03-15 49
+5952 1 1997-06-30 49
+705 1 1997-04-18 46
+836 3 1997-03-21 46
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_shipdate",
+ "key_length": "4",
+ "used_key_parts": ["l_shipDATE"],
+ "rows": 98,
+ "filtered": 100,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 4.7333,
+ "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+o_orderkey l_linenumber l_shipdate o_totalprice
+1156 3 1997-01-24 217682.81
+1156 4 1997-01-18 217682.81
+1156 6 1997-01-27 217682.81
+1156 7 1997-01-01 217682.81
+2180 2 1997-01-03 208481.57
+2180 3 1997-01-03 208481.57
+3619 1 1997-01-22 222274.54
+3619 3 1997-01-31 222274.54
+3619 6 1997-01-25 222274.54
+484 3 1997-01-24 219920.62
+5606 6 1997-01-11 219959.08
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index
+1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "range",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "i_l_shipdate",
+ "key_length": "4",
+ "used_key_parts": ["l_shipDATE"],
+ "rows": 98,
+ "filtered": 100,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "orders",
+ "access_type": "eq_ref",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["o_orderkey"],
+ "ref": ["dbt3_s001.lineitem.l_orderkey"],
+ "rows": 1,
+ "filtered": 4.7333,
+ "attached_condition": "orders.o_totalprice between 200000 and 230000"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND
+o_totalprice between 200000 and 230000;
+o_orderkey l_linenumber l_shipdate o_totalprice
+1156 3 1997-01-24 217682.81
+1156 4 1997-01-18 217682.81
+1156 6 1997-01-27 217682.81
+1156 7 1997-01-01 217682.81
+2180 2 1997-01-03 208481.57
+2180 3 1997-01-03 208481.57
+3619 1 1997-01-22 222274.54
+3619 3 1997-01-31 222274.54
+3619 6 1997-01-25 222274.54
+484 3 1997-01-24 219920.62
+5606 6 1997-01-11 219959.08
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 144,
+ "filtered": 100,
+ "attached_condition": "orders.o_totalprice between 180000 and 230000",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity",
+ "i_l_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 0.8557,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+o_orderkey l_linenumber l_shipdate l_quantity o_totalprice
+1632 1 1997-01-25 47 183286.33
+1632 3 1997-01-29 47 183286.33
+2177 5 1997-05-10 46 183493.42
+2469 3 1997-01-11 48 192074.23
+2469 6 1997-03-03 49 192074.23
+3619 1 1997-01-22 49 222274.54
+3619 3 1997-01-31 46 222274.54
+484 1 1997-03-06 49 219920.62
+484 3 1997-01-24 50 219920.62
+484 5 1997-03-05 48 219920.62
+4934 1 1997-05-20 48 180478.16
+5606 3 1997-03-11 46 219959.08
+5606 7 1997-02-01 46 219959.08
+5829 5 1997-01-31 49 183734.56
+5895 2 1997-04-27 47 201419.83
+5895 3 1997-03-15 49 201419.83
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 144,
+ "filtered": 100,
+ "attached_condition": "orders.o_totalprice between 180000 and 230000",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity",
+ "i_l_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 0.8557,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+l_quantity > 45 AND
+o_totalprice between 180000 and 230000;
+o_orderkey l_linenumber l_shipdate l_quantity o_totalprice
+1632 1 1997-01-25 47 183286.33
+1632 3 1997-01-29 47 183286.33
+2177 5 1997-05-10 46 183493.42
+2469 3 1997-01-11 48 192074.23
+2469 6 1997-03-03 49 192074.23
+3619 1 1997-01-22 49 222274.54
+3619 3 1997-01-31 46 222274.54
+484 1 1997-03-06 49 219920.62
+484 3 1997-01-24 50 219920.62
+484 5 1997-03-05 48 219920.62
+4934 1 1997-05-20 48 180478.16
+5606 3 1997-03-11 46 219959.08
+5606 7 1997-02-01 46 219959.08
+5829 5 1997-01-31 49 183734.56
+5895 2 1997-04-27 47 201419.83
+5895 3 1997-03-15 49 201419.83
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 71,
+ "filtered": 100,
+ "attached_condition": "orders.o_totalprice between 200000 and 230000",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 8.4929,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+o_orderkey l_linenumber l_shipdate o_totalprice
+1156 3 1997-01-24 217682.81
+1156 4 1997-01-18 217682.81
+1156 6 1997-01-27 217682.81
+1156 7 1997-01-01 217682.81
+1890 1 1997-04-02 202364.58
+1890 3 1997-02-09 202364.58
+1890 4 1997-04-08 202364.58
+1890 5 1997-04-15 202364.58
+1890 6 1997-02-13 202364.58
+2180 2 1997-01-03 208481.57
+2180 3 1997-01-03 208481.57
+3619 1 1997-01-22 222274.54
+3619 3 1997-01-31 222274.54
+3619 4 1997-03-18 222274.54
+3619 6 1997-01-25 222274.54
+453 1 1997-06-30 216826.73
+453 2 1997-06-30 216826.73
+484 1 1997-03-06 219920.62
+484 2 1997-04-09 219920.62
+484 3 1997-01-24 219920.62
+484 4 1997-04-29 219920.62
+484 5 1997-03-05 219920.62
+484 6 1997-04-06 219920.62
+5606 2 1997-02-23 219959.08
+5606 3 1997-03-11 219959.08
+5606 4 1997-02-06 219959.08
+5606 6 1997-01-11 219959.08
+5606 7 1997-02-01 219959.08
+5859 2 1997-05-15 210643.96
+5859 5 1997-05-28 210643.96
+5859 6 1997-06-15 210643.96
+5895 1 1997-04-05 201419.83
+5895 2 1997-04-27 201419.83
+5895 3 1997-03-15 201419.83
+5895 4 1997-03-03 201419.83
+5895 5 1997-04-30 201419.83
+5895 6 1997-04-19 201419.83
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index
+1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
+set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "table_name": "orders",
+ "access_type": "range",
+ "possible_keys": ["PRIMARY", "i_o_totalprice"],
+ "key": "i_o_totalprice",
+ "key_length": "9",
+ "used_key_parts": ["o_totalprice"],
+ "rows": 71,
+ "filtered": 100,
+ "attached_condition": "orders.o_totalprice between 200000 and 230000",
+ "using_index": true
+ },
+ "table": {
+ "table_name": "lineitem",
+ "access_type": "ref",
+ "possible_keys": [
+ "PRIMARY",
+ "i_l_shipdate",
+ "i_l_orderkey",
+ "i_l_orderkey_quantity"
+ ],
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["l_orderkey"],
+ "ref": ["dbt3_s001.orders.o_orderkey"],
+ "rows": 4,
+ "filtered": 8.4929,
+ "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'"
+ }
+ }
+}
+set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice
+FROM orders JOIN lineitem ON o_orderkey=l_orderkey
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
+o_totalprice between 200000 and 230000;
+o_orderkey l_linenumber l_shipdate o_totalprice
+1156 3 1997-01-24 217682.81
+1156 4 1997-01-18 217682.81
+1156 6 1997-01-27 217682.81
+1156 7 1997-01-01 217682.81
+1890 1 1997-04-02 202364.58
+1890 3 1997-02-09 202364.58
+1890 4 1997-04-08 202364.58
+1890 5 1997-04-15 202364.58
+1890 6 1997-02-13 202364.58
+2180 2 1997-01-03 208481.57
+2180 3 1997-01-03 208481.57
+3619 1 1997-01-22 222274.54
+3619 3 1997-01-31 222274.54
+3619 4 1997-03-18 222274.54
+3619 6 1997-01-25 222274.54
+453 1 1997-06-30 216826.73
+453 2 1997-06-30 216826.73
+484 1 1997-03-06 219920.62
+484 2 1997-04-09 219920.62
+484 3 1997-01-24 219920.62
+484 4 1997-04-29 219920.62
+484 5 1997-03-05 219920.62
+484 6 1997-04-06 219920.62
+5606 2 1997-02-23 219959.08
+5606 3 1997-03-11 219959.08
+5606 4 1997-02-06 219959.08
+5606 6 1997-01-11 219959.08
+5606 7 1997-02-01 219959.08
+5859 2 1997-05-15 210643.96
+5859 5 1997-05-28 210643.96
+5859 6 1997-06-15 210643.96
+5895 1 1997-04-05 201419.83
+5895 2 1997-04-27 201419.83
+5895 3 1997-03-15 201419.83
+5895 4 1997-03-03 201419.83
+5895 5 1997-04-30 201419.83
+5895 6 1997-04-19 201419.83
+DROP DATABASE dbt3_s001;
+set @@use_stat_tables=@save_use_stat_tables;
+SET SESSION STORAGE_ENGINE=DEFAULT;
View Lorry Controller Status