1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
|
SET SESSION STORAGE_ENGINE='InnoDB';
set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;
set optimizer_use_stat_tables='preferably';
DROP DATABASE IF EXISTS dbt3_s001;
CREATE DATABASE dbt3_s001;
use dbt3_s001;
select * from mysql.table_stat;
db_name table_name cardinality
dbt3_s001 customer 150
dbt3_s001 lineitem 6005
dbt3_s001 nation 25
dbt3_s001 orders 1500
dbt3_s001 part 200
dbt3_s001 partsupp 700
dbt3_s001 region 5
dbt3_s001 supplier 10
select * from mysql.index_stat;
db_name table_name index_name prefix_arity avg_frequency
dbt3_s001 customer PRIMARY 1 1
dbt3_s001 customer i_c_nationkey 1 6
dbt3_s001 lineitem PRIMARY 1 4.003333333333333
dbt3_s001 lineitem PRIMARY 2 1
dbt3_s001 lineitem i_l_shipdate 1 2.650044130626655
dbt3_s001 lineitem i_l_suppkey_partkey 1 30.025
dbt3_s001 lineitem i_l_suppkey_partkey 2 8.57857142857143
dbt3_s001 lineitem i_l_partkey 1 30.025
dbt3_s001 lineitem i_l_suppkey 1 600.5
dbt3_s001 lineitem i_l_receiptdate 1 2.6477072310405645
dbt3_s001 lineitem i_l_orderkey 1 4.003333333333333
dbt3_s001 lineitem i_l_orderkey_quantity 1 4.003333333333333
dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0403672903672905
dbt3_s001 lineitem i_l_commitdate 1 2.7159656264133876
dbt3_s001 nation PRIMARY 1 1
dbt3_s001 nation i_n_regionkey 1 5
dbt3_s001 orders PRIMARY 1 1
dbt3_s001 orders i_o_orderdate 1 1.3321492007104796
dbt3_s001 orders i_o_custkey 1 15
dbt3_s001 part PRIMARY 1 1
dbt3_s001 part i_p_retailprice 1 1
dbt3_s001 partsupp PRIMARY 1 3.5
dbt3_s001 partsupp PRIMARY 2 1
dbt3_s001 partsupp i_ps_partkey 1 3.5
dbt3_s001 partsupp i_ps_suppkey 1 70
dbt3_s001 region PRIMARY 1 1
dbt3_s001 supplier PRIMARY 1 1
dbt3_s001 supplier i_s_nationkey 1 1.1111111111111112
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='index_condition_pushdown=off';
EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
from customer, orders, lineitem, supplier, nation, region
where c_custkey = o_custkey and l_orderkey = o_orderkey
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
and o_orderdate < date '1995-01-01' + interval '1' year
group by n_name
order by revenue desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 211 Using where; Using temporary; Using filesort
1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.customer.c_nationkey 1 Using index
1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
from customer, orders, lineitem, supplier, nation, region
where c_custkey = o_custkey and l_orderkey = o_orderkey
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
and o_orderdate < date '1995-01-01' + interval '1' year
group by n_name
order by revenue desc;
n_name revenue
PERU 321915.8715
ARGENTINA 69817.1451
set optimizer_switch=@save_optimizer_switch;
EXPLAIN select o_year,
sum(case when nation = 'UNITED STATES' then volume else 0 end) /
sum(volume) as mkt_share
from (select extract(year from o_orderdate) as o_year,
l_extendedprice * (1-l_discount) as volume,
n2.n_name as nation
from part, supplier, lineitem, orders, customer,
nation n1, nation n2, region
where p_partkey = l_partkey and s_suppkey = l_suppkey
and l_orderkey = o_orderkey and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
group by o_year
order by o_year;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort
1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1
1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where
1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
select o_year,
sum(case when nation = 'UNITED STATES' then volume else 0 end) /
sum(volume) as mkt_share
from (select extract(year from o_orderdate) as o_year,
l_extendedprice * (1-l_discount) as volume,
n2.n_name as nation
from part, supplier, lineitem, orders, customer,
nation n1, nation n2, region
where p_partkey = l_partkey and s_suppkey = l_suppkey
and l_orderkey = o_orderkey and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
group by o_year
order by o_year;
o_year mkt_share
1995 0.4495521838895718
1996 0.024585468215352495
EXPLAIN select nation, o_year, sum(amount) as sum_profit
from (select n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from part, supplier, lineitem, partsupp, orders, nation
where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
and ps_partkey = l_partkey and p_partkey = l_partkey
and o_orderkey = l_orderkey and s_nationkey = n_nationkey
and p_name like '%green%') as profit
group by nation, o_year
order by nation, o_year desc;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE supplier index PRIMARY,i_s_nationkey i_s_nationkey 5 NULL 10 Using where; Using index; Using temporary; Using filesort
1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
1 SIMPLE partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 70
1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey 8
1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
select nation, o_year, sum(amount) as sum_profit
from (select n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from part, supplier, lineitem, partsupp, orders, nation
where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
and ps_partkey = l_partkey and p_partkey = l_partkey
and o_orderkey = l_orderkey and s_nationkey = n_nationkey
and p_name like '%green%') as profit
group by nation, o_year
order by nation, o_year desc;
nation o_year sum_profit
ARGENTINA 1997 18247.873399999993
ARGENTINA 1996 7731.089399999995
ARGENTINA 1995 134490.5697
ARGENTINA 1994 36767.101500000004
ARGENTINA 1993 35857.08
ARGENTINA 1992 35740
ETHIOPIA 1998 2758.7801999999992
ETHIOPIA 1997 19419.294599999997
ETHIOPIA 1995 51231.87439999999
ETHIOPIA 1994 3578.9478999999974
ETHIOPIA 1992 1525.8234999999986
IRAN 1998 37817.229600000006
IRAN 1997 52643.77359999999
IRAN 1996 70143.7761
IRAN 1995 84094.58260000001
IRAN 1994 18140.925599999995
IRAN 1993 78655.1676
IRAN 1992 87142.23960000002
IRAQ 1998 22860.8082
IRAQ 1997 93676.24359999999
IRAQ 1996 45103.3242
IRAQ 1994 36010.728599999995
IRAQ 1993 33221.9399
IRAQ 1992 47755.05900000001
KENYA 1998 44194.831999999995
KENYA 1997 57578.36259999999
KENYA 1996 59195.90210000001
KENYA 1995 79262.6278
KENYA 1994 102360.66609999999
KENYA 1993 128422.0196
KENYA 1992 181517.2089
MOROCCO 1998 41797.823199999984
MOROCCO 1997 23685.801799999994
MOROCCO 1996 62115.19579999998
MOROCCO 1995 42442.64300000001
MOROCCO 1994 48655.878000000004
MOROCCO 1993 22926.744400000003
MOROCCO 1992 32239.8088
PERU 1998 86999.36459999997
PERU 1997 121110.41070000001
PERU 1996 177040.40759999995
PERU 1995 122247.94520000002
PERU 1994 88046.25329999998
PERU 1993 49379.813799999996
PERU 1992 80646.86050000001
UNITED KINGDOM 1998 50577.25560000001
UNITED KINGDOM 1997 114288.8605
UNITED KINGDOM 1996 147684.46480000002
UNITED KINGDOM 1995 225267.65759999998
UNITED KINGDOM 1994 140595.5864
UNITED KINGDOM 1993 322548.49210000003
UNITED KINGDOM 1992 67747.88279999999
UNITED STATES 1998 3957.0431999999996
UNITED STATES 1997 94729.5704
UNITED STATES 1996 79297.85670000002
UNITED STATES 1995 62201.23360000001
UNITED STATES 1994 43075.629899999985
UNITED STATES 1993 27168.486199999996
UNITED STATES 1992 34092.366
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='extended_keys=on';
EXPLAIN select o_orderkey, p_partkey
from part, lineitem, orders
where p_retailprice > 1100 and o_orderdate='1997-01-01'
and o_orderkey=l_orderkey and p_partkey=l_partkey;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using where; Using index
1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 Using index
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 1 Using index
select o_orderkey, p_partkey
from part, lineitem, orders
where p_retailprice > 1100 and o_orderdate='1997-01-01'
and o_orderkey=l_orderkey and p_partkey=l_partkey;
o_orderkey p_partkey
5895 200
set optimizer_switch=@save_optimizer_switch;
DROP DATABASE dbt3_s001;
use test;
set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;
SET SESSION STORAGE_ENGINE=DEFAULT;
|
